How would I query a JSON array column such as this for text within each of the array values and only return those rows that have them?
["Mr Smith","Ms Wellington","Mr Anderson"]
I've tried these but neither return results when I know they exist:
select * from people where names::jsonb ? 'Mr';
select * from people where names::jsonb @> 'Mr';
On this case you can cast the jsonb as text and use regular LIKE operator.
create table test(data jsonb); insert into test values ('["Mr Smith","Ms Wellington","Mr Anderson"]'::jsonb); insert into test values ('["Md Smith","Ms Wellington","Md Anderson"]'::jsonb); insert into test values ('["Mg Smith","Ms Wellington","Mr Anderson"]'::jsonb); select * from test where data::text like'%Mr%';| data | | :------------------------------------------- | | ["Mr Smith", "Ms Wellington", "Mr Anderson"] | | ["Mg Smith", "Ms Wellington", "Mr Anderson"] |
dbfiddle here
There is one general way to test array elements for conditions more complex then just equality:
select * from your_table
where (
select
bool_or(<condition on x>) -- for any element
bool_and(<condition on x>) -- for all elements
from
unnest(array_column) as t(x) -- for regular arrays
json_array_elements_text(array_column) as t(x) -- for JSON arrays
jsonb_array_elements_text(array_column) as t(x) -- for JSONB arrays
)
So for your case it could be
select * from people
where (
select
bool_or(x ilike 'Mr %')
from
json_array_elements_text(name) as t(x)
)
namescolumn is notjsonb...it is plainjson.