如何在全文检索的索引更新函数中使用jsonb列？

问题场景与报错 #

现有articles表核心字段定义：

CREATE TABLE
  IF NOT EXISTS articles (
    ...
    title TEXT NOT NULL,
    content JSONB NOT NULL,
    search_index TSVECTOR NOT NULL,
    ...
  );

已为search_index字段创建GIN索引：

CREATE INDEX articles_search_index ON articles USING gin(search_index);

需求为无优先级区分检索title和content字段，将content的JSONB数据当作纯文本blob处理（不修改原数据结构）。尝试使用默认触发器函数时执行以下语句：

CREATE TRIGGER articles_search_index_update BEFORE INSERT
OR
UPDATE
  ON articles FOR EACH ROW EXECUTE FUNCTION 
     tsvector_update_trigger
        (search_index,
         'pg_catalog.english', 
          title, 
          content
        );

触发错误：column "content" is not of a character type，原因是tsvector_update_trigger仅支持字符类型列作为输入参数，无法直接传入JSONB类型列。

解决方案 #

无法直接在tsvector_update_trigger中使用JSONB列，需通过自定义触发器函数将JSONB转换为文本类型后再生成TSVECTOR：

1. 创建自定义触发器函数 #

CREATE OR REPLACE FUNCTION update_articles_search_index()
RETURNS TRIGGER AS $$
BEGIN
  -- 将JSONB转为纯文本，与title拼接后生成无优先级区分的TSVECTOR
  NEW.search_index := to_tsvector('pg_catalog.english', NEW.title || ' ' || NEW.content::TEXT);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

通过NEW.content::TEXT将JSONB对象强制转换为纯文本，与title拼接后生成TSVECTOR，确保两者在检索时无优先级差异。

2. 创建触发器绑定自定义函数 #

CREATE TRIGGER articles_search_index_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_articles_search_index();

可选优化：提取JSONB特定字段 #

若后续需仅检索JSONB中的指定字段而非全部内容，可使用jsonb_extract_path_text函数精准提取，例如仅提取content中的body字段：

NEW.search_index := to_tsvector('pg_catalog.english', NEW.title || ' ' || jsonb_extract_path_text(NEW.content, 'body'));

检索验证 #

使用plainto_tsquery执行全文检索：

SELECT * FROM articles
WHERE search_index @@ plainto_tsquery('pg_catalog.english', '目标关键词');

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