如何用单条PostgreSQL查询构建含数组对象的嵌套JSON

问题描述 #

现有三张表level_one_table、level_two_table、level_three_table，表间关系如下：

• level_one_table与level_two_table为一对多关系
• level_two_table与level_three_table为一对一关系

需要通过单条SQL查询返回嵌套JSON结构：

• 每个level_one_table对象包含自身所有字段，以及一个由关联level_two_table对象组成的数组
• 每个level_two_table对象中要嵌套对应的level_three_table对象

目前采用循环查询的方式，尝试过json_build_object但不知道如何将多行level_two_table转为数组，同时实现level_three_table的嵌套。

表结构示例：

level_one_table

level_two_table

level_three_table

当前尝试的SQL语句：

SELECT json_build_object(
  'id', t0.id,
  'level_two_table': t2.*
   ?? make level_three_table inside level_two_table as an object
) FROM level_one_table t0 
  LEFT JOIN level_two_table t1 ON t0.id = t1.fk_level_one_id
  LEFT JOIN level_three_table t2 ON t1.id = t2.fk_level_two_id

解决方案 #

可以通过嵌套聚合实现需求：先将level_two_table与对应的level_three_table组装成嵌套对象，再用json_agg将同一level_one_table下的所有level_two对象聚合为数组，最后与level_one_table字段组合成最终JSON。

手动枚举字段版本（精准控制返回字段）

SELECT
  json_build_object(
    'id', t0.id,
    -- 按需添加level_one_table的其他字段，例如'name', t0.name
    'level_two_list', json_agg(
      json_build_object(
        'id', t1.id,
        'fk_level_one_id', t1.fk_level_one_id,
        -- 按需添加level_two_table的其他字段
        'level_three', json_build_object(
          'id', t2.id,
          'fk_level_two_id', t2.fk_level_two_id
          -- 按需添加level_three_table的其他字段
        )
      ) FILTER (WHERE t1.id IS NOT NULL) -- 避免无关联level_two时生成null元素
    )
  ) AS result_json
FROM level_one_table t0
LEFT JOIN level_two_table t1 ON t0.id = t1.fk_level_one_id
LEFT JOIN level_three_table t2 ON t1.id = t2.fk_level_two_id
GROUP BY t0.id;

自动取全字段版本（简化代码）

如果需要直接返回表的所有字段，无需手动枚举，可使用row_to_json简化：

SELECT
  json_build_object(
    'id', t0.id,
    -- 按需添加level_one_table的其他字段
    'level_two_list', json_agg(
      json_build_object(
        'level_two', row_to_json(t1),
        'level_three', row_to_json(t2)
      ) FILTER (WHERE t1.id IS NOT NULL)
    )
  ) AS result_json
FROM level_one_table t0
LEFT JOIN level_two_table t1 ON t0.id = t1.fk_level_one_id
LEFT JOIN level_three_table t2 ON t1.id = t2.fk_level_two_id
GROUP BY t0.id;

关键说明 #

1. json_agg聚合数组：通过GROUP BY t0.id将同一level_one下的level_two记录聚合为数组，解决一对多的数组转换问题。
2. 嵌套json_build_object：在聚合内部完成level_two与level_three的嵌套组装，保证JSON结构层级正确。
3. FILTER过滤空值：当level_one无关联level_two时，过滤后会返回空数组而非包含null的数组，更符合业务预期。

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