聚合查询结果

Question

我有两个表，第二个表包含一个引用第一个表主键的外键。

第一张桌子" house "(id,title，城市，国家)，第二表"图像";(id、名称、house_id)

我正在实现以下查询:

SELECT * FROM houses INNER JOIN images ON houses.id = images.house_id;

结果是一个重复数据数组，除了字段名:

[ 
{
id:1,
title: "house1",
city:"c1",
country:"country2",
name:"image1",
house_id: 2
},
{
id:2,
title: "house1",
city:"c1",
country:"country2",
name:"image2",
house_id: 2
},
{
id:3,
title: "house1",
city:"c1",
country:"country2",
name:"image3"
house_id: 2,
},
]

我如何调整查询以获得如下结果:

[
{
id:2,
title: "house1",
city:"c1",
country:"country2",
imagesNames:["image1","image2","image3"]
house_id: 2,
}
]

使用膝关节是否可行?我用的是PostgreSQL数据库

Answer 1

GROUP BY所有节点共享的列，以及聚合名称。如:

SELECT h.id, h.title, h.city, h.country
, array_agg(name) AS images_names
, i.house_id -- redundant?
FROM   houses h
JOIN   images i ON h.id = i.house_id;
GROUP  BY h.id, h.title, h.city, h.country, i.house_id;