Postgresql – Group fields obtained after join as attributes of the main entity object

postgresqlquery

I work with Postgres 10.

I have 2 entities Item and Image. An Item can have multiple Image(s) so I have a Foreign key from Image to Product.

I have the following query:

SELECT name, i.image, i.type FROM item AS t
 INNER JOIN image AS i on t.id = i.item_id

By default I obtain records by Image(s), meaning the same Item can appear multiple times.

I want in the results the image to be a property of an Item object:

item.images and loop over it

It is possible to do that at the database level ?

Best Answer

In PostgreSQL, it is possible to create an array containing tuples:

SELECT name,
       (SELECT array_agg((image, type))
        FROM image
        WHERE item_id = item.id
       ) AS images
FROM item;

Alternatively, use an array containing another array:

SELECT name,
       (SELECT array_agg(ARRAY[image, type])
        ...

(Whether your language's database driver supports any of these types in the result is another question …)