PostgreSQL中JSONB数组字段不区分大小写值匹配查询方法

嘿，我懂你的需求——现在用@>操作符做的是精确匹配，对大小写敏感，没法命中像"black"和"Black"这类大小写不同的情况对吧？我给你两种靠谱的解决方案，专门针对JSONB数组里的value字段实现不区分大小写的精确匹配：

方法一：用lower()函数统一大小写匹配 #

这种方式最稳妥，能确保严格的不区分大小写精确匹配，还能避免特殊字符（比如%）带来的意外匹配：

def by_feature_value(value)
  relation.where(
    'EXISTS (
       SELECT 1
       FROM jsonb_array_elements(features) AS elem
       WHERE lower(elem->>\'value\') = lower(?)
     )',
    value
  )
end

代码细节解释： #

• jsonb_array_elements(features)：把features这个JSONB数组拆分成单独的元素行，每个元素命名为elem
• elem->>'value'：提取elem对象里value字段的字符串值（这里用->>是因为要返回文本类型，方便后续转小写操作）
• lower()：把数据库存储的value值和传入的参数统一转成小写后再做相等判断，彻底忽略大小写差异
• EXISTS子查询：只要数组里有任意一个元素满足条件，就返回这条ProductSku记录，不会因为数组有多个匹配元素导致重复返回数据

方法二：用ILIKE操作符匹配 #

如果你习惯PostgreSQL的模糊匹配语法，也可以用ILIKE（不区分大小写的LIKE）来实现，适合简单场景：

def by_feature_value(value)
  relation.where(
    'EXISTS (
       SELECT 1
       FROM jsonb_array_elements(features) AS elem
       WHERE elem->>\'value\' ILIKE ?
     )',
    value
  )
end

⚠️ 注意：如果传入的value里包含%、_这类LIKE通配符，可能会触发意外的模糊匹配结果，所以如果是严格的精确匹配需求，方法一更安全。

为什么原代码无法实现需求？ #

你原来用的features @> ?是PostgreSQL的JSONB包含操作符，它要求传入的JSON结构和数组里的元素完全精确匹配，包括字符串的大小写，所以没法实现不区分大小写的查询，必须把数组展开后逐个元素做判断。

内容的提问来源于stack exchange，提问作者Bruno Fernandes