Query Postgres JSON array for values in text

Question

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

• eComEvo: @McNets The names column is not jsonb...it is plain json.
• McNets: Is there only one element by line?
• eComEvo: Only one. It is a flat array of values only.

Answer 1

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

Answer 2

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)
 )