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

Допустим, вы решили сохранить данные в базе данных как json или jsonb и обнаружили, что вы просто создали для себя новые проблемы, которых у вас не было раньше. Ты не одинок.

JSONB - это мощный инструмент, но он требует определенных затрат, потому что вам нужно адаптировать способ запроса и обработки данных.

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

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

Финальный запрос находится в конце статьи, и вы можете посмотреть живой пример на DB Fiddle, чтобы скопировать, вставить и поиграться с ним.

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

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

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

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

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

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

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

[{«Type»: «phone», «value»: «+ 1–202–555–0105»}, {«type»: «email», «value»: «jimi.hendrix@gmail.com»}]
[{«Type»: «email», «value»: «janis.joplin@gmail.com»}]

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

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

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

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

Этот запрос возвращает значение 1, которое является индексом из объекта электронной почты, внутри contact в массиве customers.

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

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

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

Позвольте мне немного пояснить:

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

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

В заключении

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