如何从PostgreSQL的两张关联表中获取列存储对象

问题背景 #

我有3张列名完全一致的表，示例字段为id、name、category、price。执行关联查询时，各表字段会被重命名为带表源前缀的形式，使用起来较为不便，希望将同名字段提取为对象形式。

当前查询语句 #

SELECT 
     ta.name AS name_a,
     tb.name AS name_b,
     ta.price AS price_a,
     tb.price AS price_b,
     ta.category
FROM table_A ta
JOIN table_b tb on tb.id = ta.id

当前查询结果 #

期望结果 #

实现方案 #

根据使用的数据库类型，选择对应的JSON聚合函数实现：

1. MySQL/MariaDB #

用JSON_OBJECT函数将字段组合成JSON对象：

SELECT 
     ta.id,
     JSON_OBJECT('name_a', ta.name, 'name_b', tb.name) AS names,
     JSON_OBJECT('price_a', ta.price, 'price_b', tb.price) AS prices,
     ta.category
FROM table_A ta
JOIN table_b tb ON tb.id = ta.id

2. PostgreSQL #

用json_build_object函数构建JSON对象：

SELECT 
     ta.id,
     json_build_object('name_a', ta.name, 'name_b', tb.name) AS names,
     json_build_object('price_a', ta.price, 'price_b', tb.price) AS prices,
     ta.category
FROM table_A ta
JOIN table_b tb ON tb.id = ta.id

3. SQL Server #

用FOR JSON PATH结合子查询生成对象：

SELECT 
     ta.id,
     (SELECT ta.name AS name_a, tb.name AS name_b FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS names,
     (SELECT ta.price AS price_a, tb.price AS price_b FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS prices,
     ta.category
FROM table_A ta
JOIN table_b tb ON tb.id = ta.id

如果是三张表关联，只需在JSON函数中添加第三张表的对应字段即可，比如JSON_OBJECT('name_a', ta.name, 'name_b', tb.name, 'name_c', tc.name)。

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