how to convert array of integers after array_agg into values for IN clause

Question

Could please help me, I'm trying resolve this for a quite long time... I have table Product and RelatedProducts (top level products consist of other base products). Goal: I'd like get all base products. So, table looks like:

product_id   related_product_ids                
------------------------------------------------
1143         1213                               
1255         1245                               
1261         1229,1239,1309,1237,1305,1243,1143

I've got this by query:

select max(p.id) as product_id, array_to_string(array_agg(p2p.related_product_id), ',') as related_product_ids 
from product p 
  left join product_to_product p2p on p2p.product_id = p.id
where p.id in (select product_id from order_line where wo_id = 262834)
group by p.id, p2p.product_id

I'd like feed related_product_ids into product table to get all related products. So, actually I made array from all necessary values by running

select array_agg(p2p.related_product_id) as id 
from product p 
  left join product_to_product p2p on p2p.product_id = p.id 
where p.id in (select product_id from order_line where wo_id = 262834)

related_product_ids           
---------------------------------------------
{1309,1143,1229,1239,1243,1237,1305,1245,1213}

I tried, without success, following:

select * 
from product 
where id = ANY(select array_agg(p2p.related_product_id) as id 
               from product p 
                 left join product_to_product p2p on p2p.product_id = p.id
               where p.id in (select product_id from order_line where wo_id =  262834))

Error: ERROR: operator does not exist: integer = integer[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 39, SQLState: 42883, ErrorCode: 0

or following:

select * 
from product 
where id in (select array_to_string(array_agg(p2p.related_product_id), ',') as id 
             from product p 
               left join product_to_product p2p on p2p.product_id = p.id
             where p.id in (select product_id from order_line where wo_id = 262834))

Error: ERROR: operator does not exist: integer = integer[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 36, SQLState: 42883, ErrorCode: 0

and many other tries

So finally what I need is

select * 
from product 
where id in (1309,1143,1229,1239,1243,1237,1305,1245,1213)

(values from related_product_ids)

How to convert array of integers (related_product_ids) in to values.... Or may be you can suggest different better way?

DBFiddle

Answer 1

If you want to use the result as an array, you can do that with ANY - but the parameter has to be an array as well.

select * 
from product 
where id = any(array(select p2p.related_product_id
                     from product p 
                       left join product_to_product p2p on p2p.product_id = p.id
                     where p.id in (1, 2, 3)))

But I think you are over complicating things. As far as I can tell, this can be simplified to:

select p1.*
from product p1
where exists (select *
              from product_to_product p2p
              where p2p.related_product_id = p1.id
                and p2p.product_id in (1,2,3))