PostgreSQL中仅返回JSONB数组匹配片段的查询方法

前提确认 #

确保TranscriptVector字段已正确聚合Transcript->'segments'数组中所有text字段的文本，生成可被GIN索引利用的tsvector。若尚未生成，可执行以下语句更新：

UPDATE vods
SET TranscriptVector = to_tsvector('english', string_agg(seg->>'text', ' '))
FROM (
    SELECT id, jsonb_array_elements(Transcript->'segments') AS seg
    FROM vods
) AS seg_data
WHERE vods.id = seg_data.id
GROUP BY vods.id;

也可创建触发器自动维护该字段，避免手动更新：

CREATE OR REPLACE FUNCTION update_transcript_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.TranscriptVector = to_tsvector('english', string_agg(seg->>'text', ' '))
    FROM (SELECT jsonb_array_elements(NEW.Transcript->'segments') AS seg) AS seg_data;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_transcript_vector
BEFORE INSERT OR UPDATE ON vods
FOR EACH ROW EXECUTE FUNCTION update_transcript_vector();

检索匹配的Segments片段 #

通过GIN索引快速过滤目标VOD记录，再提取仅匹配的segments项返回：

SELECT
    v.id,
    jsonb_agg(s.seg) AS matching_segments
FROM vods v
CROSS JOIN LATERAL jsonb_array_elements(v.Transcript->'segments') AS s(seg)
WHERE
    v.TranscriptVector @@ to_tsquery('english', '你的搜索关键词') -- 利用GIN索引快速筛选VOD
    AND to_tsvector('english', s.seg->>'text') @@ to_tsquery('english', '你的搜索关键词') -- 过滤单个匹配的segment
GROUP BY v.id;

关键说明 #

1. 先通过TranscriptVector的GIN索引快速缩小范围，避免全表扫描；
2. 展开目标VOD的segments数组，逐一校验单个segment的文本是否匹配；
3. 最终将匹配的segments重新聚合为数组返回，仅保留有效结果。

精准匹配优化 #

若需短语精准匹配，可调整to_tsquery的参数，比如搜索连续短语：

to_tsquery('english', '机器学习:<->')

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