0

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

2
  • Subselect returns 3 columns (select name, type, description ...) whereas ARRAY() wants one column. Think about something similar to select ARRAY(name, type, description) ...... – Akina May 7 '20 at 21:27
  • 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. – a_horse_with_no_name May 8 '20 at 13:08
3

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 ...)
1
  • 1
    I enhanced it with method json_build_object and now it looks like json_build_object('name', name, 'type', type, 'description', description) – Chitrank Dixit May 9 '20 at 4:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.