Работа с 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, H2, Linux, Hibernate, Collections, Stream API, многопоточность, чат-боты, нейросети, файлы, devops, Docker, Nginx, Apache, maven, gradle, JUnit, YouTube, новости, руководство, ООП, алгоритмы, головоломки, rest, GraphQL, Excel, XML, json, yaml.