Postgres如何将一对一关联表LEFT JOIN结果转为Book对象的Author属性

嘿，这个问题我刚好碰到过！首先得明确一点：纯SQL本身返回的结果都是扁平的行数据，没法直接输出嵌套的对象结构——你现在得到的a_id、name这种单独字段就是SQL结果的典型形态。不过要得到你想要的{ id, title, author_id, author: {id, name} }格式，有两种常用的解决办法，我给你详细说说：

方法1：在应用层代码里转换（通用所有数据库） #

这是最通用的方案，不管你用什么后端语言（Python、JS、Java啥的）都能搞定。思路很简单：先执行你现有的LEFT JOIN查询拿到扁平结果，再把author相关的字段手动组装成嵌套对象。

举个JavaScript的例子：

// 假设从数据库查回来的单行数据是这样的
const dbRow = { id: 1, title: "The Great Gatsby", author_id: 2, a_id: 2, name: "F. Scott Fitzgerald" };

// 转换成你要的嵌套格式
const book = {
  id: dbRow.id,
  title: dbRow.title,
  author_id: dbRow.author_id,
  author: {
    id: dbRow.a_id,
    name: dbRow.name
  }
};

再给个Python版本的参考：

# 假设查询结果是一个字典
db_row = {"id": 1, "title": "The Great Gatsby", "author_id": 2, "a_id": 2, "name": "F. Scott Fitzgerald"}

book = {
    "id": db_row["id"],
    "title": db_row["title"],
    "author_id": db_row["author_id"],
    "author": {
        "id": db_row["a_id"],
        "name": db_row["name"]
    }
}

这种方法的好处是兼容性拉满，不管什么数据库都能用，而且后续要改字段或者加逻辑也很灵活。

方法2：用数据库的JSON函数直接生成嵌套结构（适合支持JSON的数据库） #

如果你的数据库是MySQL 5.7+、PostgreSQL 9.4+这类支持JSON操作的，可以直接在SQL里构造出嵌套的JSON对象，省掉应用层的转换步骤。

MySQL 示例 #

用JSON_OBJECT函数把author的字段打包成JSON对象：

SELECT 
  b.id,
  b.title,
  b.author_id,
  JSON_OBJECT('id', a.id, 'name', a.name) AS author
FROM books b
LEFT JOIN authors a ON a.id = b.author_id
WHERE b.id = 1;

查出来的author字段就是一个现成的JSON对象，应用层直接解析用就行。要是担心没有作者的情况（LEFT JOIN会返回NULL），可以用COALESCE给个默认空对象：

SELECT 
  b.id,
  b.title,
  b.author_id,
  COALESCE(JSON_OBJECT('id', a.id, 'name', a.name), JSON_OBJECT()) AS author
FROM books b
LEFT JOIN authors a ON a.id = b.author_id
WHERE b.id = 1;

PostgreSQL 示例 #

用json_build_object函数实现类似效果：

SELECT 
  b.id,
  b.title,
  b.author_id,
  json_build_object('id', a.id, 'name', a.name) AS author
FROM books b
LEFT JOIN authors a ON a.id = b.author_id
WHERE b.id = 1;

要是想直接返回整个book的JSON对象，还可以把所有字段都包进去：

SELECT 
  json_build_object(
    'id', b.id,
    'title', b.title,
    'author_id', b.author_id,
    'author', json_build_object('id', a.id, 'name', a.name)
  ) AS book
FROM books b
LEFT JOIN authors a ON a.id = b.author_id
WHERE b.id = 1;

小提醒 #

• 用LEFT JOIN的时候要注意：如果某本书没有对应的作者，author字段会是NULL（应用层处理时记得判空，或者用上面说的COALESCE给默认值）。
• 应用层转换更灵活，适合需要对数据做后续加工的场景；数据库端生成JSON则适合直接给前端返回JSON数据的情况，能少写点代码~

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