PostgreSQL：如何用单查询过滤JSON对象数组并提取所需值

原始表结构 #

需求 #

提取tag数组中type为country的所有value值，聚合为文本数组，预期结果如下：

之前的尝试 #

我之前通过添加行号作为唯一ID来分组实现，操作如下：

SELECT ROW_NUMBER() OVER () AS id, * INTO data_table_with_id FROM data_table;

SELECT ARRAY_AGG(tag_value) AS result
FROM (
    SELECT
        id, 
        json_array_elements("data"::json->'tag')->>'type' as tag_type,
        json_array_elements("data"::json->'tag')->>'value' as tag_value 
    FROM data_table_with_id
) tags 
WHERE tag_type = 'country' 
GROUP BY id;

问题 #

是否可以通过单条SELECT语句过滤对象数组并得到所需结果？

当然可以，不需要额外创建带ID的中间表，这里提供两种实现方式：

方法1：适用所有支持LATERAL的PostgreSQL版本

SELECT COALESCE(ARRAY_AGG(tag->>'value'), '{}'::text[]) AS result
FROM data_table
LEFT JOIN LATERAL json_array_elements(data::json->'tag') AS tag
    ON tag->>'type' = 'country'
GROUP BY data_table.ctid;

• 用LEFT JOIN LATERAL展开每行的tag数组，只保留type=country的元素；
• ARRAY_AGG聚合筛选后的value，COALESCE确保没有匹配项时返回空数组；
• 用PostgreSQL内置的ctid作为分组标识，它是每行的唯一物理位置标识，无需手动添加ID列。

方法2：PostgreSQL 12+ 可用（更简洁）

如果你的PostgreSQL版本是12或以上，用JSON路径函数一步到位：

SELECT json_path_query_array(data::json, '$.tag[*] ? (@.type == "country").value') AS result
FROM data_table;

• json_path_query_array通过JSON路径表达式直接筛选出tag数组中type为country的元素的value，并返回数组；没有匹配项时自动返回空数组，无需额外处理。

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