Как обновить объекты внутри массива JSONB с помощью PostgreSQL

Допустим, вы решили сохранить данные в базе данных в виде JSON или JSONB и обнаружили, что только что создали себе новые проблемы. Вы в этом не одиноки.

JSONB — мощный инструмент, но он требует дополнительных затрат, поскольку нужно адаптировать способ запроса и обработки данных. И нередко загружать весь объект JSONB в память, преобразовывать его, используя любимый язык программирования, и затем сохранять его обратно в базу данных. И вы только что создали еще одну проблему: узкие места в производительности и перерасход ресурсов.

В этой статье мы рассмотрим, как обновить конкретное значение объекта внутри массива одним запросом.

Предположим, вы реализуете экран для хранения контактной информации о клиентах в динамическом виде. У вас возникает идея сохранить данные в поле типа JSONB, т. к. они динамические, и поэтому использование нереляционной структуры данных обосновано.

Затем вы создаете таблицу клиентов с JSONB-полем contacts и вставляете в нее некоторые данные:

insert into customers (name, contacts) values (
  'Jimi',
  '[
    {"type": "phone", "value": "+1-202-555-0105"},
    {"type": "email", "value": "jimi@gmail.com"}
  ]'
);

insert into customers (name, contacts) values (
  'Janis',
  '[
    {"type": "email", "value": "janis@gmail.com"}
   ]'
);

Довольно легко, верно? Но как обновить конкретную контактную информацию для отдельного клиента? Как изменить адрес электронной почты Джими или телефон Дженис? 🤔

К счастью, PostgreSQL — ваш друг и предоставляет функцию jsonb_set:

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

Имея столбец JSONB и уточнив путь, можно установить новое значение:

select jsonb_set(
  '[{"type": "phone", "value": "+1-202-555-0105"},{"type": "email", "value": "jimi@gmail.com"}]',
  '{1,value}',
  '"jimi.hendrix@gmail.com"',
  false
);

select jsonb_set(
  '[{"type": "email", "value": "janis@gmail.com"}]',
  '{0,value}',
  '"janis.joplin@gmail.com"',
  false
);

Приведенные выше варианты вернут:

[{"type": "phone", "value": "+1–202–555–0105"}, {"type": "email", "value": "jimi.hendrix@gmail.com"}]

[{"type": "email", "value": "janis.joplin@gmail.com"}]

Чтобы изменить адрес электронной почты Джими в списке контактной информации, вы передаете путь {1, value}, что означает второй объект в массиве (начиная с 0) и значение ключа. Это путь. То же самое относится и к изменению электронной почты Дженис, но объект с ее почтой имеет индекс 0.

Вы можете подумать: мне просто нужно использовать jsonb_set в запросе на обновление, и все готово? Это идея, но ее пока недостаточно.

Проблема с нереляционными данными в том, что они динамические. Ну, это одна из причин использования JSONB, но возникает проблема: убедитесь, что объект электронной почты Jimi имеет индекс 1, а объект электронной почты Janis имеет индекс 0 в массиве, а другой клиент может иметь совсем другой массив с другими индексами. Итак, как вы можете узнать индекс каждого типа контактной информации? 🤔

Ответ — упорядочить элементы массива и получить их индексы:

select index-1 as index
  from customers
      ,jsonb_array_elements(contacts) with ordinality arr(contact, index)
 where contact->>'type' = 'email'
   and name = 'Jimi';

Этот запрос возвращает 1, которая является индексом объекта с адресом электронной почты (типа email) внутри массива с контактной информацией клиента Jimi.

Теперь у нас все части головоломки: мы знаем, как обновить значение JSONB и как найти индекс обновляемого объекта.

Единственный оставшийся шаг — непосредственно обновление. Собрав все вместе, мы имеем:

with contact_email as (
  select ('{'||index-1||',value}')::text[] as path
    from customers
        ,jsonb_array_elements(contacts) with ordinality arr(contact, index)
   where contact->>'type' = 'email'
     and name = 'Jimi'
)
update customers
   set contacts = jsonb_set(contacts, contact_email.path, '"jimi.hendrix@gmail.com"', false)
  from contact_email
 where name = 'Jimi';

Наиболее важной частью этого запроса является блок with. Это мощная инструкция, но в качестве примера вы можете представлять ее как «способ хранения переменной», то есть пути к контактной информации, которую необходимо обновить, и которая будет динамической в зависимости от записи.

Позвольте мне немного объяснить следующую часть:

('{'||index-1||',value}')::text[] as path

Она просто строит путь вида '{1, value}', но результат нам нужно преобразовать в text[], потому что это тип, который ожидает функция jsonb_path.

Заключение

JSONB — это отличный и ценный инструмент для решения множества задач. Но имейте в виду, что вам необходимо запрашивать и обновлять данные того же типа. Это влечет за собой дополнительные затраты, которые вы должны учитывать при принятии решения о том, какие инструменты выбирать.


Автор публикации — Леандро Ческини Перейра, перевод — Евгений Зятев.