PostgreSQL中连接表后无需逐个指定字段返回JSON对象的方法

当然可以实现，PostgreSQL提供了专门的函数来直接将整行记录转为JSON对象，无需逐个列明字段，以下是两种常用方法：

方法1：使用 row_to_json() 函数 #

这是最直接的方式，传入表别名即可将该表的整行数据转为JSON对象，自动包含所有字段：

SELECT u.id, u.username, row_to_json(p) AS post_info
FROM "user" u
JOIN post p ON u.id = p.user_id;

执行后，post_info 字段会是一个包含post表所有字段的JSON对象，键为字段名，值为对应字段的内容。

方法2：使用 to_json() 函数 #

效果和 row_to_json() 基本一致，同样支持直接传入表别名生成整行JSON：

SELECT u.id, u.username, to_json(p) AS post_info
FROM "user" u
JOIN post p ON u.id = p.user_id;

进阶：排除特定字段（PostgreSQL 12+） #

如果不需要包含表的所有字段，又不想逐个列明需要的字段，可以利用 EXCLUDE 语法排除指定字段：

SELECT u.id, u.username, row_to_json(p.* EXCLUDE (id, created_at)) AS post_info
FROM "user" u
JOIN post p ON u.id = p.user_id;

这样生成的JSON对象会自动包含post表除id和created_at之外的所有字段。

对比原写法 #

你之前的手动指定字段写法：

SELECT u.id, u.username, 
       json_build_object('name', p.name, 'content', p.content) AS post_info
FROM "user" u
JOIN post p ON u.id = p.user_id;

使用上述函数后，不仅无需手动维护字段列表，后续表结构变更（新增字段）时，JSON对象也会自动包含新字段，无需修改SQL语句。

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