postgresql - 如何在Postgresql中的复杂嵌套JSONB上实现全文搜索

我在一个jsonb列中存储了非常复杂的JSONB。

数据库表如下所示:

 CREATE TABLE sites (
   id text NOT NULL,
   doc jsonb,
   PRIMARY KEY (id)
 )

我们存储在doc列中的数据是一个复杂的嵌套JSONB数据:

   {
      "_id": "123",
      "type": "Site",
      "identification": "Custom ID",
      "title": "SITE 1",
      "address": "UK, London, Mr Tom's street, 2",
      "buildings": [
          {
               "uuid": "12312",
               "identification": "Custom ID",
               "name": "BUILDING 1",
               "deposits": [
                   {
                      "uuid": "12312",
                      "identification": "Custom ID",
                      "audits": [
                          {
                             "uuid": "12312",
                              "sample_id": "SAMPLE ID"
                          }
                       ]
                   }
               ]
          }
       ]
    }

所以我的JSONB的结构如下:

SITE
  -> ARRAY OF BUILDINGS
     -> ARRAY OF DEPOSITS
       -> ARRAY OF AUDITS

我们需要通过每种输入类型中的某些值来实现全文搜索:

SITE (identification, title, address)
BUILDING (identification, name)
DEPOSIT (identification)
AUDIT (sample_id)

SQL查询应仅在这些字段值中运行全文本搜索。

我猜需要使用GIN索引和类似tsvector的东西，但没有足够的Postgresql背景。

那么，我的问题是有可能索引然后查询此类嵌套的JSONB结构吗？

最佳答案

让我们添加tsvector类型的新列:

alter table sites add column tsvector tsvector;

现在，让我们创建一个触发器，该触发器将收集词汇，组织它们并将其放入我们的tsvector中。我们将使用4个组(A，B，C，D)-这是一种tsvector的特殊功能，允许您稍后在搜索时区分词汇(请参见手册https://www.postgresql.org/docs/current/static/textsearch-controls.html中的示例；不幸的是，此功能最多只能支持4个组)开发人员为此只保留了2位，但是我们很幸运，我们只需要4个组):

create or replace function t_sites_tsvector() returns trigger as $$
declare
  dic regconfig;
  part_a text;
  part_b text;
  part_c text;
  part_d text;
begin
  dic := 'simple'; -- change if you need more advanced word processing (stemming, etc)

  part_a := coalesce(new.doc->>'identification', '') || ' ' || coalesce(new.doc->>'title', '') || ' ' || coalesce(new.doc->>'address', '');

  select into part_b string_agg(coalesce(a, ''), ' ') || ' ' || string_agg(coalesce(b, ''), ' ')
  from (
    select
      jsonb_array_elements((new.doc->'buildings'))->>'identification',
      jsonb_array_elements((new.doc->'buildings'))->>'name'
  ) _(a, b);

  select into part_c string_agg(coalesce(c, ''), ' ')
  from (
    select jsonb_array_elements(b)->>'identification' from (
      select jsonb_array_elements((new.doc->'buildings'))->'deposits'
    ) _(b)
  ) __(c);

  select into part_d string_agg(coalesce(d, ''), ' ')
  from (
    select jsonb_array_elements(c)->>'sample_id'
    from (
      select jsonb_array_elements(b)->'audits' from (
        select jsonb_array_elements((new.doc->'buildings'))->'deposits'
      ) _(b)
    ) __(c)
  ) ___(d);

  new.tsvector := setweight(to_tsvector(dic, part_a), 'A')
    || setweight(to_tsvector(dic, part_b), 'B')
    || setweight(to_tsvector(dic, part_c), 'C')
    || setweight(to_tsvector(dic, part_d), 'D')
  ;
  return new;
end;
$$ language plpgsql immutable;

create trigger t_sites_tsvector
  before insert or update on sites for each row execute procedure t_sites_tsvector();

^^-滚动它，此代码片段比看起来大(特别是您的MacOS没有滚动条...)

现在，让我们创建GIN索引以加快搜索查询的速度(如果您有很多行(例如，多于几百或几千)，这是有意义的):

create index i_sites_fulltext on sites using gin(tsvector);

现在我们插入一些要检查的内容:

insert into sites select 1, '{
      "_id": "123",
      "type": "Site",
      "identification": "Custom ID",
      "title": "SITE 1",
      "address": "UK, London, Mr Tom''s street, 2",
      "buildings": [
          {
               "uuid": "12312",
               "identification": "Custom ID",
               "name": "BUILDING 1",
               "deposits": [
                   {
                      "uuid": "12312",
                      "identification": "Custom ID",
                      "audits": [
                          {
                             "uuid": "12312",
                              "sample_id": "SAMPLE ID"
                          }
                       ]
                   }
               ]
          }
       ]
    }'::jsonb;

检查select * from sites; –您必须看到tsvector列中填充了一些数据。

现在让我们查询一下:

select * from sites where tsvector @@ to_tsquery('simple', 'sample');

-必须返回我们的记录。在这种情况下，我们搜索'sample'字，而我们不在乎将在哪个组中找到它。

让我们对其进行更改，并尝试仅在A组(如您所述的“SITE(标识，标题，地址)”)中进行搜索:

select * from sites where tsvector @@ to_tsquery('simple', 'sample:A');

-不能返回任何内容，因为'sample'字仅位于组D中(“AUDIT(sample_id)”)。确实:

select * from sites where tsvector @@ to_tsquery('simple', 'sample:D');

-会再次将我们的记录返回给我们。

请注意，您需要使用to_tsquery(..)而不是plainto_tsquery(..)才能处理4个组。因此，您需要自己清理输入(避免使用或删除特殊字符，例如&和|，因为它们在tsquery值中具有特殊含义)。

好消息是您可以在一个查询中组合不同的组，如下所示:

select * from sites where tsvector @@ to_tsquery('simple', 'sample:D & london:A');

另一种方法(例如，如果您必须使用四个以上的组)是具有多个tsvector，每个tsvector都位于单独的列中，使用单个查询构建它们，创建索引(您可以在多个tsvector列上创建单个索引)和查询寻址单独的列。它与我上面解释的相似，但效率可能较低。

希望这可以帮助。