postgresql jsonb case insensitive query

Question

I have a table like:

CREATE TABLE cityData
(
  item character varying,
  data jsonb
);

it contains values like

ITEM        DATA
test1       [{"rank":"1", "city":"New York"},{"rank":"3", "city":"Sidney"}] 
test2       [{"rank":"2", "city":"NEW YORK"},{"rank":"4", "city":"New Delhi"}] 

I need to get number of distinct json objects where city is 'New York' I am using the following query

SELECT  * FROM   cityData t
WHERE ( data @> '[{"city":"New York"}]')
and t.item ilike '%test%';

But this query outputs test1 row. I need to make the query case insensitive so that data @> '[{"city":"New York"}]' matches both New York and NEW YORK

Answer 1

where lower(data::text)::jsonb @> lower('[{"city":"New York"}]')::jsonb

Answer 2

With PostgreSQL 12+, case-insensitive matches can be done using @? operator and a jsonpath query. Following the example in question, your clause might looks something along the lines of:

where data @? '$[*].city like_regex "(?i)^NEW YORK$"'

This can be significantly faster without building specific case-insensitive indexes, as opposed to casting JSONB to string and back to use lower().