CODEWITHSUNDEEP
sqljsonpostgresql
sula1337
sula1337

Reputation: 5

Getting values from nested JSON with a dynamic key in a database

I have a Postgres database with a table that has jsonb column and each row is one big blob of json I need to get the values in deeper level but can't access it because the key is a dynamic UUID. Tried something like this to get the keys themselves

SELECT 
    json_object_keys(body::json-> '__tasks') as tasksId
FROM head.bp_instances inst

WHERE 
body ->> '__templateId' = '6666f537-a370-4ebd-b709-ccb1d04b2da3'

But can't figure out what to do with these UUID? Iterating through each and surely is not gonna be effective I believe.

JSON itself looks like this:

"__tasks": {
         "e689f2c9-e6c1-432b-8dec-968e75c8350b": {
            "__id": "e689f2c9-e6c1-432b-8dec-968e75c8350b",
            "path": "/items/fbdeab2c-14cd-4209-8883-11673bdd3780",
            "state": "in_progress"
          },
         "e689f2c9-e6c1-432b-8dec-968e75c8350b": {
            "__id": "e689f2c9-e6c1-432b-8dec-968e75c8350b",
            "path": "/items/fbdeab2c-14cd-4209-8883-11673bdd3780",
            "state": "in_progress"
          },
         "e689f2c9-e6c1-432b-8dec-968e75c8350b": {
            "__id": "e689f2c9-e6c1-432b-8dec-968e75c8350b",
            "path": "/items/fbdeab2c-14cd-4209-8883-11673bdd3780",
            "state": "in_progress"
          }
     }

I need to get values like path, state etc.

Upvotes: 0

Views: 1168

Answers (1)

user330315
user330315

Reputation:

If you want one row per nested item, you can use json_each()

select b.id, 
       t.uid, 
       t.item ->> 'path' as path,
       t.item ->> '__id' as item_id, 
       t.item ->> 'state' as state
from bp_instances b
  cross join json_each(body -> '__tasks') as t(uid, item)
WHERE body ->> '__templateId' = '6666f537-a370-4ebd-b709-ccb1d04b2da3'

Based on your sample data this returns:

id | uid                                  | path                                        | item_id                              | state      
---+--------------------------------------+---------------------------------------------+--------------------------------------+------------
 1 | e689f2c9-e6c1-432b-8dec-968e75c8350b | /items/fbdeab2c-14cd-4209-8883-11673bdd3780 | e689f2c9-e6c1-432b-8dec-968e75c8350b | in_progress
 1 | e689f2c9-e6c1-432b-8dec-968e75c8350b | /items/fbdeab2c-14cd-4209-8883-11673bdd3780 | e689f2c9-e6c1-432b-8dec-968e75c8350b | in_progress
 1 | e689f2c9-e6c1-432b-8dec-968e75c8350b | /items/fbdeab2c-14cd-4209-8883-11673bdd3780 | e689f2c9-e6c1-432b-8dec-968e75c8350b | in_progress

Upvotes: 1

Related Questions