PostgreSQL如何提取JSON列嵌套数组中所有Coordinates对象？

假设你的表名为your_table，存储JSON数据的列名为json_col，根据JSON列的具体类型选择对应查询语句：

当JSON列类型为jsonb（PostgreSQL推荐使用的JSON类型） #

SELECT
  (coord->>'id') AS id,
  (coord->>'X') AS X,
  (coord->>'Y') AS Y
FROM
  your_table,
  jsonb_array_elements(json_col->'NestedArray') AS nested_elem,
  jsonb_array_elements(nested_elem->'Coordinates') AS coord;

当JSON列类型为json #

将上述语句中的jsonb_array_elements替换为json_array_elements即可：

SELECT
  (coord->>'id') AS id,
  (coord->>'X') AS X,
  (coord->>'Y') AS Y
FROM
  your_table,
  json_array_elements(json_col->'NestedArray') AS nested_elem,
  json_array_elements(nested_elem->'Coordinates') AS coord;

补充说明 #

• 语句使用了PostgreSQL隐式横向连接（LATERAL JOIN），逐行处理每行JSON数据，先展开顶层NestedArray数组，再展开每个数组元素下的Coordinates嵌套数组，最终得到所有坐标对象。
• ->>运算符用于将JSON字段的值以文本类型返回，直接输出符合要求的结果集。
• 如需过滤无效数据，可在语句末尾添加WHERE条件，例如WHERE json_col ? 'NestedArray'过滤没有NestedArray字段的行，也可添加DISTINCT关键字对结果去重。

内容的提问来源于stack exchange，提问作者user928112