PostgreSQL JSONB不区分大小写查询

Question

我有一个类似于以下结构的表：

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

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

- ranjan

Answer 1

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

- Clodoaldo Neto

---

将其转换为文本，然后再转换为jsonb是否高效？ - ranjan

@ranjan 不要使用 JSON 作为结构化数据。 - Clodoaldo Neto

不要使用 JSON 作为结构化数据？你是什么意思？ - dwanderson

@dwanderson：结构化数据是易于规范化的数据。 - Clodoaldo Neto

Answer 2

在PostgreSQL 12+中，可以使用@?操作符和jsonpath查询进行不区分大小写的匹配。按照问题中的示例，您的子句可能类似于：

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

如果不构建特定的不区分大小写索引，而是将JSONB强制转换为字符串并使用lower()函数，则可以显著提高速度。

- Anton Strogonoff

---

你知道路径本身可能有不同大小写时，是否可以使用类似的语法吗？在上面的示例中，可能会有使用"CITY"而不是"city"的行... - undefined

@JulienGenestoux 我之前研究过这个问题，但似乎并不确定。如果你不知道有哪些键，也许可以使用文档中列出的json_object_keys()函数提取实际的键，然后进行处理。不过最终的效果如何，我也不太清楚…… - undefined