CODEWITHSUNDEEP
jsonpostgresql
user928112
user928112

Reputation: 582

How do I select all from a nested array of JSON objects?

I have a PostgreSQL table with each row containing JSON objects that look something like this:

{
   "Metadata":{
      ...
   },
   "NestedArray":[
      {
         ...,
         ...,
         "Coordinates":[
            {
               "id":"1000",
               "X":"...",
               "Y":"..."
            },
            {
               "id":"1001",
               "X":"...",
               "Y":"..."
            },
            {
               "id":"1003",
               "X":"...",
               "Y":"..."
            }
         ]
      }
   ]
}

So each object contains a NestedArray, which contains another nested array called Coordinates.

MyObject.NestedArray[].Coordinates[]

What I'm trying to do is query a JSON column in my PostgreSQL table that contains objects such as the one above, and get a resultset of all Coordinates objects.

So this is essentially what I want to end up with:

id X Y
1001 . .
1002 . .
1003 . .
1004 . .
1005 . .
1006 . .

How do I go about doing this?

Upvotes: 0

Views: 2274

Answers (2)

user330315
user330315

Reputation:

You need to unnest the array, then you can access each key using the ->> operator:

select e.item ->> 'id' as id, 
       e.item ->> 'x' as x,
       e.item ->> 'y' as y
from the_table
  cross join jsonb_array_elements(the_column -> 'NestedArray') as c(o)
  cross join jsonb_array_elements(c.o -> 'Coordinates') as e(item)

This assumes that the_column is defined with the data type jsonb (which it should be). If it's not use json_array_elements() instead.

Online example

Upvotes: 0

Edouard
Edouard

Reputation: 7065

I don't think that jsonb_array_elements() will work in this case because there are two nested levels of json arrays. This should be tested intead :

SELECT r.item ->> 'id' as id
     , r.item ->> 'X' as X
     , r.item ->> 'Y' as Y
FROM your_table
CROSS JOIN LATERAL jsonb_path_query(your_json_column :: jsonb, '$.NestedArray[*].Coordinates[*]') AS r(item)

Upvotes: 2

Related Questions