PostgreSQL查询JSON类型字段中嵌套数组的name与version属性值

问题原因 #

你之前的查询返回空值，是因为config::json -> 'A' -> 'B'取到的是JSON数组，不能直接对数组类型取name、version这类对象属性，需要先把数组拆分为单行的单个JSON对象，再提取对应字段。

正确查询语句 #

PostgreSQL 可以用json_array_elements函数拆解JSON数组，配合横向关联实现需求：

SELECT
  b_element ->> 'name' AS name,
  b_element ->> 'version' AS version
FROM my_table,
     json_array_elements(config::json -> 'A' -> 'B') AS b_element;

说明 #

• 上述写法是隐式的CROSS JOIN LATERAL，会自动把每行数据里B数组的每一个元素拆分为单独的行，再提取对应字段
• 如果你需要保留B数组为空、或A/B字段不存在的行，可改用左连接写法：

SELECT
  b_element ->> 'name' AS name,
  b_element ->> 'version' AS version
FROM my_table
LEFT JOIN LATERAL json_array_elements(config::json -> 'A' -> 'B') AS b_element ON true;

• 如果你使用的是JSONB类型字段，将json_array_elements替换为jsonb_array_elements即可，性能更优。

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