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

Question

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!

You have a typo in your JSONB. The key is data:. It should be data. That doesn't fix your problem. — Schwern
– Schwern, Commented Jan 13, 2022 at 23:15
Good catch. Just fixed. Any thoughts on a solution to this problem @Schwern? — Chris
– Chris, Commented Jan 13, 2022 at 23:17

klin · Accepted Answer · 2022-01-13 23:25:07Z

2

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?

answered Jan 13, 2022 at 23:25

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

Schwern · Accepted Answer · 2022-01-13 23:34:41Z

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'];

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related