Поиск в массиве JSON объекта, содержащего значение, соответствующее шаблону

Question

У меня есть БД с jsonb столбец, где каждая строка содержит массив пар имя-значение. Пример для одного jsonb значение:

[
    {"name":"foo", "value":"bar"},
    {"name":"biz", "value":"baz"},
    {"name":"beep", "value":"boop"}
]

Как бы я запросить строки, которые содержат частичное значение? Т.е. найти строки с ключом объекта JSON value или "%ba%"?

Я знаю, что я могу использовать SELECT * FROM tbl WHERE jsoncol @> '[{"value":"bar"}]' найти строки, в которых JSON - это конкретное значение, но как мне запросить строки, содержащие шаблон?

Answer 1

Там нет встроенного jsonb операторы или любые индексы, поддерживающие этот вид фильтра напрямую (пока).

Я предлагаю EXISTS Полусоединение:

SELECT t.*
FROM   tbl t
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(t.jsoncol) elem
   WHERE  elem->>'value' LIKE '%ba%'
   );

Это позволяет избежать лишних оценок и окончательного DISTINCT шаг вам нужно будет получить четкие строки с равниной CROSS JOIN,

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

Связанные с:

• Поиск вложенных значений в массиве jsonb с большим оператором
• Найти строки, содержащие ключ, в массиве записей JSONB
• Создание индекса Postgres JSONB для подобъекта массива

В сторону, если ваш jsonb значения действительно похожи на пример, вы можете обрезать много шума и просто сохранить:

[
   {"foo":"bar"},
   {"biz":"baz"},
   {"beep":"boop"}
]

Answer 2

Вы можете использовать функцию jsonb_array_elements() в боковом соединении и использовать его результат value в WHERE пункт:

select distinct t.* 
from my_table t
cross join jsonb_array_elements(jsoncol)
where value->>'value' like '%ba%'

Пожалуйста, прочитайте Как запросить массивы jsonb с оператором IN для заметок о distinct и производительность.