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

Добавим новый столбец типа 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' и нам все равно, в какой группе оно будет найдено.

Давайте изменим его и попробуем искать только в группе А ("САЙТ (идентификация, название, адрес)", как вы его описали):

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 группами) - это иметь несколько tsvectors, каждый из которых находится в отдельном столбце, создавать их с помощью одного запроса, создавать индекс (вы можете создать один индекс для нескольких столбцов tsvector) и запрос, адресующий отдельные столбцы. Это похоже на то, что я объяснил выше, но, возможно, менее эффективно.

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

В 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;

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