PostgreSQL JSONB 聚合构建嵌套文档

示例说明

主从数据转 API 响应通常要两步：查主表、查从表、应用层拼装。jsonb_agg 让数据库一次成型：GROUP BY 主表后聚合从表行为数组，jsonb_build_object 按键名组织字段。FILTER 子句配合 LEFT JOIN 的全 NULL 行做兜底——没有条目的订单得到空数组而不是含 null 的单元素数组。

技术要点

• jsonb_agg 聚合行集为 JSON 数组
• FILTER 条件聚合排除空连接行
• jsonb_object_agg 键值对转对象

代码解读

主查询 LEFT JOIN 条目表后按订单分组：jsonb_agg 内层逐条目构造对象并按行号排序，外层 COALESCE 把无条目时的 NULL 换成空数组；时间戳用 to_char 定制 ISO 形态字符串。第二个查询把标签表的键值行转成单对象——数组的 ORDER BY 保证条目顺序稳定。

使用建议

聚合输出的 JSON 形态要与序列化层约定一致。大数据量聚合注意内存与网络膨胀。应用层拼装更灵活，SQL 拼装更快，按团队栈选型。

代码示例

-- PostgreSQL 订单与其条目一次查询聚合成嵌套 JSON
SELECT
  jsonb_build_object(
    'order_id', o.id,
    'status', o.status,
    'total_cents', o.total_cents,
    'placed_at', to_char(o.placed_at, 'YYYY-MM-DD"T"HH24:MI:SS'),
    'items', COALESCE(
      jsonb_agg(
        jsonb_build_object(
          'sku', i.sku,
          'quantity', i.quantity,
          'unit_cents', i.unit_cents
        ) ORDER BY i.line_no
      ) FILTER (WHERE i.id IS NOT NULL),
      '[]'::jsonb
    )
  ) AS order_document
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.id IN ('ord-1', 'ord-2')
GROUP BY o.id, o.status, o.total_cents, o.placed_at
ORDER BY o.id;

-- 数组打包键值对：标签表转对象
SELECT
  t.order_id,
  jsonb_object_agg(t.tag_key, t.tag_value) AS tags_object
FROM order_tags t
GROUP BY t.order_id;