我有一张桌子：
CREATE TABLE cityData ( item character varying,data jsonb );
它包含像
ITEM DATA
test1 [{"rank":"1","city":"New York"},{"rank":"3","city":"Sidney"}]
test2 [{"rank":"2","city":"NEW YORK"},{"rank":"4","city":"New Delhi"}]
我需要获得一些不同的json对象,其中城市是’纽约’
我使用以下查询
SELECT * FROM cityData t
WHERE ( data @> '[{"city":"New York"}]')
and t.item ilike '%test%';
解决方法
where lower(data::text)::jsonb @> lower('[{"city":"New York"}]')::jsonb
