ERROR: subquery must return only one column

Question

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

Answer 1

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