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

• Akina: Subselect returns 3 columns (select name, type, description ...) whereas ARRAY() wants one column. Think about something similar to select ARRAY(name, type, description) ......
• user1822: I would probably aggregate the product information into a JSONB array which makes it easier to retrieve and read the individual columns from the product table.

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

• Chitrank Dixit: I enhanced it with method json_build_object and now it looks like json_build_object('name', name, 'type', type, 'description', description)