Как работать с JSON в PostgreSQL

Как связаны JSON и PostgreSQL

Поддержка JSON в PostgreSQL — не просто галочка в списке возможностей. Это полноценный инструмент для хранения, извлечения и обработки полуструктурированных данных. Начиная с версии 9.2, СУБД позволяет работать с типом данных json, а с версии 9.4 — с более продвинутым jsonb.

В отличие от классических реляционных таблиц, JSON-объекты дают гибкость: в одной колонке можно хранить вложенные структуры, массивы, поля с произвольными именами. Такой подход удобен при работе с внешними API, логами и событиями, а также в ситуациях, когда структура данных может меняться.

Например, в одной строке таблицы может храниться вот такой объект JSON:

      {
  "user": {
    "id": 42,
    "name": "Иван"
  },
  "roles": ["admin", "editor"],
  "active": true
}

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

Разница между JSON и JSONB

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

Хранение данных

Тип json сохраняет данные в виде обычной текстовой строки. PostgreSQL проверяет, что формат корректен, но не анализирует и не изменяет структуру. Оригинальное форматирование, порядок ключей и даже пробелы — все остается как в исходной строке.

Тип jsonb (binary JSON) — это уже структурированное представление. При сохранении данных PostgreSQL:

1. парсит JSON и строит древовидную модель;
2. сортирует ключи по алфавиту;
3. удаляет пробелы, переносы строк и отступы;
4. исключает дублирующиеся ключи;
5. оптимизирует хранение для последующего поиска и анализа.

В результате jsonb занимает чуть больше ресурсов на запись, но дает значительно более высокую производительность при чтении, фильтрации и агрегации.

Сравнение

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

Тип json нельзя сравнивать оператором = вовсе (он не определен). Если нужно сравнение “как в исходной строке”, следует привести к типу text и сравнить сами строки (json::text). Семантическое сравнение структур доступно у jsonb: для объектов порядок ключей игнорируется, а для массивов — сохраняет значение.

Пример:

      -- json
SELECT '{"a":1, "b":2}'::json = '{"b":2, "a":1}'::json;
-- ошибка: operator does not exist: json = json

-- json
SELECT '{"a":1, "b":2}'::json::text = '{"b":2, "a":1}'::json::text;
-- false

-- jsonb
SELECT '{"a":1, "b":2}'::jsonb = '{"b":2, "a":1}'::jsonb;
-- true

Кодировка

В PostgreSQL JSON всегда хранится в кодировке UTF-8. Это относится как к типу json, так и к jsonbjsonb. Любые строки внутри объектов сохраняются в этом стандарте.

PostgreSQL автоматически экранирует специальные символы (кавычки, обратные слэши) при сохранении, чтобы JSON оставался корректным. При извлечении данных экранирование убирается, и вы видите нормальный читаемый текст.

Главное отличие между json и jsonb:

• json хранит строки буквально, включая все escape-последовательности Unicode.
• jsonb при записи раскодирует Unicode, теряя при этом исходное форматирование.

Пример:

      SELECT
  '{"unicode": "\u043f\u0440\u0438\u043c\u0435\u0440"}'::json  AS json_val,
  '{"unicode": "\u043f\u0440\u0438\u043c\u0435\u0440"}'::jsonb AS jsonb_val;

Результат:

                                            json_val                                     |         jsonb_val
----------------------------------------------------------------------+----------------------------
 {"unicode": "\u043f\u0440\u0438\u043c\u0435\u0440"} | {"unicode": "пример"}

То есть jsonb автоматически превращает Unicode-последовательности в читаемый текст, а json сохраняет их в исходном виде. В остальном оба типа работают с UTF-8 одинаково.

Индексация

Одно из главных преимуществ jsonb — возможность индексировать данные внутри объекта. Это ускоряет поиск по ключам и значениям в больших таблицах. Тип json нельзя индексировать напрямую по содержимому, потому что PostgreSQL видит его как обычный текст.

PostgreSQL поддерживает несколько способов индексирования jsonb, но чаще всего используется GIN (Generalized Inverted Index). Пример создания стандартного GIN-индекса:

      CREATE INDEX idx_payload ON events USING GIN (payload);

Этот индекс работает с большинством операторов поиска, таких как:

