我如何从JSON对象的嵌套数组中选择所有?

Question

我有一个PostgreSQL表，每一行都包含JSON对象，看起来像这样:

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

所以每个对象包含一个NestedArray，它包含另一个嵌套数组Coordinates。

MyObject.NestedArray[].Coordinates[]

我想做的是在我的PostgreSQL表中查询一个JSON列，其中包含上述对象，并获得所有Coordinates对象的结果集。

这就是我想要的结果:

<表类>idXYtbody><<tr>1001。。1002。。1003。。1004。。1005。。1006。。

我不认为jsonb_array_elements()将在这种情况下工作，因为有两个嵌套的json数组级别。这个应该被测试:

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)

您需要打开数组，然后您可以使用->>操作符访问每个键:

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)

这里假设the_column被定义为数据类型jsonb(它应该是)。如果不是，使用json_array_elements()。

在线例子