Postgresql how to return select as array

Question

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

So "Eyes" is json? And you want a json array or postgres array? Post an example of the desired output. — 404
– 404, Commented Jan 23, 2019 at 11:25

klin · Accepted Answer · 2019-01-23 11:29:02Z

1

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)

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

answered Jan 23, 2019 at 11:29

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

What, I tried exactly this many times without it working. Now that I tried it again just to double check, it works...thanks anyways haha!