• @> — проверка, что объект содержит другой объект;
• ? — проверка наличия ключа;
• ?| и ?& — проверка наличия хотя бы одного или всех ключей.

Если основная задача — ускорить поиск по оператору @>, можно использовать метод jsonb_path_ops:

      CREATE INDEX idx_payload_path ON events USING GIN (payload jsonb_path_ops);

Такой индекс быстрее для @>, но не поддерживает другие операторы (?, ?|, ?&).

Пример использования индекса:

      SELECT * FROM events
WHERE payload @> '{"event_type": "login"}';

Благодаря GIN-индексу PostgreSQL быстро находит все записи, где payload содержит указанный объект.

Преобразование типов

Если у вас в таблице хранятся данные в виде json, и вы хотите воспользоваться функциональностью jsonb, можно легко преобразовать тип:

      SELECT my_column::jsonb FROM my_table;

Обратное преобразование — jsonb -> json — не поддерживается напрямую, поскольку jsonb при сохранении теряет оригинальное форматирование. Однако вы можете получить строковое представление:

      SELECT my_column::text FROM my_table;

или использовать функцию to_json() для безопасной сериализации:

      SELECT to_json(my_column) FROM my_table;

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

Создание таблицы

Чтобы создать таблицу с полем типа json или jsonb, укажите нужный тип данных при описании столбца:

      CREATE TABLE users (
  id serial PRIMARY KEY,
  profile jsonb
);

Добавление строк и столбцов в таблицу

Добавление строк

Добавить JSON-объект можно напрямую в виде строки или через встроенные функции:

      -- Добавление с использованием функции jsonb_build_object
INSERT INTO users (profile) VALUES
 (jsonb_build_object(
      'name', 'Анна',
      'age', 30,
      'address', jsonb_build_object(
          'city', 'Москва',
          'zip', '101000'
      )
  ));

-- Добавление строки с JSON-объектом в виде текста
INSERT INTO users (profile) VALUES
 ('{"name": "Иван", "age": 25, "address": {"city": "Санкт-Петербург", "zip": "190000"}}'),
  ('{"name": "Ольга", "age": 35}');

Добавление новых столбцов

Добавить колонку с типом json или jsonb можно командой ALTER TABLE:

      ALTER TABLE users ADD COLUMN metadata JSON;

ALTER TABLE users ADD COLUMN settings JSONB DEFAULT '{}'::jsonb;

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

PostgreSQL позволяет напрямую обращаться к полям JSON-объекта внутри таблицы — извлекать значения, фильтровать строки и даже агрегировать данные. Для этого используются специальные операторы и функции, которые отличаются в зависимости от типа данных (json или jsonb).

Извлечение значений

Операторы -> и ->> позволяют получить значение из объекта по ключу:

-> — возвращает значение в виде JSON;

->> — возвращает значение как текст.

Пример:

      SELECT
  profile -> 'name'   AS name_json,
  profile ->> 'name'  AS name_text
FROM users;

Результат:

      name_json | name_text 
----------+-----------
 "Анна"   | Анна
 "Иван"   | Иван
 "Ольга"  | Ольга

Обычно для фильтрации и отображения данных используют ->>, а для дальнейшей работы с JSON — ->.

Извлечение вложенных значений

Чтобы получить значение из вложенного объекта, можно использовать цепочку операторов:

      SELECT
  profile -> 'address' ->> 'city' AS city
FROM users;

Также доступны операторы #> и #>>, которые принимают путь в виде массива:

• #> — возвращает JSON;
• #>> — возвращает текст.

Пример:

      SELECT
  profile #>> '{address,city}' AS city
FROM users;

Результат:

          city      
--------------- 
 Москва         
 Санкт-Петербург
 null

В последней строке представлен null, потому что у Ольги нет поля address.

Фильтрация по содержимому

Оператор @> проверяет, содержит ли JSON-объект указанный фрагмент.

Пример: найти всех пользователей, чей возраст — 30:

      SELECT *
FROM users
WHERE profile @> '{"age": 30}';

Результат:

      id |                 profile                 
---+-----------------------------------------
 1 | {"name": "Анна", "age": 30, "address": {"city": "Москва", "zip": "101000"}}

