如何编写SQL查询实现关联表聚合为JSON并按指定status筛选

嘿，我来帮你搞定这个SQL查询需求！先把相关的表结构、测试数据和最终的解决方案梳理清楚：

我们需要编写SQL查询，当筛选status在指定集合中时，返回表A的id、status字段，以及符合条件的表B记录聚合而成的JSON数组（命名为arrayjson）。核心规则是：只有表B中status匹配筛选集合的记录才会被聚合；即使对应表A没有符合条件的表B记录，也要返回表A信息，同时JSON数组为空。

表A结构与测试数据 #

表B结构与测试数据 #

正式表结构定义 #

Table A (
id int,
status int);

Table B(
id int,
status int,
a_id int foreign key reference A(id)
);

下面以MySQL为例给出查询语句（其他数据库的JSON聚合函数可按需调整）：

SELECT
    A.id,
    A.status,
    COALESCE(
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', B.id,
                'status', B.status,
                'a_id', B.a_id
            )
        ),
        '[]'
    ) AS arrayjson
FROM A
LEFT JOIN B ON A.id = B.a_id AND B.status IN (:status_list)
WHERE A.id IN (
    -- 包含有符合条件B记录的A
    SELECT DISTINCT a_id FROM B WHERE B.status IN (:status_list)
    UNION
    -- 包含自身status符合条件的A
    SELECT id FROM A WHERE A.status IN (:status_list)
)
GROUP BY A.id, A.status;

关键逻辑说明 #

• LEFT JOIN B ON A.id = B.a_id AND B.status IN (:status_list)：只关联表B中status匹配筛选条件的记录，避免不符合的记录干扰聚合结果
• JSON_ARRAYAGG(JSON_OBJECT(...))：将符合条件的表B行转换成JSON对象，再聚合为数组
• COALESCE(..., '[]')：当没有匹配的表B记录时，返回空数组[]而非NULL
• WHERE子句：确保只返回自身status在筛选集合中，或有对应B记录status在筛选集合中的表A数据，完全匹配需求中的测试场景

场景1：筛选status IN (1,3) #

场景2：筛选status IN (3) #

场景3：筛选status IN (4) #

场景4：筛选status IN (5) #

其他数据库适配提示 #

• PostgreSQL：将JSON_ARRAYAGG(JSON_OBJECT(...))替换为json_agg(json_build_object('id', B.id, 'status', B.status, 'a_id', B.a_id))
• SQL Server：使用STRING_AGG(CONCAT('{"id":', B.id, ',"status":', B.status, ',"a_id":', B.a_id, '}'), ',')拼接JSON字符串，再用CONCAT('[', ..., ']')包裹，同时处理空值情况

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