Полнотекстовый поиск PostgreSQL внутри JSON, индекс внутри JSON?

Question

[Luhashan]

Есть таблица в postgres – table.
В table есть поле data (jsonb).
В data лежат json-объекты типа:
{
"obj": [
{
"TEXT": "any text here",
"key1": any value,
"key2": any value
},
{
" TEXT ": " any text here ",
"key1": any value,
"key2": any value
},
...
]
}
Пытаюсь сделать полнотекстовый поиск по " TEXT ".
Вопрос:
1. Можно ли не распаковывая json в отдельные таблицы/поля создать gin-индекс (tsvector) для " TEXT " и как?
2. Можно ли в принципе сделать полнотекстовый поиск по " TEXT ", который не будет слишком тормозить (база около 5 гигов) не распаковывая json в отдельные таблицы/поля?
Заранее всем огромное спасибо!

Comments

[rPman]

у меня только один вопрос ЗАЧЕМ?

зачем брать шуруповерт, затем раскурочить топор, долго обрабатывать напильником его до состояния молотка, затем вставлять молоток как сверло... и забивать так им гвозди?

[Luhashan]

Dr. Bacon, Да, для конкретного ключа ок. Но вот для данных внутри jsonb массива возможно ли? В принципе через jsonb_array_elements для поиска по шаблону или регуляркой я эти данные достаю, но можно ли их проиндексировать превратив перед этим в tsvector?

[Luhashan]

rPman, Согласен, просто интересно можно так сделать или нельзя от слова совсем.

[rPman]

с вероятностью 99% поддержка json в базе данных это то же самое как создание поля, индекса и хранимой процедуры на обработку обновлений этого json поля

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

[Dimonchik]

просто интересно можно так сделать или нельзя

приварить паровоз к рельсам (с)

[Vitsliputsli]

rPman,

я понимаю, существуют задачи (выдуманные) когда заказчик хочет чтобы база данных - это железобетонно

Можете пояснить? Как заказчик связан с технической реализацией?
А если вдруг он решает все вопросы по технической реализации, то тогда и проблемы с ней надо адресовать ему.

[rPman]

Vitsliputsli, когда используется принцип 'разделяй и властвуй', когда реализация базы и реализация интерфейса максимально разносятся (в т.ч. по людям) для обеспечения надежности например,.. бакэнд лезет к базе только по строго выверенному api к спец модулю который все контролирует (это как надо), отличный пример - финансовые сервисы, бакэнд не при каких условиях не должен лезть к базе напрямую к таблицам, как минимум запись должна быть ограничена

хранимые процедуры это просто механизм реализации этой прослойки, просто не самый удобный

[Vitsliputsli]

rPman, ну т.е. под закачиком понималось лицо ответственное за компонент с основной базой данной. Тогда да, либо договариваться с этим лицом, либо дублировать у себя базу в том виде, в каком она будет соответствовать требованиям, опять же если это допустимо политикой.

Answer 1

[galaxy]

Сделать-то можно. Но лучше все-таки хотя бы отдельное поле под tsvector завести.

create table t (col jsonb);
create index ix_t_col using gin(to_tsvector('pg_catalog.english', jsonb_path_query_array(col, '$.obj.TEXT')));

insert into t values('{
"obj": [
{
"TEXT": "These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions.",
"key1": 77,
"key2": "a"
},
{
"TEXT": "Returns target with new_value inserted. If the item designated by the path is an array element, new_value will be inserted before that item if insert_after is false (which is the default), or after it if insert_after is true. If the item designated by the path is an object field, new_value will be inserted only if the object does not already contain that key",
"key1": 99,
"key2": false
}
]
}');


select ts_headline(col, 'function'::tsquery),
       ts_headline(arr, 'function'::tsquery)
  from (
    select col, jsonb_path_query_array(col, '$.obj.TEXT') arr,
           to_tsvector('pg_catalog.english', jsonb_path_query_array(col, '$.obj.TEXT')) tsv 
      from t
  ) q
 where tsv @@ 'function'::tsquery;

jsonb_path_query_array(col, '$.obj.TEXT') - выделяет все, что у вас по ключу TEXT в массив. Дальше он индексируется

Comments

[Luhashan]

Спасибо за ответ, протестил ваш вариант на базе 2К строк, 74К значений "TEXT".
Без индекса простейший запрос 1,8 сек. С индексом 1,3 сек. Как-бы не особо.
Вот такой тупой вариант:

select * FROM 
  (select jsonb_array_elements(col->'obj') -> 'TEXT' as text  from t) as one
  where to_tsvector('pg_catalog.english', one.TEXT) @@ to_tsquery('function')

работает 1,1 сек. Как бы тоже не особо.

Еще попробовал регуляркой - 0.4 сек.

Собственно, вопрос был в том, как проиндексировать каждое поле 'TEXT' внутри json-масива, а не массив этих полей (честно говоря не очень понимаю какой смысл индексировать массив из строк, если поиск идет по строкам, но это ладно) и возможно ли это ?

