PostgreSQL如何对双嵌套数组执行UNNEST操作 新手问题求助

标准PostgreSQL环境解法 #

你的custom_fields字段为JSONB类型，嵌套了两层结构：外层对象的f键对应第一层数组，数组每个元素是包含v键的对象，需要用JSON专属展开函数jsonb_array_elements处理，统计SQL如下：

SELECT COUNT(*) AS matched_total
FROM db.tickets
WHERE EXISTS (
    SELECT 1
    -- 展开f字段对应的JSON数组
    FROM jsonb_array_elements(custom_fields -> 'f') AS array_elem
    -- 匹配v值为1234的元素
    WHERE array_elem ->> 'v' = '1234'
);

如果你的字段是JSON类型而非JSONB，把jsonb_array_elements替换为json_array_elements即可。

兼容OFFSET/SAFE_OFFSET语法的云数仓环境（如BigQuery PostgreSQL兼容模式）解法 #

你之前的报错提示对应这类环境，需要先定位到f对应的数组层再展开，统计SQL如下：

SELECT COUNT(DISTINCT tickets.id) AS matched_total
FROM db.tickets
-- 展开custom_fields.f对应的数组
CROSS JOIN UNNEST(JSON_VALUE_ARRAY(custom_fields.f)) AS cf
WHERE JSON_VALUE(cf.v) = '1234'

之前尝试失败的原因 #

• 直接用下标取custom_fields[safe_offset(1)]仅能取出数组第一个元素，没有遍历所有数组元素，也没有进入第二层v字段的匹配逻辑
• 直接对custom_fields字段执行UNNEST，没有指定要展开的是f键对应的子数组，自然无法解除嵌套结构
• 原生的[1][1]二维下标访问仅支持PostgreSQL原生数组类型，不支持JSON格式的嵌套结构访问

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