PostgreSQL more than one row returned by a subquery used as an expression

Question

Running PostgreSQL 9.6. I'm trying to output rows consisting of a value and a list of names. This is my query:

SELECT name, (SELECT car_name FROM cars WHERE user = id)
FROM users WHERE user_id = 1 ORDER BY name;

But it fails with:

ERROR:  more than one row returned by a subquery used as an expression

It of course make sense, but I would like to have the nested query to be outputted as a list or json. I've tried with row_to_json, but that fails also.

Answer 1

Use an aggregation function, such as string_agg() or json_agg():

SELECT name,
       (SELECT string_agg(car_name) FROM cars WHERE user = id)
FROM users
WHERE user_id = 1
ORDER BY name;