CODEWITHSUNDEEP
arraysjsonpostgresqljsonb
Chris
Chris

Reputation: 2168

Postgres - JSONB - Nested Columns - Query Nested Jsonb array column

I have a table with the following structure -

Column Name | Data Type
--------------------------
user_id     | uuid
profile     | jsonb

An example profile field would be something like -

{ "data": { "races": [ "white", "asian" ] } }

I want to query this table for users contain one of the following races (for example) - "white", "african american"

I would expect my example user above to be returned since their races field contains "white".

I have tried something like this with no success -

SELECT user_id from table
WHERE profile -> 'data' ->> 'races' = ANY('{"white", "african american"}')

Using Postgres 13.x

Thank you!

Upvotes: 0

Views: 1268

Answers (2)

Schwern
Schwern

Reputation: 164679

tl;dr use the ?| operator.

There's two problems with your query.

->> returns text not jsonb. So you're asking if the text ["white", "asian"] matches white or african american.

You probably did that because otherwise you got type errors trying to use any with JSONB. any wants a Postgres array of things to compare, and it has to be an array of jsonb. We can do that...

select user_id
from user
where profile -> 'data' -> 'races' = ANY(array['"white"', '"african american"']::jsonb[]);

But this has the same problem as before, it's checking if the json array [ "white", "asian" ] equals "white" or "african american".

You need an operator which will match against each element of the JSON. Use the ?| operator.

select user_id
from users
where profile -> 'data' -> 'races' ?| array['white', 'african american'];

Upvotes: 1

klin
klin

Reputation: 121534

Use the ?| operator:

select user_id 
from my_table
where profile -> 'data' -> 'races' ?| array['white', 'african american']

According to the documentation:

jsonb ?| text[] -> boolean

Do any of the strings in the text array exist as top-level keys or array elements?

Upvotes: 2

Related Questions