如何使用PostgreSQL访问嵌套JSON字典中的数组元素、键与值

以下方案基于PostgreSQL JSON操作语法实现，针对你提供的record表report字段结构编写：

场景1：筛选u1 = "0000"的记录 #

仅筛选符合条件的整行记录 #

SELECT * 
FROM record r
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(r.report->'PIname'->'PIDataArea'->'PInven'->'PILine') AS line
    WHERE line->>'u1' = '0000'
);

提取u1及对应关联字段值 #

SELECT 
    line->>'u1' AS u1,
    line->>'u2' AS u2,
    line->'modes'->>'#txt' AS modes_value
FROM record r,
json_array_elements(r.report->'PIname'->'PIDataArea'->'PInven'->'PILine') AS line
WHERE line->>'u1' = '0000';

场景2：筛选sky:selling为"1"或"0"的记录 #

仅筛选符合条件的整行记录 #

SELECT * 
FROM record r
WHERE EXISTS (
    SELECT 1
    -- 先展开PILine数组
    FROM json_array_elements(r.report->'PIname'->'PIDataArea'->'PInven'->'PILine') AS line
    -- 再展开sky:q1嵌套数组
    FROM json_array_elements(line->'sky:Forest'->'sky:q1') AS q1_item
    WHERE q1_item->>'sky:selling' IN ('0', '1')
);

提取sky:selling及对应关联字段值 #

SELECT 
    q1_item->>'sky:selling' AS sky_selling,
    line->>'sky:SCode' AS sky_SCode,
    line->>'Qualify' AS Qualify
FROM record r,
json_array_elements(r.report->'PIname'->'PIDataArea'->'PInven'->'PILine') AS line,
json_array_elements(line->'sky:Forest'->'sky:q1') AS q1_item
WHERE q1_item->>'sky:selling' IN ('0', '1');

原查询问题说明 #

你之前的语句核心问题是JSON路径匹配错误：sky:selling 不在report字段顶层，而是在嵌套的两层数组内，直接遍历顶层JSON键无法命中目标字段。
如果你使用的是jsonb类型字段，只需要把上述语句中的json_array_elements替换为jsonb_array_elements即可，其他语法通用。

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