如何对JSONB字段的gist.concise数组执行to_tsvector全文检索？

你的问题出在直接将整个gist.concise数组转为字符串做检索，而没有提取数组中每个对象的text字段内容。以下是几种可行的解决方法：

方法一：用EXISTS子句匹配单个text字段 #

这种方式效率较高，只要数组中有任一对象的text匹配关键词就会返回结果：

SELECT audio.id
FROM audio
JOIN audio_json ON audio.id = audio_json.audio_id
WHERE
  to_tsvector(audio.name) @@ to_tsquery('ornithorinque')
  OR to_tsvector(audio.context::text) @@ to_tsquery('ornithorinque')
  OR EXISTS (
    SELECT 1
    FROM jsonb_array_elements(audio_json.analysis -> 'gist' -> 'concise') AS elem
    -- 处理gist.concise为null的情况，避免报错
    WHERE audio_json.analysis -> 'gist' -> 'concise' IS NOT NULL
      AND to_tsvector(elem ->> 'text') @@ to_tsquery('ornithorinque')
  );

方法二：聚合所有text字段后检索 #

如果需要匹配多个text字段的组合内容，可以先将数组中所有text拼接成单个文本再做检索：

SELECT audio.id
FROM audio
JOIN audio_json ON audio.id = audio_json.audio_id
WHERE
  to_tsvector(audio.name) @@ to_tsquery('ornithorinque')
  OR to_tsvector(audio.context::text) @@ to_tsquery('ornithorinque')
  OR to_tsvector(
    COALESCE(
      (SELECT string_agg(elem ->> 'text', ' ')
       FROM jsonb_array_elements(audio_json.analysis -> 'gist' -> 'concise') AS elem),
      ''
    )
  ) @@ to_tsquery('ornithorinque');

使用COALESCE避免当gist.concise为空时返回null导致检索失败。

方法三：用JSON路径表达式简化提取 #

PostgreSQL 12+支持JSON路径，可以直接提取所有text字段组成的数组，再转为文本检索：

SELECT audio.id
FROM audio
JOIN audio_json ON audio.id = audio_json.audio_id
WHERE
  to_tsvector(audio.name) @@ to_tsquery('ornithorinque')
  OR to_tsvector(audio.context::text) @@ to_tsquery('ornithorinque')
  OR to_tsvector(
    COALESCE(jsonb_path_query_array(audio_json.analysis, '$.gist.concise[*].text')::text, '')
  ) @@ to_tsquery('ornithorinque');

路径表达式$.gist.concise[*].text会遍历数组中所有对象，提取它们的text值。

性能优化：创建GIN索引 #

如果数据量较大，建议创建表达式索引加速检索。例如针对方法二的聚合文本创建索引：

CREATE INDEX idx_audio_json_gist_concise_text ON audio_json
USING GIN (
  to_tsvector(
    'english', -- 根据你的文本语言调整
    COALESCE(
      (SELECT string_agg(elem ->> 'text', ' ')
       FROM jsonb_array_elements(analysis -> 'gist' -> 'concise') AS elem),
      ''
    )
  )
);

内容的提问来源于stack exchange，提问作者Léo Bournizien