CODEWITHSUNDEEP
sqlarraysjsonpostgresqlwhere-clause
aletede91
aletede91

Reputation: 1166

Search strings in json array column

I have this PostgreSQL table:

id | something
1  | ["something1", "something2", "something3"]
2  | ["something1"]
3  | ["something2", "something4"]

I am using this query to get all the datas having the string something1 in the something column:

select * from my_table where (something)::jsonb ? 'something1'

How can i modify (or also there's a better way) this query to get all the datas that contains something1 OR something2?

Upvotes: 0

Views: 38

Answers (1)

GMB
GMB

Reputation: 222482

You can use ?:

where something::jsonb ? 'something1'

To check for several possible values, use ?| against a text array:

where something::jsonb ?| array['something1', 'something2']

This checks if any value from the array exists in the jsonb array. If you want to check if all array elements exist in the jsonb payload, then use ?& instead.

Upvotes: 1

Related Questions