Search strings in json array column

Question

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?

GMB · Accepted Answer · 2020-10-02 13:19:01Z

1

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.

edited Oct 2, 2020 at 13:19

answered Oct 2, 2020 at 13:14

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

aletede91 Over a year ago

This is exactly what I was looking for, thanks man!

mobeen Over a year ago

@aletede91 can you tell how to write same query in mysql with same scenario ?

Collectives™ on Stack Overflow

Search strings in json array column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related