PostgreSQL如何根据json_agg聚合值进行筛选

报错原因 #

你遇到的operator does not exist: json = integer错误，是因为直接将JSON类型的聚合字段与整数进行比较，PostgreSQL没有定义这种跨类型的比较操作符，必须先处理类型匹配问题。

正确SQL写法 #

方法一：通过展开JSON数组筛选

先执行聚合得到包含JSON数组的结果集，再通过json_array_elements展开数组元素，判断是否存在值为100的元素：

SELECT s_id
FROM (
    -- 你的基础聚合查询
    SELECT s_id, json_agg(t_value) AS t_values
    FROM t_id_s_id_table
    JOIN t_table ON t_id_s_id_table.t_id = t_table.t_id
    GROUP BY s_id
) AS agg_result
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(agg_result.t_values) AS elem
    WHERE elem::integer = 100
);

方法二：转换为JSONB使用包含操作符

PostgreSQL的JSONB类型支持数组包含判断，将聚合结果转为JSONB后，用@>操作符检查是否包含目标值：

SELECT s_id
FROM (
    -- 你的基础聚合查询，将json_agg转为jsonb
    SELECT s_id, json_agg(t_value)::jsonb AS t_values
    FROM t_id_s_id_table
    JOIN t_table ON t_id_s_id_table.t_id = t_table.t_id
    GROUP BY s_id
) AS agg_result
WHERE t_values @> '[100]'::jsonb;

说明 #

• 方法一兼容性好，适合所有支持json_array_elements的PostgreSQL版本；
• 方法二更高效，JSONB的包含操作可以利用索引优化（如果后续建立了相关索引）。

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