Извлечение вложенных данных: jsonb_to_recordset()

Далее мы переходим к более сложным сценариям работы с данными в формате JSONB — извлечению вложенных данных и преобразованию их в строки таблицы. Скажете, зачем это нужно? Очень просто! Представьте себе, что вам дали JSON-объект с массивом покупок и попросили посчитать общую сумму всех покупок или вывести их в табличном виде для отчета. Мы разберем это прямо сейчас!

Почему нельзя просто работать с JSON как с текстом или структурой? Давайте рассмотрим сценарий. Во многих реальных приложениях данные хранятся в виде массивов JSON:

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

Это удобно, но при анализе данных часто нужно превратить массив в таблицу для таких операций, как фильтрация, сортировка и агрегация. Представьте себе: «Все заказы на сумму больше 500 долларов». JSONB сам по себе не позволяет сделать это так удобно, как хотелось бы. Именно здесь на помощь приходит jsonb_to_recordset().

Работа с jsonb_to_recordset()

Функция jsonb_to_recordset() позволяет преобразовать массив JSONB-объектов в строки таблицы. Она буквально превращает каждый элемент массива в строку, а ключи превращает в столбцы. Эта функция незаменима в сценариях, где данные вложены глубоко или содержат массивы объектов.

Синтаксис

SELECT *
FROM jsonb_to_recordset('[ массив JSONB ]') AS alias(колонка1 ТИП, колонка2 ТИП, ...);

• [ массив JSONB ]: массив объектов JSON, из которого мы извлекаем данные.
• AS alias: создаем временное имя для результирующей таблицы.
• колонка1 ТИП, колонка2 ТИП: определяем, как должны называться столбцы и какие типы данных они будут использовать (например, INTEGER, TEXT, NUMERIC).

Пример: преобразование массива JSONB в строки

Рассмотрим, что у нас есть следующая таблица:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

И в таблице лежат такие данные:

Теперь задача: вывести список всех продуктов для всех заказов в табличном виде. Вот как это сделать с помощью jsonb_to_recordset():

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

Результат:

Пример: фильтрация данных

Давайте усложним задачу. Хотим отобразить только те продукты из заказов, которые стоят дороже 100 долларов:

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

Результат:

Пример: агрегация данных

Как насчет подсчета общей суммы всех продуктов в заказах? Просто используем агрегатные функции:

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

Результат:

Важные замечания

Убедитесь, что структура JSON-массива одинакова для всех объектов. Если объект имеет разные ключи или вложенные структуры, вы можете получить ошибки или неожиданное поведение.

Правильно задавайте типы данных для извлеченных столбцов. Например, если ключ содержит дату, используйте DATE, для чисел — NUMERIC или INTEGER.

Помните, что jsonb_to_recordset() преобразует только массивы JSONB; с одиночными объектами она не работает.

Типичные ошибки и способы их предотвращения

Неправильное использование типов данных: если в JSONB массиве есть значения с разными типами (например, строка вместо числа), это приведет к ошибке. Рекомендуется приводить данные к нужному формату до использования функции.

Обращение к неправильным ключам: если ключ отсутствует в одном из объектов массива, будет ошибка. Проверьте структуру данных перед выполнением запроса.

Отсутствие данных: если JSONB-колонка пуста (NULL), функция не будет возвращать результатов. В таких случаях добавляйте проверки, например COALESCE().

Практическое применение

jsonb_to_recordset() широко используется в реальных задачах, включая обработку заказов, анализ отчетов, логирование действий пользователей и обработку внешних API. Например:

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