PostgreSQL 11解析字符型嵌套JSON数组的查询需求

针对你遇到的嵌套JSON数组解析问题，结合PostgreSQL 11.16的语法特性，以下是两种场景的具体实现方案：

核心思路 #

由于Brands列是character varying类型，需先转换为jsonb类型（PostgreSQL 11对jsonb的数组处理支持更稳定），再通过jsonb_array_elements逐层展开嵌套数组，配合条件过滤实现需求。

1. 用于SELECT子句：展示符合条件的嵌套数据 #

如果需要同时获取原表数据和符合条件的嵌套元素，可通过两次CROSS JOIN展开数组，同时加入合法性判断避免非法JSON结构报错：

SELECT 
    t.*,
    brand_elem AS matched_brand,
    coding_elem AS matched_coding
FROM ThisTable t
-- 展开上层品牌数组
CROSS JOIN jsonb_array_elements(t.Brands::jsonb) AS brand_elem
-- 安全展开coding数组：处理非数组/NULL情况
LEFT JOIN jsonb_array_elements(
    CASE 
        WHEN jsonb_typeof(brand_elem->'type'->'coding') = 'array' 
        THEN brand_elem->'type'->'coding' 
        ELSE '[]'::jsonb 
    END
) AS coding_elem ON TRUE
WHERE 
    -- 过滤上层site条件
    brand_elem->>'site' = 'https://brand.map.com/cur'
    -- 过滤coding的code和site条件
    AND coding_elem->>'code' = 'gee'
    AND coding_elem->>'site' = 'http://ag.org/clear/latest/green-fld/Coding/GrType'

2. 用于WHERE子句：筛选符合条件的整行数据 #

如果只需要筛选出满足条件的整行数据，不需要展示嵌套元素，用嵌套EXISTS子查询性能更优：

SELECT *
FROM ThisTable t
WHERE EXISTS (
    SELECT 1
    -- 遍历上层品牌数组
    FROM jsonb_array_elements(t.Brands::jsonb) AS brand_elem
    WHERE 
        brand_elem->>'site' = 'https://brand.map.com/cur'
        AND EXISTS (
            SELECT 1
            -- 遍历当前品牌下的coding数组
            FROM jsonb_array_elements(brand_elem->'type'->'coding') AS coding_elem
            WHERE 
                coding_elem->>'code' = 'gee'
                AND coding_elem->>'site' = 'http://ag.org/clear/latest/green-fld/Coding/GrType'
        )
)

注意事项 #

• 若Brands列存在非法JSON格式数据，可在查询前加入jsonb_typeof(t.Brands::jsonb) = 'array'过滤，避免转换报错。
• 由于PostgreSQL 11不支持jsonb_path_query这类更简洁的路径查询语法，只能通过逐层展开数组的方式实现，升级到更高版本可简化写法。

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