主页 / dba / 问题 / 247194
Accepted
seng
Asked: 2019-09-07 08:04:17 +0800 CST

jsonb数组上的三元组匹配

  • 772

您好我正在尝试在包含数组的表上使用 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);

它可以完成这项工作，但速度很慢（没有索引）。我的问题是我可以做些什么来加快搜索（索引，替代查询）？TIA

postgresql string-searching

1 个回答

  1. Best Answer

    您无法索引返回行的表达式，因此最好使用每行一个昵称来规范化您的数据，而不是全部填充到数组中。然后您可以索引“昵称”表并将其加入“人员”表。

    如果您不想这样做，您可以将 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 索引中获得更多误报，这些误报需要通过重新检查过滤掉，因此性能可能会受到影响。

    • 1

相关问题