;--prestoselecta, array_join(array_agg(b),',')fromtablegroupbya ;
row.getArrayOfStrings（'name of the array_agg column '）
Group user roles into array: select user_id,array_agg(role_id) from user_roles group by user_id where user_id = 2; Somehow join the array of array_agg(role_id) into roles to select the role names. As you can see, I'm a bit confused on how to do #2. Is this the best way...
demo:db<>fiddle select COALESCE( json_agg(row_to_json(row(p2.pet_id::text, p2.pet_name))) FILTER (WHERE pet_id IS NOT NULL), '[]' ) as json, p1.person_name from person p1 left join pet p2 on p1.person_id = p2.owner_id group by p1.person_name; FILTER clause to fi...
Assuming that you want a JSON(B) output, you can do this with and :row_to_jsonb()``jsonb_agg() select q.*, (select jsonb_agg(row_to_json(a)) from answers a where a.question_id = q.id) answers from questions q Demo on DB Fiddle: id | type | test_id | text | answers...
row.getArrayOfStrings（'name of the array_agg column '）
This would require to use the JSON_TABLE functionality as well as the JSON_ARRAYAGG aggregation function. Recreating your situation using the following DML and DDL: CREATE TABLE categories (id INT, name VARCHAR(30)); INSERT INTO categories VALUES (1, 'Food'), (2, 'Fruit'), (3, 'Dried...