Запрос LIKE для элементов плоского массива jsonb

У меня есть таблица Postgres posts со столбцом типа jsonb, который в основном представляет собой плоский массив тегов.

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

Возможно ли такое в Postgres? Я постоянно нахожу сверхсложные примеры, и никто никогда не описывает такой базовый и простой сценарий.

Мой текущий код отлично работает для проверки, есть ли сообщения с определенными тегами:

select * from posts where tags @> '"TAG"'

и я ищу способ запустить что-нибудь среди строк

select * from posts where tags @> '"%TAG%"'

postgresql arrays jsonb postgresql-9.6 pattern-matching
person mbajur    schedule 30.04.2018    source источник
comment
вы не задумывались о чем-то вроде select * from posts where wmi_codes::text LIKE '%TAG%'?   -  person Jim Jones    schedule 30.04.2018
comment
Я так и сделал, и это мой план Б, но мне кажется, что я отбрасываю все плюсы использования столбца jsonb с этим решением вместо того, чтобы хранить его как текстовый столбец ... я?   -  person mbajur    schedule 30.04.2018
comment
Полностью согласен с вами, это совсем не элегантно. Мне кажется странным использовать такой подход, но если он делает то, что вы хотите, и это не влияет на производительность, я все равно рассмотрю его. Удачи :-)   -  person Jim Jones    schedule 30.04.2018
comment
Если вы хотите эффективно обрабатывать данные в PostgreSQL, не храните их в формате JSON. Если это список тегов, сохраните их в таблице. Тогда все станет просто.   -  person Laurenz Albe    schedule 30.04.2018
comment
Мне как бы нужно использовать jsonb, это не было моим решением и не моим кодом, и мне нужно адаптироваться :)   -  person mbajur    schedule 30.04.2018

Ответы (1)


arrow_upward
6
arrow_downward 

SELECT *
FROM   posts p
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements_text(p.tags) tag
   WHERE  tag LIKE '%TAG%'
   );

Связанные с объяснением:

Или проще с помощью _2 _ оператор, так как Postgres 12 реализовал SQL / JSON:

SELECT *
--     optional to show the matching item:
--   , jsonb_path_query_first(tags, '$[*] ? (@ like_regex "^ tag" flag "i")')
FROM   posts
WHERE  tags @? '$[*] ? (@ like_regex "TAG")';

Оператор @? - это просто оболочка для функции jsonb_path_exists(). Это эквивалентно:

...
WHERE  jsonb_path_exists(tags, '$[*] ? (@ like_regex "TAG")');

Ни один из них не имеет поддержки индекса. (Может быть добавлено для оператора @? позже, но пока отсутствует на стр. 13). Таким образом, эти запросы выполняются медленно для больших таблиц. Нормализованный дизайн, как уже предлагала Лоренц, будет лучше - с триграммным индексом:

Только для соответствия префикса (LIKE 'TAG%', без подстановочного знака в начале) вы можете заставить его работать с полнотекстовым индексом:

CREATE INDEX posts_tags_fts_gin_idx ON posts USING GIN (to_tsvector('simple', tags));

И соответствующий запрос:

SELECT *
FROM   posts p
WHERE  to_tsvector('simple', tags)  @@ 'TAG:*'::tsquery

Или используйте словарь english вместо simple (или что-то еще, что подходит для вашего случая), если вы хотите использовать естественный английский язык.

to_tsvector(json(b)) требует Postgres 10 < / strong> или более поздней версии.

Связанный:

person Erwin Brandstetter    schedule 01.05.2018
comment
@ IljaEverilä: Да, Postgres 12 сделал это возможным. Я кое-что добавил выше. - person Erwin Brandstetter; 16.07.2020