如何查询PostgreSQL jsonb列中值匹配模式的JSON数组对象？

好问题！针对你这种要在JSONB数组的value字段上做模糊匹配（ILIKE '%ba%'）的需求，我整理了几种实用的实现方法，适合不同场景：

方法1：展开数组后关联查询 #

通过jsonb_array_elements函数把JSONB数组拆分成单独的元素行，过滤出匹配条件的元素后，再关联回原表获取完整行。记得加DISTINCT避免同一行被多次返回（如果数组里有多个匹配元素的话）：

SELECT DISTINCT tbl.*
FROM tbl
JOIN jsonb_array_elements(tbl.jsoncol) AS elem
ON elem->>'value' ILIKE '%ba%';

方法2：用EXISTS子查询（推荐） #

这种方式更高效，因为EXISTS子查询只要找到一个匹配的元素就会终止检查，而且天然不会产生重复行，不需要额外去重：

SELECT *
FROM tbl
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(tbl.jsoncol) AS elem
  WHERE elem->>'value' ILIKE '%ba%'
);

方法3：JSON路径表达式（PostgreSQL 12+） #

如果你用的是PostgreSQL 12或更高版本，可以用更简洁的JSON路径语法来实现，可读性更强：

SELECT *
FROM tbl
WHERE jsonb_path_exists(jsoncol, '$[*] ? (@.value like_regex "ba" flag "i")');

这里的like_regex "ba" flag "i"等价于ILIKE '%ba%'，flag "i"表示不区分大小写匹配。

额外提示 #

如果你的表数据量很大，这类查询可能会触发全表扫描。如果需要频繁做这类模糊匹配，可以考虑：

• 给JSONB列创建GIN索引（适合JSONB的通用查询优化）；
• 把数组中value字段的内容提取出来，单独存一个文本数组字段，并给这个字段创建GIN或GIST索引，能大幅提升模糊匹配的性能。

内容的提问来源于stack exchange，提问作者jay.lee