Использование jsonb_set () для обновления определенного значения массива jsonb

В настоящее время я работаю с PostgreSQL 9.5 и пытаюсь обновить значение внутри массива поля jsonb. Но я не могу получить индекс выбранного значения

Моя таблица выглядит так:

 CREATE TABLE samples (
    id serial,
    sample jsonb
 );

Мой JSON выглядит так:

{"result": [
    {"8410": "ABNDAT", "8411": "Abnahmedatum"},
    {"8410": "ABNZIT", "8411": "Abnahmezeit"},
    {"8410": "FERR_R", "8411": "Ferritin"}
]}

Моя инструкция SELECT для получения правильного значения работает:

SELECT 
    id, value 
FROM 
    samples s, jsonb_array_elements(s.sample#>'{result}') r  
WHERE 
    s.id = 26 and r->>'8410' = 'FERR_R';

приводит к:

id | value
----------------------------------------------
26 | {"8410": "FERR_R", "8411": "Ferritin"}

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

UPDATE 
    samples s
SET
    sample = jsonb_set(sample,
              '{result,2,ratingtext}',
              '"Some individual text"'::jsonb,
              true)
WHERE
      s.id = 26;

После выполнения оператора UPDATE мои данные выглядят следующим образом (тоже правильно):

{"result": [
    {"8410": "ABNDAT", "8411": "Abnahmedatum"},
    {"8410": "ABNZIT", "8411": "Abnahmezeit"},
    {"8410": "FERR_R", "8411": "Ferritin", "ratingtext": "Some individual text"}
]}

Пока все хорошо, но я вручную поискал значение индекса 2, чтобы найти нужный элемент внутри массива JSON. Если порядок будет изменен, это не сработает.

Итак, моя проблема:

Есть ли способ получить индекс выбранного элемента массива JSON и объединить оператор SELECT и оператор UPDATE в один?

Как:

UPDATE 
    samples s
SET
    sample = jsonb_set(sample,
              '{result,' || INDEX OF ELEMENT || ',ratingtext}',
              '"Some individual text"'::jsonb,
              true)
WHERE
      s.id = 26;

Значения samples.id и 8410 известны до подготовки оператора.

Или на данный момент это невозможно?


postgresql arrays json sql-update jsonb
person Daniel Seichter    schedule 17.08.2016    source источник
comment
Аналогичный вопрос / ответ: dba.stackexchange.com/questions/193390/   -  person Christophe Roussy    schedule 31.07.2018

Ответы (1)


arrow_upward
25
arrow_downward

Вы можете найти индекс искомого элемента, используя jsonb_array_elements() with ordinality (обратите внимание, ordinality начинается с 1, а первый индекс массива json равен 0):

select 
    pos- 1 as elem_index
from 
    samples, 
    jsonb_array_elements(sample->'result') with ordinality arr(elem, pos)
where
    id = 26 and
    elem->>'8410' = 'FERR_R';

 elem_index 
------------
          2
(1 row)

Используйте приведенный выше запрос для обновления элемента на основе его индекса (обратите внимание, что второй аргумент jsonb_set() является текстовым массивом):

update 
    samples
set
    sample = 
        jsonb_set(
            sample,
            array['result', elem_index::text, 'ratingtext'],
            '"some individual text"'::jsonb,
            true)
from (
    select 
        pos- 1 as elem_index
    from 
        samples, 
        jsonb_array_elements(sample->'result') with ordinality arr(elem, pos)
    where
        id = 26 and
        elem->>'8410' = 'FERR_R'
    ) sub
where
    id = 26;

Результат:

select id, jsonb_pretty(sample)
from samples;

 id |                   jsonb_pretty                   
----+--------------------------------------------------
 26 | {                                               +
    |     "result": [                                 +
    |         {                                       +
    |             "8410": "ABNDAT",                   +
    |             "8411": "Abnahmedatum"              +
    |         },                                      +
    |         {                                       +
    |             "8410": "ABNZIT",                   +
    |             "8411": "Abnahmezeit"               +
    |         },                                      +
    |         {                                       +
    |             "8410": "FERR_R",                   +
    |             "8411": "Ferritin",                 +
    |             "ratingtext": "Some individual text"+
    |         }                                       +
    |     ]                                           +
    | }
(1 row)

Последний аргумент в jsonb_set() должен быть true, чтобы принудительно добавить новое значение, если его ключ еще не существует. Однако его можно пропустить, так как его значение по умолчанию - true.

Хотя проблемы с параллелизмом кажутся маловероятными (из-за ограничивающего условия WHERE и потенциально небольшого количества затронутых строк), вас также может заинтересовать Атомарное ОБНОВЛЕНИЕ .. ВЫБРАТЬ в Postgres.

person klin    schedule 17.08.2016
comment
Большое спасибо за ваш ответ! Это работает как шарм! Я не понимал, что могу использовать элемент в предложении WHERE. - person Daniel Seichter; 17.08.2016
comment
Спасибо клину, это просто спасло мне день! - person harsh.tibrewal; 18.01.2018
comment
Красиво объясненный разбивкой на разделы, это наверняка заслуживает большего количества голосов. - person Christophe Roussy; 31.07.2018
comment
@klin, может не хватать одной вещи - потенциальных проблем с параллелизмом, см. dba.stackexchange.com/questions/69471/postgres-update-limit-1/, также зачем использовать true в jsonb_set? - person Christophe Roussy; 31.07.2018
comment
@ChristopheRoussy - спасибо, я добавил соответствующие примечания. - person klin; 31.07.2018