Aggregate query result-CodePudding

I have two tables, the second table contains a foreign key which references the first table primary key.

First table "Houses" (id,title,city,country), Second table "Images" (id,name,house_id)

I am implementing the following query:

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

The result is an array of repeated data except for a field name:

[ 
  {
    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,
  },
]

How could I adjust the query to get the result like the following:

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

Is it doable using knex? I am using a PostgreSQL database.

CodePudding user response：

GROUP BY all columns shared by all peers, and aggregate names. Like:

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;