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

Answer 2

Goal: I'd like get all base products.

If I assume that a "base" product is one that never appears in the related_product_id column, then not exists comes to mind:

select p.*
from product p
where not exists (select 1 
                  from  product_to_product p2p
                  where p2p.related_product_id = p.id
                 );

Answer 3

Error in your DBFiddle example is:

In last query just unnest the array instead of array_to_string

select * from product where id = ANY(select unnest(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 (1, 2, 3))

Answer 4

I don't know why your =ANY doesn't work, it seems to me like it should. Because a select can theoretically return multiple rows, it treats your array_agg kind of like the inner array of a nested array. The ANY "unnests" the first layer, but still leaves a int[] layer for the = to work with.

But your IN example works if you just get rid of the aggregation:

Since you didn't give create scripts for your tables, I've substituted ones from pgbench so that I could post tested code. The concept should apply back to your tables.

select * from pgbench_accounts where aid in (select bid from pgbench_branches);

Note that ANY also works when you don't aggregate:

select * from pgbench_accounts where aid =ANY (select bid from pgbench_branches);

List and arrays and sets are different things. But they can be used interchangeably in some situations. But I don't how to predict which ones without just trying them.