jsonb阵列上的三图匹配

Question

你好，我试图在我的表上使用pg_trgm实现某种模糊字符串匹配，其中包含一个jsonb数组，下面是一个示例数据：

      name
------------------
[{"firstname": "jhon", "lastname": "doe"},{"firstname": "jhonny", "lastname": "doe"}]
------------------    
[{"firstname": "jane", "lastname": "doe"},{"firstname": "janne", "lastname": "doe"}]

这是我当前查询的一个示例：

SELECT * FROM persons WHERE exists(
    SELECT * FROM json_array_elements(persons.name::json)
        WHERE word_similarity('jhon', (COALESCE(value->>'firstname', '') || ' ' || (COALESCE(value->>'lastname', ''))) ) >= 0.7);

它做了这项工作，但速度慢(没有索引)。我的问题是，我能做什么来加快搜索(索引，替代查询)？提亚

Answer 1

您不能索引返回的表达式，所以最好用每行一个昵称来规范数据，而应该全部填充到数组中。然后可以对“昵称”表进行索引，并将其连接到"persons“表。

如果不想这样做，可以将name字段转换为字符串，并对其进行索引。这可能导致不必要的结果，如果查询可以跨越分隔符而仍然满足截止(除非查询还包含标点符号)，所以查询不一样，但可能足够好。

create or replace function arr_val_agg(jsonb) returns text as $ 
  select string_agg(bar.value,', ') from jsonb_array_elements($1) 
    join lateral jsonb_each_text(value) bar on true;
$ language SQL immutable;

create index on persons using gin (arr_val_agg(name) gin_trgm_ops );

select * from persons where arr_val_agg(name) %> 'jhon';

随着arr_val_agg的长度越来越长，您可能从trigram索引中得到更多的假阳性，需要通过重新检查来过滤掉，因此性能可能会受到影响。