'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.

sqljsonpostgresql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions