PostgreSQL单查询获取主表行及关联子表行数组实现方法

PostgreSQL 内置了完整的 JSON 构造、聚合函数，完全可以通过单条 SQL 直接输出和你预期结构一致的结果，性能优于两次独立查询，也不需要放弃范式化表结构把商品项冗余存为 JSONB 列。

核心查询SQL #

单订单/批量订单通用写法（JOIN+聚合） #

SELECT
  json_build_object(
    'id', o.id,
    'total', o.total,
    'items', COALESCE(
      json_agg(
        json_build_object(
          'name', i.name,
          'url', i.url
        )
        ORDER BY i.id -- 可按需调整商品项排序规则，比如按创建时间排序
      ) FILTER (WHERE i.id IS NOT NULL),
      '[]'::json
    )
  ) AS order_json
FROM orders o
LEFT JOIN items i ON o.id = i.order_id
-- 查单个订单就传单个id，批量查就改写成 o.id IN (12345, 12346) 即可
WHERE o.id = 12345
GROUP BY o.id, o.total;

子查询写法（无需GROUP BY，逻辑更直观） #

SELECT
  json_build_object(
    'id', o.id,
    'total', o.total,
    'items', COALESCE(
      (
        SELECT json_agg(
          json_build_object('name', i.name, 'url', i.url)
          ORDER BY i.id
        )
        FROM items i
        WHERE i.order_id = o.id
      ),
      '[]'::json
    )
  ) AS order_json
FROM orders o
WHERE o.id = 12345;

语法说明 #

• json_build_object：按传入的键值对顺序构造JSON对象，字段名、字段值完全自定义
• json_agg：将分组/子查询返回的多行结果聚合成JSON数组，对应结构里的items数组
• FILTER (WHERE i.id IS NOT NULL)：处理订单无关联商品的场景，避免聚合出[null]的无效结构
• COALESCE：兜底逻辑，当订单没有关联商品时直接返回空数组[]，保证输出结构一致
• 如果需要输出JSONB格式，把所有json_开头的函数替换成jsonb_即可，性能差异极小

性能优化建议 #

1. 你当前只给items表的主键id建了索引，建议补充外键字段的索引，关联查询时可以直接走索引扫，避免全表扫描：

   CREATE INDEX idx_items_order_id ON items(order_id);
   

   加完该索引后，上述查询的性能基本和单表查询持平，远高于两次独立查询的方案（减少了一次数据库网络往返，高并发场景下收益非常明显）。
2. 如果不需要返回商品表的自增id等内部字段，上述写法已经是最优，不需要额外字段冗余。

node-postgres 驱动使用示例 #

PG驱动会自动把PostgreSQL返回的JSON类型解析为原生JS对象，不需要额外做JSON.parse处理，直接取值即可：

const { Pool } = require('pg');
const pool = new Pool(); // 填入你的数据库连接配置

async function getOrder(orderId) {
  const { rows } = await pool.query(`
    SELECT
      json_build_object(
        'id', o.id,
        'total', o.total,
        'items', COALESCE(
          json_agg(
            json_build_object(
              'name', i.name,
              'url', i.url
            ) ORDER BY i.id
          ) FILTER (WHERE i.id IS NOT NULL),
          '[]'::json
        )
      ) AS order_data
    FROM orders o
    LEFT JOIN items i ON o.id = i.order_id
    WHERE o.id = $1
    GROUP BY o.id, o.total
  `, [orderId]);
  return rows[0]?.order_data || null;
}

调用上述方法返回的结果结构和你业务接口接收的JSON结构完全一致，不需要额外做格式转换。

你考虑的将商品项直接存为orders表JSONB列的方案确实不是最优选择，拆分为两张关联表的范式化设计更利于后续商品维度的统计、关联查询、单独更新操作，配合上述JSON构造函数完全可以兼顾存储规范性和接口输出便利性。

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