ERROR: subquery must return only one column

I have two tables one is Category and other one is Product.

Table Description are

Category

• name

• description

• parent_id (self referencing key) (can be upto 3 levels)

Product

• name

• description

• type

-
category_id (foreign key to Category table)

I want to show all the categories on a single along with the associated products. So for getting associated categories I use the below query. I am able to do the left join at the self referential tables but I am not able to get list of product data, as this is a subquery and subquery would only return a single column.

select                                                                                                                                                                                                      
               cat1.id, ARRAY(select name, type, description from product where
               product.category_id = cat1.id)
               as category_1_products_data,

               cat2.id, ARRAY(select name, type, description from product where
               product.category_id = cat2.id)
               as category_2_products_data,

               cat3.id, ARRAY(select name, type, description from product where
               product.category_id = cat3.id)
               as category_3_products_data
    from       category cat1
    left join  category cat2
    on         cat2.parent_id = cat1.id
    left join  category cat3
    on         cat3.parent_id = cat2.id
    where      cat1.parent_id is null;

ERROR: subquery must return only one column
LINE 2: cat1.id, (select name, type, description from ...

All elements of an array must have the same type; when constructing an array with a subquery, the simplest way to enforce this is to demand that the query returns exactly one column.

But you can make the subquery return a single column whose type is a composite type by using a row constructor:

ARRAY(SELECT ROW(name, type, description) FROM ...)

StackExchange Database Administrators Q#266721, answer score: 9