Проверка наличия ключей

Иногда важно проверить, есть ли в объекте нужное поле. PostgreSQL предлагает:

• ? — проверяет наличие одного ключа;
• ?| — проверяет наличие хотя бы одного из списка ключей.

Примеры:

      -- Пользователи, у которых есть поле "address"
SELECT id
FROM users
WHERE profile ? 'address';

-- Есть ли хотя бы один из полей "email" или "phone"
SELECT id
FROM users
WHERE profile ?| array['email', 'phone'];

Результаты:

      -- Первый запрос:
 id 
----
 1
 2

-- Второй запрос (ничего не вернет):
 id 
----
 (0 rows)

Агрегация данных

Если в объекте хранятся числовые значения (например, возраст), их можно агрегировать:

      SELECT
  AVG((profile ->> 'age')::int) AS avg_age,
  MAX((profile ->> 'age')::int) AS max_age
FROM users;

Результат:

      avg_age | max_age 
--------+---------
  30.0  |   35

Специальные функции PostgreSQL для json/jsonb

PostgreSQL предоставляет целый набор встроенных функций для работы с JSON-данными. Они позволяют:

• разобрать объект на ключи и значения;
• узнать тип хранимого значения;
• извлечь вложенные данные по цепочке ключей;
• вывести JSON в читаемом виде.

Перебор ключей и значений

Функция json_each (jsonb_each) разбивает JSON-объект на пары «ключ – значение».

Пример:

      SELECT *
FROM jsonb_each('{"name": "Анна", "age": 30}'::jsonb);

Результат:

       key  | value 
------+-------
 name | "Анна"
 age  | 30

Получение списка ключей

json_object_keys (jsonb_object_keys) возвращает только список ключей верхнего уровня в JSON-объекте.

Пример:

      SELECT jsonb_object_keys(profile)
FROM users
WHERE id = 1;

Результат:

      jsonb_object_keys 
-------------------
 name
 age
 address

Определение типа значения

json_typeof (jsonb_typeof) определяет тип значения, находящегося по указанному ключу. Результатом будет строка: object, array, string, number, boolean или null.

Пример:

      SELECT
  jsonb_typeof(profile -> 'age')     AS age_type,
  jsonb_typeof(profile -> 'address') AS address_type
FROM users
WHERE id = 1;

Результат:

      age_type | address_type 
---------+--------------
 number  | object

Извлечение вложенных значений по пути

json_extract_path (jsonb_extract_path) извлекает вложенное значение по пути из ключей.

Пример:

      SELECT jsonb_extract_path(profile, 'address', 'city') AS city_jsonb
FROM users
WHERE id = 2;

Результат:

        city_jsonb    
----------------
 "Санкт-Петербург"

Форматирование JSON для читаемого вывода

jsonb_pretty форматирует JSON в читаемый вид: с отступами, переносами строк, оформлением вложенных объектов.

Пример:

      SELECT jsonb_pretty(profile)
FROM users
WHERE id = 1;

Результат:

      {
    "name": "Анна",
    "age": 30,
    "address": {
        "city": "Москва",
        "zip": "101000"
    }
}

Преобразование таблицы в список объектов JSON

PostgreSQL позволяет не только разбирать JSON-объекты, но и собирать их обратно — из строк таблицы формировать полноценные JSON-массивы или отдельные объекты.

Создание JSON-объекта из строки

Функция jsonb_build_object создает JSON-объект из пар ключ–значение. Ключи указываются вручную, а значения берутся из полей таблицы.

Пример — соберем JSON для каждого пользователя:

      SELECT jsonb_build_object(
  'id', id,
  'name', profile ->> 'name',
  'city', profile #>> '{address,city}'
) AS user_jsonb
FROM users;

Результат:

                        user_jsonb                   
-----------------------------------------------
 {"id": 1, "name": "Анна", "city": "Москва"}
 {"id": 2, "name": "Иван", "city": "Санкт-Петербург"}
 {"id": 3, "name": "Ольга", "city": null}

Значение можно кастомизировать под нужную структуру — добавлять, убирать или переименовывать ключи.

Сборка JSON-массива из набора строк

Если нужно получить один массив объектов, используют агрегатную функцию jsonb_agg. Она собирает результат из всех строк в один JSON-массив.

