Блог

Я пытаюсь выполнить поиск в таблице определенным LIKE %str% образом, но по полям внутри значений json по нескольким столбцам.

У меня есть таблица, в которой есть три столбца jsonb change , previous и specific_changes . Как вы можете себе представить, содержимое — это JSON, но структура этого json заранее неизвестна, поэтому я не могу использовать -> or ->> в запросе следующим образом:

select * from change_log where change -> 'field' = '"something"'

 create table change_log
(
    id               serial      not null
        constraint pk_change_log
            primary key,
    change           jsonb       not null,
    previous         jsonb,
    changed_at       timestamp with time zone default timezone('utc'::text, now()),
    specific_changes jsonb
);

INSERT INTO public.change_log (id, change, previous, changed_at, specific_changes) VALUES (1, '{"val": 2, "test": "test", "nested": {"nval": 1}}', 'null', '2020-11-12 16:53:28.827896', '{"val2": "Value2"}');
INSERT INTO public.change_log (id, change, previous, changed_at, specific_changes) VALUES (2, '{"val": "testNewChange", "test": "testChange", "nested": {"key": 1}}', '{"val": "2", "test": "testChange", "nested": {"nval": 1}}', '2020-11-15 12:18:35.021843', '{"new": "testValue"}');
INSERT INTO public.change_log (id, change, previous, changed_at, specific_changes) VALUES (3, '{"val": "newewChange", "test": "changeNew", "nested": {"val": 3}}', '{"val": "testNewChange", "test": "testValue", "nested": {"key": 1}}', '2020-11-15 12:19:40.832843', '{"new": "testChange", "nested": {"val": 1}}');
  

Мой вопрос:

1. Как будет выглядеть запрос, в котором заданная строка вернет все строки из таблицы change_log, любой из 3 упомянутых столбцов jsonb содержит любые поля, имеющие значение like %string% .

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

Примеры:

   INPUT           OUTPUT(ids)
  "2"              (1,2)
  "Change"         (2,3)
  "Chan"           (2,3)
  "Value"         (1,2,3)
  

EDIT1: я использую postgres версии 9.6

ПРАВКА2: исправлены вставленные изменения, отражающие желаемое поведение

Если вы используете Postgres 12 или более позднюю версию, вы можете использовать выражение пути SQL / JSON:

 select *
from change_log
where change @@ '$.** like_regex "change" flag "i"'
   or previous @@ '$.** like_regex "change" flag "i"'
   or specific_changes @@ '$.** like_regex "change" flag "i"'
  

Общий подход для старых версий PostgreSQL заключается в использовании exists некоторой функции, например

 select *
from table_name
where exists (
    select 1
    from jsonb_each_text(column_name) as t(k,v)
    where v ilike '%string%');
  

Для нескольких столбцов это можно сделать с помощью or :

 select *
from table_name
where
    exists (
        select 1
        from jsonb_each_text(column1) as t(k,v)
        where v ilike '%string%') or
    exists (
        select 1
        from jsonb_each_text(column2) as t(k,v)
        where v ilike '%string%');
  

или union :

 select *
from table_name
where
    exists (
        select 1
        from (
            select * from jsonb_each_text(column1) union all
            select * from jsonb_each_text(column2)) as t(k,v)
        where t.v ilike '%string%');
  

ДЕМОНСТРАЦИЯ

Обратите внимание, что вложенные объекты не будут обрабатываться должным образом, поскольку они будут проверяться как весь текст, включая ключи.

Чтобы исправить это, вам необходимо создать хранимую функцию, которая рекурсивно возвращает все значения из JSON.

Но это тема для другого вопроса 🙂

Вы можете запросить, например

 SELECT DISTINCT l.id
  FROM change_log l
 CROSS JOIN JSONB_EACH_TEXT( l.change ) AS c(e)
 CROSS JOIN JSONB_EACH_TEXT(  nullif(l.previous, 'null') ) AS p(e) 
 CROSS JOIN JSONB_EACH_TEXT( l.specific_changes ) AS s(e)
 WHERE c.value ~* 'change' OR s.value ~* 'change' OR p.value ~* 'change'
  

где ~* оператор выполняет поиск соответствия заданному ключевому слову без учета регистра, а функция JSONB_EACH_TEXT() расширяет самый внешний объект JSON в набор пар ключ / значение.

PS Необходимо исправить значение 'null' путем преобразования null в 1 значение идентификатора previous столбца или использовать nullif(l.previous, 'null') в качестве аргумента для второго JSONB_EACH_TEXT() в запросе

Demo