postgresql 如何使用索引更新函数中的jsonb列进行全文搜索？

von4xj4u 于 2023-01-25 发布在 PostgreSQL

关注(0)|答案(2)|浏览(207)

我有一个包含以下（相关）字段的articles表

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

带有search_index上的索引

CREATE INDEX articles_search_index ON articles USING gin(search_index);

而且我希望能够搜索title和content，而没有任何特定的优先级。
我想把整个content数据当作一个文本块，而不实际改变它的结构。
我希望能奏效的是：

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内容？

postgresql

来源：https://stackoverflow.com/questions/75175416/how-to-use-jsonb-column-in-index-update-function-for-full-text-search

2条答案

按热度按时间

xpszyzbs1#

您可以使用jsonb_to_tsvector()生成的列。由于您还希望索引标题，因此需要将content列与包含标题的JSON值连接起来。
大致如下：

CREATE TABLE IF NOT EXISTS articles 
(
  ...
  title TEXT NOT NULL,
  content JSONB NOT NULL,
  search_index TSVECTOR generated always as 
     (jsonb_to_tsvector('english', content||jsonb_object(array['title', title]), '"all"')) stored
);

请注意，您不能使用jsonb_build_object()，因为不幸的是，它没有标记为“不可变”，但jsonb_object()标记为“不可变”。
如果存储在content列中的JSON可以包含顶级键title，则需要使用不同的键，可能是jsonb_object(array['__title', title])。
我可以看到的一个优点（除了不必管理触发器之外）是，这只索引实际的JSON * 值 * 而不是键，因此我希望这会产生更准确的搜索结果。