如何在PostgreSQL中对非嵌套JSONB列进行模糊匹配查询

问题根源 #

你原查询直接对整个JSONB字段用LIKE，会把数组转成完整字符串后匹配，不仅效率低下，还容易出现误匹配（比如数组内其他字符串或字段内容包含目标子串），无法精准针对数组元素做模糊匹配。

正确实现方式 #

要对JSONB数组内的每个元素单独做模糊匹配，需借助PostgreSQL的jsonb_array_elements函数展开数组，结合EXISTS子查询过滤：

SQL查询示例 #

SELECT * 
FROM blog b
WHERE EXISTS (
  SELECT 1 
  FROM jsonb_array_elements(b.tags) AS tag
  WHERE tag::text ILIKE '%horr%'
);

• jsonb_array_elements(b.tags)：将tags数组拆分为单独行（每个元素占一行）
• tag::text：把JSONB类型的元素转为文本格式
• ILIKE：支持不区分大小写的模糊匹配，若需严格区分大小写改用LIKE

安全的JavaScript写法（规避SQL注入） #

禁止直接拼接字符串，使用参数化查询：

const tags = "horr";
// 用参数占位符$1避免注入风险
const query = `
  SELECT * 
  FROM blog b
  WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(b.tags) AS tag
    WHERE tag::text ILIKE $1
  )
`;
// 执行时传入参数（以pg库为例）：
// client.query(query, [`%${tags}%`])

这种写法既实现了数组元素的精准模糊匹配，又能有效防止SQL注入，可准确筛选出tags数组包含带"horr"子串元素的记录。

性能优化补充 #

如果tags字段需频繁做此类模糊查询，可通过生成列+索引提升效率：

-- 创建生成列，将JSONB数组转为文本数组
ALTER TABLE blog ADD COLUMN tags_text_array text[] GENERATED ALWAYS AS (tags::text[]) STORED;
-- 给生成列建GIN索引
CREATE INDEX idx_blog_tags_text_array ON blog USING GIN (tags_text_array);

优化后的查询语句：

SELECT * FROM blog WHERE EXISTS (
  SELECT 1 FROM unnest(tags_text_array) AS tag WHERE tag ILIKE '%horr%'
);

内容的提问来源于stack exchange，提问作者Jackson Kasi