Пример — соберем массив всех пользователей:

      SELECT jsonb_agg(
  jsonb_build_object(
    'id', id,
    'name', profile ->> 'name',
    'age', profile ->> 'age'
  )
) AS users_array
FROM users;

Результат:

                                   users_array                              
---------------------------------------------------------------------
[
  {"id": 1, "name": "Анна", "age": "30"},
  {"id": 2, "name": "Иван", "age": "25"},
  {"id": 3, "name": "Ольга", "age": "35"}
]

Динамическое формирование ключей

Если нужно, чтобы ключи в JSON-объекте формировались из значений в таблице, используют jsonb_object_agg:

      SELECT jsonb_object_agg(
  id,
  jsonb_build_object(
    'name', profile ->> 'name',
    'city', profile #>> '{address,city}'
  )
) AS users_by_id
FROM users;

Результат:

                            users_by_id                      
--------------------------------------------------------
{
  "1": {"name": "Анна", "city": "Москва"},
  "2": {"name": "Иван", "city": "Санкт-Петербург"},
  "3": {"name": "Ольга", "city": null}
}

Если результат планируется передавать во внешний API, можно обернуть результат в to_json, чтобы сохранить типы и порядок полей.

Сохранение данных таблицы в формате JSON

PostgreSQL позволяет экспортировать данные из таблицы в формате JSON — как отдельных объектов, так и массивов. Ниже рассмотрим несколько подходов.

Сохранение каждой строки как JSON-объект

Самый простой способ — превратить каждую строку таблицы в JSON-объект с помощью функции row_to_json:

      SELECT row_to_json(users)
FROM users;

Результат:

                            row_to_json                       
----------------------------------------------------------
 {"id":1,"profile":{"name":"Анна","age":30,"address":{"city":"Москва"}}}
 {"id":2,"profile":{"name":"Иван","age":25,"address":{"city":"Санкт-Петербург"}}}
 {"id":3,"profile":{"name":"Ольга","age":35,"address":{}}}

Здесь PostgreSQL автоматически превращает строку таблицы в объект, включая вложенные структуры.

Экспорт массива всех пользователей в JSON

Чтобы получить один JSON-массив всех строк, используем json_agg поверх row_to_json:

      SELECT json_agg(row_to_json(users)) AS users_json
FROM users;

Результат:

                                      users_json                                
-------------------------------------------------------------------------
[
  {"id":1,"profile":{"name":"Анна","age":30,"address":{"city":"Москва"}}},
  {"id":2,"profile":{"name":"Иван","age":25,"address":{"city":"Санкт-Петербург"}}},
  {"id":3,"profile":{"name":"Ольга","age":35,"address":{}}}
]

Этот формат можно сразу отправить клиенту или сохранить в файл.

Кастомные поля и структура объекта

Если для экспорта нужны только конкретные поля таблицы, можно использовать json_build_object и json_agg:

      SELECT json_agg(
  json_build_object(
    'user_id', id,
    'name', profile ->> 'name',
    'city', profile #>> '{address,city}'
  )
) AS custom_users
FROM users;

Результат:

                               custom_users                         
-------------------------------------------------------------
[
  {"user_id":1,"name":"Анна","city":"Москва"},
  {"user_id":2,"name":"Иван","city":"Санкт-Петербург"},
  {"user_id":3,"name":"Ольга","city":null}
]

Выгрузка JSON в файл

Если вы работаете в psql, JSON можно сохранить в файл напрямую, используя следующее выражение:

      \copy (SELECT json_agg(row_to_json(users)) FROM users) TO 'users.json'

Или через команду COPY в SQL (если используется внешний скрипт):

      COPY (
  SELECT json_agg(row_to_json(users)) FROM users 
) TO '//6ef4e6a1-9d49-47ac-bfed-170f67a815cf.selcdn.net/path/to/file.json';

Для COPY путь должен быть доступен серверу PostgreSQL.

Заключение

PostgreSQL позволяет работать с JSON-данными на уровне SQL-запросов: хранить, извлекать, фильтровать и агрегировать без дополнительной обработки на стороне приложения. Это упрощает разработку и делает работу с полуструктурированными данными нативной частью запросов.