如何在PostgreSQL中生成包含关联评论的报告JSON数组？

问题场景 #

有两张关联表：

• REPORT：存储报告信息，字段包括description、id_code
• REPORT_COMMENT：存储报告评论，通过report_id与REPORT.id_code关联，一对多关系

现有SQL可输出单条报告的JSON（包含对应评论数组），但无法将所有报告整合为一个JSON数组：

SELECT row_to_json(i)
 FROM (
    SELECT description, id_code,
    (    
        SELECT array_to_json(array_agg(row_to_json(c)))

            FROM (
                SELECT report_id, comment, author 
                FROM report_comment
                WHERE report_id=report.id_code
                ) c
    ) AS comments
    FROM report
 ) i;

尝试修改第一行为SELECT array_to_json(array_agg(row_to_json(i)))或SELECT json_agg(row_to_json(i))，均返回空结果。

解决办法 #

方案1：处理空评论为JSON空数组 #

原问题出在无评论的报告中，comments字段会返回NULL，导致聚合时出现异常。用COALESCE将NULL替换为空JSON数组即可：

SELECT json_agg(row_to_json(i))
FROM (
    SELECT 
        description, 
        id_code,
        COALESCE(
            (SELECT array_to_json(array_agg(row_to_json(c)))
             FROM (
                 SELECT report_id, comment, author 
                 FROM report_comment
                 WHERE report_id = report.id_code
             ) c
        ), '[]'::json) AS comments
    FROM report
) i;

方案2：用左连接+分组聚合优化性能 #

相比子查询，左连接+分组的写法更高效，且自动处理无评论的情况（生成空数组）：

SELECT json_agg(row_to_json(report_with_comments))
FROM (
    SELECT 
        r.description,
        r.id_code,
        json_agg(row_to_json(rc)) AS comments
    FROM report r
    LEFT JOIN report_comment rc ON r.id_code = rc.report_id
    GROUP BY r.id_code, r.description
) report_with_comments;

原修改返回空的原因 #

如果REPORT表存在数据但返回空，核心是无评论的报告的comments字段为NULL，部分场景下聚合函数对包含NULL字段的行处理异常；若REPORT表本身无数据，聚合自然返回空。通过上述方案处理空评论后，即可正常生成包含所有报告的JSON数组。

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