[SQL] PostgreSQL : subselect = any(?)

For my indexation mechanism, i'm trying to write a where clause based on subselect using PostgreSQL.

I have a ManyToOne relation (1 Entity A referenced by x Entity B), in my select Entity A, i try to add a where clause on the Entity B foreign key.

I tried a left join but i don't want to have the same result multiples times. I can use a distinct, it works but it have poor performance.

I tried something like this but it's not a valid syntax

select ...
from entity_a entA
where (select array_agg(entB.id) from entity_b entB where entB.fkEntA= entA.id) = any(?)


(The ? is replaced by one or multiples ids in my JDBC query and represent some entity B ids).

What i ask for help :

What changes should i do to the following where clause to get it working.

where (select array_agg(entB.id) from entity_b entB where entB.fkEntA= entA.id) = any(?)


EDIT :

The expected result doesn't really matter, my SQL result contains a lot a complex select statements and i push the result of the query to ElasticSearch. I just check how to make a where clause without using left join and distinct.

For the statement

select ...
from entity_a entA
where (select array_agg(entB.id) from entity_b entB where entB.fkEntA= entA.id) = any(?)


I get

sql error 42809 : Op ANY/ALL (array) requires array
​

but i don't find how to transfom any(?) as array

I found some currently working solutions but they are not optimized

Solution 1 : distinct + left join

select distinct ...
from entity_a entA
left join entity_b entB on entB.fkEntA = entA.id
where entB.id = any(?)


Solution 2 : multiple where clause

select ...
from entity_a entA
where (? in (select array_agg(entB.id) from entity_b entB where entB.fkEntA= entA.id)
or ? in (select array_agg(entB.id) from entity_b entB where entB.fkEntA= entA.id))

Continue reading...