postgresql jsonb不区分大小写的查询 [英] postgresql jsonb case insensitive query

问题描述

我有一张桌子，像这样:

I have a table like:

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对象的数量 我正在使用以下查询

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

但是此查询输出test1行.我需要使查询不区分大小写，以便data @> '[{"city":"New York"}]'匹配New York和NEW YORK

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

推荐答案

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

这篇关于postgresql jsonb不区分大小写的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！