CODEWITHSUNDEEP
sqlpostgresql
Freddy Bonda
Freddy Bonda

Reputation: 1259

Postgresql how to return select as array

IN POSTGRESQL:

Say I have a table of Users and I do:

SELECT "EyeColor" FROM "Users" WHERE "Age" = 32

And this returns:

[
    {"EyeColor": "blue"},
    {"EyeColor": "green"},
    {"EyeColor": "blue"}
]

Then I want to put blue, green, blue into an array and use that. This is the closest I can get but it is not working:

SELECT * FROM "Eyes" WHERE "Color" IN
(SELECT array_agg("EyeColor") FROM "Users" WHERE "Age" = 32)

I want the anpve query to work the same as this:

SELECT * FROM "Eyes" WHERE "Color" IN ('blue', 'green')

Upvotes: 0

Views: 93

Answers (1)

klin
klin

Reputation: 121834

You do not need to aggregate the subquery result into an array. You can use IN (subquery):

SELECT * 
FROM "Eyes" 
WHERE "Color" IN (
    SELECT "Eyes" 
    FROM "Users" 
    WHERE "Age" = 32)

or ANY (subquery): 

SELECT * 
FROM "Eyes" 
WHERE "Color" = ANY(
    SELECT "Eyes"
    FROM "Users" 
    WHERE "Age" = 32)

Upvotes: 1

Related Questions