postgres 通过zhparser实现全文搜索功能

╰半夏微凉° 2022-11-18 04:36 332阅读 0赞

# 前言 #

我们在工作中遇到一个字段存储jsonb 类型，并需要对这个字段进行全文索引。由于对postgres 一些字段类型不清楚，走了一些弯路这里讲解。

# 实践 #

## 一、postgres 和 zhparser 的安装 ##

前面两步postgres 的安装网上挺多资料，可以省略。  
zhparser 的安装网上资料比较杂乱，后续做一下补充。

## 二、全文索引的实践 ##

**2、1 jsonb 类型**

（1）表结构的初始化，插入初始化数据

create table test2(
      id int,
      doc jsonb
    );
    
    --针对jsonb 插入部分值
    insert into test2 values(1,'{"nickname": "gs", "tags": ["python", "golang", "db"]}')
    insert into test2 values(2,'{"num":2,"nickname": "gs", "tags": ["python", "golang", "db"]}')

（2）查询部分doc 中的查询修改

**查询jsonb中部分值**

--扩展字段提取相应属性的值（test2 表中doc 字段 中的 nickname 属性）
      select  id,doc :: json->>'nickname' from test2

![在这里插入图片描述][20210412141722317.png]

**修改**

--扩展字段提取相应属性的值 修改 doc 字段中 nickname 为 nickValue1"'
    	update test2 set doc = jsonb_set(doc,'{nickname}','"nickValue1"'::jsonb, false) where id = '2';
    
    		--添加整个字段的值
    	update test2 set doc = jsonb_set(doc, '{platform}','"baidu"') where id=1
    	
    	--修改整个字段的值
    	update test2 set doc ='{"num":2,"nickname":"nick"}' where id=1

**针对jsonb 中的部分字段进行筛选**

--筛选出doc 中num=2 的值
    select * from test2 where  doc :: json->>'num'='2'

**2、2 针对jsonb 实现全文索引（依赖于 zhparser插件，需要postgres 10 以上，该测试基于PostgreSQL 10.16 on x86\_64-pc-linux-gnu）**

--创建extension
    create extension zhparser;
    CREATE TEXT SEARCH CONFIGURATION zh (PARSER = zhparser);
    ALTER TEXT SEARCH CONFIGURATION zh ADD MAPPING FOR n,v,a,i,e,l,j WITH simple;
    select to_tsvector('zh','十九大精神');

![在这里插入图片描述][20210412143703450.png]

1、创建name\_age 设置初始化数据

--创建name_age表
    create table name_age(
     name jsonb,
     tsvector tsvector
    )
    
    
    insert into name_age values ('{"nickname": "hello", "tags": ["java", "golang", "db"]}','Sleep deprivation curing depression')
    
    insert into name_age values ('{"nickname": "中国台湾是中国的一部分", "tags": ["java", "golang", "db"]}','Sleep deprivation curing depression')

2、插入创建jsonb的索引

create index idx_name_index on name_age using gin(to_tsvector('zh', name));

3、针对jsonb 进行字段进行全文筛选

to\_tsvector(‘zh’,name) 为全文索引的分词，下面一句是有golang进行查询

SELECT name,to_tsvector('zh',name)
        FROM name_age where to_tsvector('zh',name) @@ to_tsquery('zh','golang')

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70]

**2、3 关于jsonb 全文索引的优化**

（1）我们上文通过这种方式来处理来处理查询，每次搜索都需要对name 字段进行分词、然后再进行索引，那么有办法省略这一步吗？

SELECT name,to_tsvector('zh',name)
        FROM name_age where to_tsvector('zh',name) @@ to_tsquery('zh','golang')

我们创建一个新的字段

ALTER TABLE name_age ADD COLUMN tsv_column tsvector;           // 添加一个分词字段
    UPDATE name_age SET tsv_column = to_tsvector('zh', coalesce(name,'{}'));   // 将字段的分词向量更新到新字段中(这里是jsonb 类型故使用‘{}’，文本可以直接使用‘’)
    CREATE INDEX idx_gin_zhcn ON name_age USING GIN(tsv_column);   // 在新字段上创建索引

我们到这一步，基本达到我们要求了，但每次更新、新增 name\_age 都需要单独更新变量tsv\_column 这个字段，我们如果需要减少这一步。

CREATE TRIGGER trigger_name BEFORE INSERT OR UPDATE  ON name_age FOR EACH ROW EXECUTE PROCEDURE
    tsvector_update_trigger(tsv_column, 'public.zh', name); // 创建一个更新分词触发器
    
    insert into name_age (name) values('{"tags": ["java", "golang", "db"], "nickname": "aa"}');

然后报错

ERROR: column “name” is not of a character type

tsvector\_update\_trigger 这个是postgres 自定义的函数并不支持 jsonb 类型的操作，这个时候我们是不是可以自己写一个一个函数代替postgres 自定义函数来进行操作呢？

DROP TRIGGER trigger_name ON name_age ;  // 删除name_age 之前创建trigger_name  触发器
    
    // 创建一个 jsonb_insert_update_trigger 的函数（由于自身对function 了解比较少，写得很定制化，如果有大佬指导可以写一个 像tsvector_update_trigger 这样通用的函数）
    create or replace function jsonb_insert_update_trigger()
    returns trigger
    language plpgsql as
    $BODY$
      BEGIN
        NEW.tsv_column := to_tsvector ('public.zh',
          NEW.name);
        return NEW;
      END;
    $BODY$;
    
    CREATE TRIGGER trigger_name BEFORE INSERT OR UPDATE  ON name_age FOR EACH ROW EXECUTE PROCEDURE
    jsonb_insert_update_trigger(); // 直接采用自己写的函数来创建
    
    
    insert into name_age (name) values('{"tags": ["java", "golang", "db"], "nickname": "aa"}');   //插入数据

插入数据之后，tsv\_column 自动创建了tsvector 类型的值  
![在这里插入图片描述][2021041417173722.png]  
对部分字段进行更新查看效果

update  name_age set name='{"tags": ["java", "golang", "db"], "nickname": "中国台湾是中国的一部分"}' where name :: json->>'nickname'='aa'

发现tsv\_column 字段也自动更新了  
![在这里插入图片描述][20210414172314259.png]

[20210412141722317.png]: /images/20221022/63ab978b35f44ba7bb33fe4913877433.png
[20210412143703450.png]: /images/20221022/3ff1d41930ea450d9dfc1b08efc03485.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70]: /images/20221022/c35d42a0453d48e784df9f3b7951597a.png
[2021041417173722.png]: /images/20221022/df81ea4ce092451db7466dbd3616d09e.png
[20210414172314259.png]: /images/20221022/4d9f685f1e1245c396fc8d62e7fb6a3e.png