PostgreSQL JSONB Aggregation Array Builder

-- 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;