如何在PostgreSQL函数中从三张关联表返回嵌套JSON？

直接上可以实现需求的SQL代码：

SELECT
  json_agg(structure) AS json_object
FROM
  (
    SELECT
      json_build_object(
        'id', p.id,
        'name', p.name,
        'outputs', COALESCE(json_agg(o.output_with_components), '[]'::json)
      ) AS structure
    FROM
      projects p
      LEFT JOIN (
        SELECT
          o.project_id,
          json_build_object(
            'id', o.id,
            'name', o.name,
            'components', COALESCE(json_agg(c.comp), '[]'::json)
          ) AS output_with_components
        FROM
          outputs o
          LEFT JOIN (
            SELECT
              output_id,
              json_build_object('id', id, 'name', name) AS comp
            FROM components
          ) c ON c.output_id = o.id
        GROUP BY o.id, o.name, o.project_id
      ) o ON o.project_id = p.id
    GROUP BY p.id, p.name
  ) structure;

改动思路拆解 #

1. 最内层：关联outputs和components
   在原来的output子查询里，新增和components的LEFT JOIN，用json_agg(c.comp)把每个output对应的组件汇总成数组，再用COALESCE确保没有组件时返回空数组[]而非null。

2. 中间层：组装带components的output对象
   修改output子查询的json_build_object，加入'components'字段，把生成的组件数组嵌套进去，让每个output对象包含自己的components列表。

3. 外层：保持projects的聚合逻辑
   外层projects的关联逻辑基本不变，只是把原来的o.oj替换为新的o.output_with_components，同样用COALESCE处理没有outputs的情况，保证结构一致性。

效果验证 #

执行这段SQL后，返回的JSON结构会完全匹配你的期望：每个project包含outputs数组，每个output又包含自己的components数组；即使某个project没有outputs、或某个output没有components，都会返回空数组而非null，避免结构异常。

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