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

TopLevel/Final products consist of so called "base" products(related_product_id(s). All products in same product table. — Igor Choriy
– Igor Choriy, Commented May 16, 2020 at 16:00
Unrelated, but: array_to_string(array_agg(p2p.related_product_id), ',') can be simplified to sring_agg(p2p.related_product_id, ',') — user330315
– user330315, Commented May 16, 2020 at 18:23

user330315 · Accepted Answer · 2020-05-16 18:27:00Z

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

answered May 16, 2020 at 18:27

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Igor Choriy Over a year ago

Thank you for reminder. Sorry, І've first time asked question here. :) Already marked

Igor Choriy Over a year ago

is it possible rewrite that 2nd query to JOINs to avoid using function exists(or any other). I'm trying translate that query to JPA.

user330315 Over a year ago

@IgorChoriy: a join would change the meaning of the query (especially when you have a one-to-many relationship). Are you saying JPA doesn't even support the most basic operators like EXISTS? I don't think much of obfuscation layers in general, but that seems nidicolous.

Gordon Linoff · Accepted Answer · 2020-05-16 12:51:23Z

0

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

answered May 16, 2020 at 12:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Igor Choriy Over a year ago

I probably mislead you by these "base". I called it "base" because TopLevel/Final product consist of so called "base" products. So, unfurtunately I need product id(s) that are in related_product_id(s)

jjanes · Accepted Answer · 2020-05-16 18:18:38Z

0

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

edited May 16, 2020 at 18:18

answered May 16, 2020 at 15:05

jjanes

45k5 gold badges39 silver badges48 bronze badges

1 Comment

jjanes Over a year ago

Yes, but that error doesn't make any sense to me. The entire point of ANY is to allow a particular kind of type mismatch. Isn't this that type?

Akhilesh Mishra · Accepted Answer · 2020-05-17 05:35:19Z

0

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

edited May 17, 2020 at 5:35

answered May 16, 2020 at 13:31

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

2 Comments

Igor Choriy Over a year ago

Thank you very much! It is what I tried to achive. Funny I tried with unnest and any, but could make it work.

Igor Choriy Over a year ago

If you can edit your answer, it has extra parenthesis. Correct will be

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

dbfiddle.uk/…

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related