Postgres: How to display multiple values from a sub-query

I recently needed to generate a query where I needed to return the results from a joined table as part of a single result row for a Postgres SQL query.

So for context imagine I had a Person table, a Colour table and a FavouriteColours link table that links the primary key of the Person and the Colour so that each person can have a variable number of favourite colours from zero to all of them.

In my query I wanted to present a result set where each row was a person and all their favourite colours were listed in the result row, so I could export all the data.

The naive approach uses a sub-query within the projection result, like so:

SELECT colour.name
FROM colour
INNER JOIN favourite_colours
WHERE colour.id = favourite_colours.colour_id
AND favourite_colours.person_id = person.id

This failed however with an error message that roughly read "an expression from the sub-query returned multiple rows". Interestingly Postgres only raises this issue if multiple rows actually exist. If coincidentally all the people in the query one had one favourite colour then the query would run and then fail later when someone less decisive comes along.

Historically you might have dealt with this situation in a bit of hacky way by trying to turn the results of the sub-query into a string by joining them with a library function but fortunately modern PSQL has a better solution in the form of the ARRAY type.

In addition to being a column type this can be applied to the result of a query to turn the results into a single array.

ARRAY (SELECT colour.name
FROM colour
INNER JOIN favourite_colours
WHERE colour.id = favourite_colours.colour_id
AND favourite_colours.person_id = person.id)

Now the query will return a single value that will display and be exported in the form: {'Blue', 'Yellow'}.

You can debug these sub-queries by just adding SELECT in front the ARRAY to see just that list which will contain all the favourite colours.