Как реализовать полнотекстовый поиск по сложному вложенному JSONB в Postgresql

Ответ 1

Давайте добавим новый столбец типа tsvector:

alter table sites add column 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' 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 ("САЙТ (идентификация, название, адрес)", как вы это описали):

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');

Другой путь (например, если вам нужно работать более чем с 4 группами) - это использовать несколько векторов-цвекторов, каждый из которых находится в отдельном столбце, построить их с помощью одного запроса, создать индекс (вы можете создать один индекс для нескольких tsvector столбцы) и запрос адресации отдельных столбцов. Это похоже на то, что я объяснил выше, но, возможно, менее эффективно.

Надеюсь это поможет.

Ответ 2

В Postgres 10 все выглядит немного проще, так как функция to_tsvector поддерживает json. Так, например, это работает хорошо:

UPDATE dataset SET search_vector = to_tsvector('english',
'{
  "abstract":"Abstract goes here",
  "useConstraints":"None",
  "dataQuality":"Good",
  "Keyword":"historic",
  "topicCategory":"Environment",
  "responsibleOrganisation":"HES"
}'::json)
where dataset_id = 4;

Примечание. Я не пробовал это для глубоко вложенной структуры, но не понимаю, почему это не сработает