Question 191577 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Retrieving the value of "SkipEndUserAcceptance" from a JSON

I’m attempting to retrieve information from a JSON, but the specific location of the information may vary. Here is an example of the data I have:

id templatevalues
1 {"complexTypeProperties":[{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"448928","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":"10146","AttributeName":"Category"}},{"properties":{"Value":true,"AttributeName":"SkipEndUserAcceptance"}}]}
2 {"complexTypeProperties":[{"properties":{"AttributeName":"RegisteredForActualService"}},{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"10154","AttributeName":"Category"}},{"properties":{"Value":"true","AttributeName":"SkipEndUserAcceptance"}}]}
3 {"complexTypeProperties":[{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"721846","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":"10146","AttributeName":"Category"}},{"properties":{"Value":"true","AttributeName":"SkipEndUserAcceptance"}}]}
4 {"complexTypeProperties":[{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"SlightDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"2854102","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":"10153","AttributeName":"Category"}},{"properties":{"Value":"435331","AttributeName":"ServiceDeskGroup"}}]}
5 {"complexTypeProperties":[{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"10146","AttributeName":"Category"}},{"properties":{"Value":"597224","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":true,"AttributeName":"SkipEndUserAcceptance"}}]}
6 {"complexTypeProperties":[{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"10146","AttributeName":"Category"}},{"properties":{"Value":"597224","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":false,"AttributeName":"SkipEndUserAcceptance"}}]}

I need to retrieve the value of "SkipEndUserAcceptance" from a JSON. This value can be either "false" or "true". Currently, I’m using the expression :

TEMPLATEVALUES::jsonb -> 'complexTypeProperties' @> '[{"properties":{"AttributeName":"SkipEndUserAcceptance","Value":"true"}}]' AS "SkipEndUserAcceptance"

to retrieve it. However, the returned value appears to be unpredictable.

Could you please suggest a solution for retrieving the value of "SkipEndUserAcceptance"? Additionally, if the attribute "SkipEndUserAcceptance" is not present, it should be set to "false".

Current output :

id SkipEndUserAcceptance
1 false
2 true
3 true
4 false
5 false
6 false

Expected output :

id SkipEndUserAcceptance
1 true
2 true
3 true
4 false
5 true
6 false

fiddle : https://www.db-fiddle.com/f/6AacERW43QoJ5Ggb3X4Dys/0

Tags:

2

Answers


  1. 0

    Use jsonb_to_recordset to create a table from the array:

    SELECT id
     , COALESCE((MAX(properties ->> 'Value')
                 FILTER (WHERE properties ->> 'AttributeName' = 'SkipEndUserAcceptance'))::BOOL
    , FALSE) AS skipenduseracceptance
FROM tbl_temp
   , JSONB_TO_RECORDSET(templatevalues -> 'complexTypeProperties') jtr(properties JSONB)
GROUP BY id
ORDER BY id;
    Login or Signup to reply.
  2. 0

    Use this scalar subquery

    (
   select (j ->> 'Value')::boolean 
   from jsonb_path_query(templatevalues, '$.complexTypeProperties[*].properties') as j
   where j ->> 'AttributeName' = 'SkipEndUserAcceptance'
 )

    to extract the SkipEndUserAcceptance value and coalesce it to false for the expected result. Here it is:

    select id, 
 coalesce((
   select (j ->> 'Value')::boolean 
   from jsonb_path_query(templatevalues, '$.complexTypeProperties[*].properties') as j
   where j ->> 'AttributeName' = 'SkipEndUserAcceptance'
 ), false)  as "SkipEndUserAcceptance"
from the_table;

    See Demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search