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