Query Postgres JSON array for values in text 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 @&gt; '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                                         |
| :------------------------------------------- |
| [&quot;Mr Smith&quot;, &quot;Ms Wellington&quot;, &quot;Mr Anderson&quot;] |
| [&quot;Mg Smith&quot;, &quot;Ms Wellington&quot;, &quot;Mr Anderson&quot;] |



dbfiddle here