如何针对存储嵌套数组对象的JSONB类型列执行ILIKE查询？

针对JSONB类型列category_products（结构为外层数组，每个元素包含products子数组，子数组元素为带product_name属性的对象），以下是实现product_name大小写不敏感模糊查询的具体方案：

基础查询：匹配任意符合条件的行 #

如果只需要返回表中存在匹配product_name的行，使用EXISTS子查询效率更高：

SELECT *
FROM your_table
WHERE EXISTS (
  SELECT 1
  -- 展开外层category数组
  FROM jsonb_array_elements(your_table.category_products) AS cat_item
  -- 展开每个category下的products子数组
  CROSS JOIN jsonb_array_elements(cat_item->'products') AS prod_item
  -- 提取product_name文本并执行ILIKE模糊匹配
  WHERE prod_item->>'product_name' ILIKE '%product_one%'
);

提取匹配的具体产品信息 #

如果需要返回匹配的产品详情（比如对应的分类、价格），可以直接展开数组并过滤：

SELECT 
  t.id, -- 替换为你的表主键或标识列
  cat_item->>'category_name' AS category_name, -- 若外层对象有分类名称字段可添加
  prod_item->>'product_name' AS matched_product,
  prod_item->>'price' AS product_price
FROM your_table t
CROSS JOIN jsonb_array_elements(t.category_products) AS cat_item
CROSS JOIN jsonb_array_elements(cat_item->'products') AS prod_item
WHERE prod_item->>'product_name' ILIKE '%product_two%';

大表优化：添加索引 #

如果表数据量较大，为提升查询性能，可创建针对嵌套路径的GIN索引：

-- 针对products数组中product_name的路径查询索引
CREATE INDEX idx_category_products_product_names 
ON your_table USING GIN (
  jsonb_path_query_array(category_products, '$.products[*].product_name') jsonb_path_ops
);

该索引可帮助数据库快速定位包含目标product_name的行，减少后续数组展开的计算量。

内容的提问来源于stack exchange，提问作者Bennison J