Блог

Главная - Вопросы по программированию - PostgreSQL jsonb_path_query удаляет результат вместо возврата нулевого значения

PostgreSQL jsonb_path_query удаляет результат вместо возврата нулевого значения

#postgresql #jsonb

Вопрос:

В примере таблицы:

 create table example
(
    id serial not null
        constraint example_pk
            primary key,
    data json not null
);

и данные

 INSERT INTO public.example (id, data) VALUES (1, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]');
INSERT INTO public.example (id, data) VALUES (2, '[{"key": "1", "value": "val1"}]');
INSERT INTO public.example (id, data) VALUES (3, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]');
ID данные
1 [{«ключ»: «1», «значение»: «val1»}, {«ключ»: «2», «значение»: «val2»}]
2 [{«ключ»: «1», «значение»: «val1»}]
3 [{«ключ»: «1», «значение»: «val1»}, {«ключ»: «2», «значение»: «val2»}]

Я хочу запросить поле значения в столбце данных, где ключ = 2

Запрос, который я сейчас использую, таков:

     SELECT id,
           jsonb_path_query(
                   TO_JSONB(data),
                   '$[*] ? (@.key == "2").value'::JSONPATH
               )::VARCHAR AS values
    FROM example

Я бы ожидал, что результаты будут:

ID ценности
1 «val2»
2 нулевой
3 «val2»

Но фактический результат таков:

ID ценности
1 «val2»
3 «val2»

Есть ли причина, по которой нулевой вывод jsonb_path_query опущен? Как мне заставить его вести себя так, как я ожидаю?

Комментарии:

1. Если вы хотите использовать путь JSON, то почему ваш столбец не определен как jsonb ? Тогда вы также можете избавиться от кастинга. jsonb в любом случае, это рекомендуемый тип данных для значений JSON.

Ответ №1:

Вы хотите jsonb_path_query_first() , если вам нужен результат выражения пути:

 SELECT id,
       jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') AS values
FROM example

Обратите внимание, что это возвращает jsonb значение. Если вам нужна text ценность, используйте:

 jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') #>> '{}

Ответ №2:

В соответствии с документацией PostgreSQL фильтр действует как WHERE условие

При определении пути вы также можете использовать одно или несколько выражений фильтра, которые работают аналогично предложению WHERE в SQL. Выражение фильтра начинается со знака вопроса и содержит условие в круглых скобках:

Мне удалось достичь того, что вы ищете, используя LATERAL и LEFT JOIN 

 SELECT id,
         *
FROM example left join 
LATERAL jsonb_path_query(
        TO_JSONB(data),
        '$[*] ? (@.key == "2").value'::JSONPATH) 
    on true;

Результат

  id | id |                              data                              | jsonb_path_query 
---- ---- ---------------------------------------------------------------- ------------------
  1 |  1 | [{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}] | "val2"
  2 |  2 | [{"key": "1", "value": "val1"}]                                | 
  3 |  3 | [{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}] | "val2"
(3 rows)
Метки: