Витягування вкладених даних: 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.
• Додатки для аналітики часто використовують цю функцію для обробки складних багаторівневих даних.