2

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';
3
  • @McNets The names column is not jsonb...it is plain json.
    – eComEvo
    May 4 '17 at 18:16
  • Is there only one element by line?
    – McNets
    May 4 '17 at 18:38
  • Only one. It is a flat array of values only.
    – eComEvo
    May 4 '17 at 18:39
2

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.