Попробовал вот так, но ругается на второй аргумент в to_tsvector

CREATE INDEX ix_t_col ON t
USING gin (to_tsvector('pg_catalog.english',  jsonb_array_elements(col->'obj') -> 'TEXT'))

Интуитивно, posgresql должен создать индекс каждого поля 'TEXT' через ссылку на таблицу-колонку-jsonb-obj-массив-позицию в массиве. Это же возможно? Чем json отличается просто от поля в таблице? Только дополнительным path к искомому значению.
Но вот как сделать не понимаю пока, изучаю posgresql всего третий день... :(

[galaxy]

Luhashan,

протестил ваш вариант на базе 2К строк, 74К значений "TEXT".

это смешные объемы. Тут могло и без индекса быть быстрее.

Собственно, вопрос был в том, как проиндексировать каждое поле 'TEXT' внутри json-масива, а не массив этих полей (честно говоря не очень понимаю какой смысл индексировать массив из строк, если поиск идет по строкам, но это ладно) и возможно ли это ?

Не понимаю вопроса, если честно. Запись в индексе позволяет найти строку таблицы. Если у вас поля в этой строке содержат множество значений в запакованном виде (в json, например), индекс нужное значение не поможет распаковать. Смысл индексировать массив очевидный: вы хотите искать только по полям с ключом TEXT, если проиндексировать весь JSON, будет искать по лишним полям, так что сначала надо вытащить их (вы делаете то же самое в запросе с jsonb_array_elements).

Попробовал вот так, но ругается на второй аргумент в to_tsvector

потому что jsonb_array_elements возвращает несколько результатов - это функция-таблица, грубо говоря. Можно объединить эти результаты в массив или в большую строку, тогда сработает. Это по сути то же самое, что я предложил.

[Luhashan]

Если у вас поля в этой строке содержат множество значений в запакованном виде (в json, например), индекс нужное значение не поможет распаковать.

Но ведь по конкретному ключу в json ведь можно индекс построить и с ним будет немного быстрее запрос идти, значит он работает?

Смысл индексировать массив очевидный: вы хотите искать только по полям с ключом TEXT, если проиндексировать весь JSON, будет искать по лишним полям, так что сначала надо вытащить их (вы делаете то же самое в запросе с jsonb_array_elements).

Кстати сделал вектор и проиндексировал весь JSON. Тот же запрос выполняться стал немного быстрее 0,9 сек. Как-то странно это, казалось бы вашим методом локализуем максимально поиск, а работает в итоге медленнее, если тупо без локализации проиндексировать весь JSON и искать по нему :(

[galaxy]

Luhashan, ну в общем варианты у вас есть.

Если сильно важна скорость, лучше всего будет, наверное, сделать генерируемую колонку и ее уже индексировать, т.к. все эти операции и json и tsvector весьма дорогие.

Кстати, если вам не нужен поиск с учетом морфологии, т.е. если вам достаточно поиска по точному совпадению слова, можно значения из TEXT разбить на отдельные слова, объединить их все в массив, и его индексировать. Искать потом через WHERE word = ANY(words_arr).
Должно работать еще быстрее, т.к. обходимся без накладного FTS

[Luhashan]

galaxy, Да, огромное вам спасибо, написал вам сообщение на habr. Понял что не с того начал, чтобы было с чем сравнить сделал теперь все как и положено. Весь "TEXT" вынес в отдельную таблицу, создал там колонку с tsvector, проиндексировал ее и получилось прекрасно, 50мс на запрос.
Выводы которые я сделал: postgres плохо оптимизирует работу с json, как вы и написали, tsvector очень дорогая операция, документация у postgres не показалась мне идеальной, в юзкейсах tsvector нужно жирным писать, что без предварительного построения tsvector поиск будет более чем медленный, либо при первом обращении к этой функции под капотом строить скрытую колонку векторов и потом юзать ее как неявную часть таблицы. Еще раз большое спасибо!

Answer 2

[Luhashan]

Ну кому интересно, нашел вот такое https://www.depesz.com/2017/04/04/waiting-for-post...
На сколько я понял, проиндексировать можно только весь json как текст, индекса по ключам похоже сделать не получится:
"Nice. It found all words in all values in the json (it didn't index keys of objects)".
Хотя можно было бы и сделать, ведь postgres дает возможность добраться до ключей в запросе, значит какое-то json дерево один хрен строится, особенно учитывая, что очень высокий процент разработчиков часто не парится и херачит полубезумные json объекты в одно поле, даром что реляционная база юзается :( :)

Comments

[d'Ivan]

Читать про computed columns

[galaxy]

На сколько я понял, проиндексировать можно только весь json как текст, индекса по ключам похоже сделать не получится:

нет, там написано, что индексируется только текст в значениях, а не названия самих ключей

[Luhashan]

galaxy, Да, сорян, протупил.