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

Question

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?

Edouard · Accepted Answer · 2021-10-29 19:11:15Z

2

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)

answered Oct 29, 2021 at 19:11

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 0 · Accepted Answer · 2021-10-29 20:04:29Z

0

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

edited Oct 29, 2021 at 20:04

answered Oct 19, 2021 at 5:42

user330315

2 Comments

user928112 Over a year ago

What do you mean by unnesting the array? I don't have control over the structure of the JSON document.

user330315 Over a year ago

jsonb_array_elements will unnest the array

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related