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 ...)