PostgreSQL JSONB列数组对象的多条件模糊查询实现

针对你需要查询Name包含"Jane"或Tags数组中对象的Value包含"4345"的需求，可通过以下SQL实现：

SELECT *
FROM documents
WHERE 
  -- 匹配Name字段包含"Jane"的记录
  data->>'Name' LIKE '%Jane%'
  OR
  -- 匹配Tags数组中存在Value包含"4345"的记录
  EXISTS (
    SELECT 1
    FROM jsonb_array_elements(data->'Tags') AS tag_item
    WHERE tag_item->>'Value' LIKE '%4345%'
  );

各部分说明： #

• Name字段模糊匹配：

  • data->>'Name'：从JSONB列data中提取Name字段的文本值（->>返回文本类型，而非JSONB类型，适合用LIKE做模糊匹配）。
  • LIKE '%Jane%'：%是通配符，代表任意长度的字符，此表达式会匹配所有包含"Jane"的字符串。如果需要不区分大小写，可改用ILIKE '%Jane%'。

• Tags数组的Value模糊匹配：

  • jsonb_array_elements(data->'Tags')：将Tags数组展开为独立行，每个数组元素对应一行，用tag_item作为别名。
  • tag_item->>'Value' LIKE '%4345%'：检查展开后的每个标签对象的Value字段是否包含"4345"。
  • EXISTS子查询：只要数组中存在至少一个符合条件的标签，就会返回true，避免同一行记录被重复返回。

替代方案（PostgreSQL 12+可用）： #

用jsonb_path_exists函数实现更简洁的数组匹配：

SELECT *
FROM documents
WHERE 
  data->>'Name' LIKE '%Jane%'
  OR
  jsonb_path_exists(data, '$.Tags[*].Value ? (@ like_regex "4345")');

这里$.Tags[*].Value遍历所有Tags的Value字段，like_regex "4345"表示匹配包含该字符串的内容；如需不区分大小写，可添加flag "i"：@ like_regex "4345" flag "i"。

原SQL仅支持精确匹配的原因： #

你之前使用的data->'Tags' @> '[{ "Value":"1004345"}]'是JSONB的包含运算符，它要求Tags数组中必须存在一个完全匹配{"Value":"1004345"}的对象，属于精确匹配逻辑，无法实现模糊查询。

内容的提问来源于stack exchange，提问作者Alexander Zeitler