PostgreSQL使用LEFT JOIN将关联表字段输出为嵌套JSON对象的查询方案

核心SQL语句 #

直接使用PostgreSQL原生的json_build_object函数构造嵌套的customer对象，查询结果无需二次处理即可符合要求的格式：

SELECT 
  r.req_id::text,
  r.details,
  json_build_object(
    'cust_id', c.cust_id::text,
    'full_name', c.full_name
  ) AS customer
FROM request_tbl r
LEFT JOIN customers_tbl c ON r.customer_id = c.cust_id;

注：语句中的::text是为了匹配你示例中字符串类型的id值，如果你的表字段本身就是字符串类型，可以去掉该强转语法。

Node.js pg包调用示例 #

const { Pool } = require('pg');
// 数据库连接配置可根据实际情况修改
const pool = new Pool({
  user: '数据库用户名',
  host: '数据库地址',
  database: '数据库名',
  password: '数据库密码',
  port: 5432,
});

async function getRequestList() {
  const { rows } = await pool.query(`
    SELECT 
      r.req_id::text,
      r.details,
      json_build_object(
        'cust_id', c.cust_id::text,
        'full_name', c.full_name
      ) AS customer
    FROM request_tbl r
    LEFT JOIN customers_tbl c ON r.customer_id = c.cust_id;
  `);
  // 直接执行JSON.stringify(rows)即可得到你要求的输出结构
  return rows;
}

补充说明 #

• 如果你需要过滤掉没有关联客户的请求记录，将语句中的LEFT JOIN替换为INNER JOIN即可
• 没有关联客户的请求记录返回时，customer字段值会自动为null，符合JSON格式规范

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