Работа с json в PostgreSQL

11 мая 2023

Содержание

Разница между типами json и jsonb
Создание объектов json
Извлечение значений из полей json-объекта
Список объектов json
Сохранение данных в формате json
Выводы

JSON (JavaScript Object Notation) является форматом для хранения и передачи данных в форме объектов, массивов и других значений, которые можно легко преобразовать в текстовую строку. PostgreSQL поддерживает хранение данных в формате JSON, а также множество функций для обработки и анализа JSON-данных.

В PostgreSQL JSON может храниться в столбцах таблицы, при этом можно использовать индексы для ускорения поиска и запросов. Кроме того, в PostgreSQL есть возможность использовать операторы для извлечения и модификации данных в формате JSON.

Разница между типами json и jsonb

В PostgreSQL есть два типа данных для работы с JSON: json и jsonb. Они отличаются друг от друга способом хранения данных и способом работы с ними.

Тип данных json использует текстовый формат хранения JSON-данных, который позволяет хранить только простые типы данных JSON: строки, числа, логические значения, null и массивы или объекты, состоящие только из этих типов. Это означает, что при хранении данных типа json не происходит никакой оптимизации, и любые изменения в данных приводят к перезаписи всей строки JSON.

Тип данных jsonb использует бинарный формат хранения JSON-данных, который позволяет хранить все типы данных JSON, включая вложенные объекты и массивы, а также дополнительные типы данных, такие как булевы значения, даты и времена и т.д. Бинарный формат данных jsonb позволяет эффективно хранить, индексировать и быстро выполнять запросы к данным в формате JSON, и при изменении данных не требует перезаписи всей строки JSON, а лишь тех частей, которые изменились.

В целом, использование типа данных jsonb является более предпочтительным в PostgreSQL, так как он позволяет более эффективно хранить и обрабатывать JSON-данные, а также более быстро выполнять запросы, связанные с ними. Поэтому далее будем рассматривать только jsonb.

Создание объектов json

Одной из основных функций для работы с JSON является jsonb_build_object(), которая позволяет создавать объекты JSON внутри запросов. Это может быть полезно, например, при импорте данных из внешних источников.

Предположим, что мы хотим в select-запросе динамически сгенерировать json-объект. Сделать это можно так:

SELECT jsonb_build_object('name', 'Иван', 'age', 30, 'city', 'Москва') AS person;

Этот запрос создаст JSON-объект со свойствами name, age и city, которые будут содержать соответствующие значения. Результатом запроса будет следующий JSON-объект:

{
  "age": 30,
  "city": "Москва",
  "name": "Иван"
}

Извлечение значений из полей json-объекта

В PostgreSQL также можно использовать операторы -> и ->> для извлечения данных из JSON-объектов. Оператор -> возвращает значение по указанному ключу, а оператор ->> возвращает значение в форме текстовой строки. Рассмотрим это на примерах.

SELECT '{"name": "Иван", "age": 30, "city": "Москва"}'::jsonb -> 'age' as age;
-- результат будет числом 30

Тут мы динамически формируем json-объект из строки и приводим его к типу jsonb. Затем выбираем значение поля age. Поскольку здесь одинарная стрелка, то результат будет числом 30. Если же поставить двойную стрелку, то результат будет строкой «30».

SELECT '{"name": "Иван", "age": 30, "city": "Москва"}'::jsonb ->> 'age' as age;
-- результат будет строкой "30"

Список объектов json

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

Мы можем всё её содержимое преобразовать в один json, в котором будет список соответствующих объектов (один объект = одна строка в таблице).

SELECT jsonb_agg(jsonb_build_object('name', name, 'age', age)) AS people
FROM people_table;

В результате получим:

[
  {
    "age": 30,
    "name": "Иван"
  },
  {
    "age": 28,
    "name": "Пётр"
  },
  {
    "age": null,
    "name": "Александр"
  }
]

Сохранение данных в формате json

Давайте расширим таблицу people_table, добавив в неё новую колонку json_data. В эту колонку поместим все данные той же строки в формате json. Для того, чтобы к этой колонке можно было выполнять рассмотренные выше запросы, она должна иметь тип jsonb.

alter table people_table
add json_data jsonb;

Теперь выполним sql-update, чтобы заполнить новый столбец:

update people_table o
set json_data = (select to_jsonb(e.*)
                 from (select i.id, i.name, i.age
                       from people_table i
                       where i.id = o.id) e);

Тут мы используем два подзапроса.

Самый внутренний select выбирает нужные поля
Строки внешнего и внутреннего запроса связываем по id
Для преобразования в json используем функцию to_jsonb() в среднем запросе.

В итоге таблица примет следующий вид:

Выводы

В целом, работа с JSON в PostgreSQL позволяет эффективно использовать возможности этого формата для обработки и анализа данных. PostgreSQL предоставляет широкий спектр функций для работы с JSON, что делает его очень гибким и мощным инструментом для работы с данными в формате JSON.

Облако тэгов

Kotlin, Java, Spring, Spring Boot, Spring Data, SQL, PostgreSQL, Oracle, Linux, Hibernate, Collections, Stream API, многопоточность, файлы, Nginx, Apache, maven, gradle, JUnit, YouTube, новости, руководство, ООП, алгоритмы, головоломки, rest, GraphQL, Excel, XML, json, yaml.