I'm trying to iterate over an array field in order to use every record as a parameter to query and finally join all results, but I need help to get it.
I have a table with an array field called fleets
and it can have one or more values ie. {1,2,3}
I need iterate over every value to get all vehicles belonging to these fleets.
With a subquery I'm getting 3 rows with these values 1
,2
,3
SELECT * FROM vehicles WHERE fleet_fk=(
SELECT unnest(fleets) FROM auth_user WHERE id=4)
I'm using PostgreSQL 9.4.
Since
fleets
is an array column you have a couple of options.Either use the
ANY
construct directly (no need tounnest()
):Or rewrite as join:
Or you can
unnest()
, then you don't needANY
any more:This is assuming you don't have another column named
fleet_fk
inauth_user
. Use the more explicitON
clause for the join in this case to avoid the ambiguity.Be aware that there are two implementation for
ANY
.ANY
for setsANY
for arraysBehavior of the beasts is basically the same, you just feed them differently.
DB design
Consider normalizing the hidden many-to-many (or one-to-many?) relationship in your DB schema: