Привет, Хабр!
До PostgreSQL 17 работа с JSON‑массивами внутри JSONB‑столбца выглядела примерно так: jsonb_array_elements для развёртывания, затем цепочка ->> для извлечения полей, ::numeric для приведения типов, и обязательный LATERAL JOIN, чтобы это всё склеить. Читаемость на уровне регулярных выражений: автор понимает, что написал, остальные гадают.
PostgreSQL 17 добавил JSON_TABLE — стандартную SQL‑функцию (SQL/JSON, часть стандарта SQL:2016), которая превращает JSON‑документ в виртуальную таблицу. Один вызов вместо цепочки функций. С типизацией, обработкой ошибок и вложенными путями. Разберём, что умеет и чем отличается от привычных подходов.
Проблема: JSON‑массив внутри столбца
Таблица orders, столбец data типа jsonb. Внутри — информация о заказе, включая массив товаров:
{ "order_id": 1001, "customer": "Иванов", "items": [ {"product": "Ноутбук", "qty": 1, "price": 75000}, {"product": "Мышь", "qty": 2, "price": 1500}, {"product": "Клавиатура", "qty": 1, "price": 3500} ], "status": "shipped" }
Задача: получить плоскую таблицу «заказ — товар — количество — цена» для аналитики.
Способ до PostgreSQL 17:
SELECT (data->>'order_id')::int AS order_id, data->>'customer' AS customer, item->>'product' AS product, (item->>'qty')::int AS qty, (item->>'price')::numeric AS price FROM orders, LATERAL jsonb_array_elements(data->'items') AS item;
Работает, но каждое поле — отдельное выражение ->> с кастом. Типизация ручная. Если qty вдруг не число — ::int бросит ошибку на всю выборку. Обработка null и ошибок на совести.
Способ с JSON_TABLE (PostgreSQL 17):
SELECT jt.* FROM orders, JSON_TABLE( data, '$.items[*]' COLUMNS ( product VARCHAR(200) PATH '$.product', qty INT PATH '$.qty', price NUMERIC(12,2) PATH '$.price' ) ) AS jt;
Одна функция. Путь к массиву — '$.items[*]', каждый столбец — имя, тип, jsonpath до значения. PostgreSQL сам разворачивает массив, извлекает поля и приводит к указанным типам.
Синтаксис подробно
JSON_TABLE( json_expression, -- JSONB-столбец или выражение json_path -- JSONPath к корню итерации COLUMNS ( column_name type PATH json_path -- Обычный столбец [DEFAULT expr ON EMPTY] -- Значение, если путь не найден [DEFAULT expr ON ERROR], -- Значение, если ошибка приведения типа column_name FOR ORDINALITY, -- Порядковый номер (1, 2, 3...) NESTED PATH json_path COLUMNS ( -- Вложенный массив ... ) ) )
Разберём по частям.
PATH — jsonpath‑выражение относительно текущего элемента массива.
'$.product'— полеproductтекущего элемента.DEFAULT... ON EMPTY — что вернуть, если путь не найден в документе (поле отсутствует). Без этого — NULL.
DEFAULT... ON ERROR — что вернуть, если значение не конвертируется в указанный тип (например, строка
"abc"в INT). Без этого будет ошибка.
SELECT jt.* FROM orders, JSON_TABLE( data, '$.items[*]' COLUMNS ( row_num FOR ORDINALITY, product VARCHAR(200) PATH '$.product', qty INT PATH '$.qty' DEFAULT 0 ON ERROR, price NUMERIC(12,2) PATH '$.price' DEFAULT 0.00 ON EMPTY, discount NUMERIC(5,2) PATH '$.discount' DEFAULT 0.00 ON EMPTY ) ) AS jt;
FOR ORDINALITY — столбец с порядковым номером элемента в массиве (1, 2, 3...). Применимо, когда порядок товаров в заказе имеет значение.
DEFAULT 0 ON ERROR — если qty содержит не число (например, "два"), вместо ошибки вернётся 0.
Вложенные массивы: NESTED PATH
JSON‑документ может содержать массивы внутри массивов. Например, товар в заказе имеет список опций:
{ "items": [ { "product": "Ноутбук", "options": [ {"name": "RAM", "value": "16GB"}, {"name": "SSD", "value": "512GB"} ] } ] }
NESTED PATH разворачивает вложенный массив с сохранением связи с родительским элементом:
SELECT jt.* FROM orders, JSON_TABLE( data, '$.items[*]' COLUMNS ( product VARCHAR(200) PATH '$.product', NESTED PATH '$.options[*]' COLUMNS ( option_name VARCHAR(50) PATH '$.name', option_value VARCHAR(50) PATH '$.value' ) ) ) AS jt;
Результат:
product | option_name | option_value ----------+-------------+-------------- Ноутбук | RAM | 16GB Ноутбук | SSD | 512GB
Без NESTED PATH это потребовало бы двойного jsonb_array_elements с LATERAL и ручным JOIN. JSON_TABLE делает это декларативно.
Фильтрация внутри JSON_TABLE
JSONPath в JSON_TABLE поддерживает фильтры:
-- Только товары дороже 5000 SELECT jt.* FROM orders, JSON_TABLE( data, '$.items[*] ? (@.price > 5000)' COLUMNS ( product VARCHAR(200) PATH '$.product', price NUMERIC PATH '$.price' ) ) AS jt;
Фильтр ? (@.price > 5000) — часть JSONPath, выполняется до формирования строк. PostgreSQL не разворачивает весь массив, а сразу отсекает неподходящие элементы.
JSON_TABLE + JOIN с реляционными данными
Главная сила JSON_TABLE — бесшовная интеграция с обычным SQL:
SELECT o.id AS order_id, c.name AS customer_name, jt.product, jt.qty, jt.price, jt.qty * jt.price AS line_total, p.stock AS current_stock FROM orders o JOIN customers c ON c.id = o.customer_id CROSS JOIN LATERAL JSON_TABLE( o.data, '$.items[*]' COLUMNS ( product VARCHAR(200) PATH '$.product', qty INT PATH '$.qty', price NUMERIC PATH '$.price', product_sku VARCHAR(50) PATH '$.sku' ) ) AS jt LEFT JOIN products p ON p.sku = jt.product_sku WHERE jt.price > 1000 ORDER BY line_total DESC;
JSON‑данные развёрнуты в строки, тут же соединены с реляционными таблицами customers и products, отфильтрованы и отсортированы. Один запрос, который раньше потребовал бы CTE, подзапрос и пару LATERAL JOIN.
Производительность: что учитывать
Под капотом PostgreSQL разворачивает JSON‑массив в строки, аналогично jsonb_array_elements. Разница лишь в удобстве, типизации и обработке ошибок, а не в фундаментальной скорости.
А вот что реально влияет на производительность:
GIN‑индекс на JSONB‑столбце не ускоряет JSON_TABLE напрямую. Индекс помогает отфильтровать строки таблицы до развёртывания. Поэтому важен WHERE‑фильтр на уровне основной таблицы:
-- Медленно: развернуть ВСЕ заказы, потом фильтровать SELECT jt.* FROM orders, JSON_TABLE(...) jt WHERE jt.price > 1000; -- Быстрее: сначала отфильтровать заказы, потом развернуть SELECT jt.* FROM orders WHERE data @> '{"status": "shipped"}' -- GIN-индекс работает здесь CROSS JOIN LATERAL JSON_TABLE(...) jt WHERE jt.price > 1000;
Размер JSON‑массива у нас работает как прямой множитель. Если в каждом заказе 3 товара — всё быстро. Если 3000 — развёртывание будет дорогим. Для аналитики по огромным массивам рассмотрите материализацию: ETL‑процесс, который разворачивает JSON в отдельную реляционную таблицу и обновляет её по расписанию.
Миграция с jsonb_array_elements
Если у вас уже есть запросы на jsonb_array_elements, переход на JSON_TABLE — рефакторинг, а не переписывание. Логика та же, меняется синтаксис. Основной мотив перехода — читаемость и обработка ошибок (DEFAULT ON ERROR вместо CASE WHEN обёрток).
Если проект ещё на PostgreSQL 16 или ниже — JSON_TABLE недоступен, и jsonb_array_elements остаётся единственным вариантом. Миграция на PostgreSQL 17 стоит того по многим причинам (vacuum improvements, WAL optimizations, parallel GIN builds), и JSON_TABLE — хорошая плюшка сверху.
Пока работа с PostgreSQL ограничивается простыми таблицами и типовыми запросами, глубина не так критична. Но как только появляются JSON-данные, сложная логика выборок, вопросы производительности и разбор инцидентов, нужен уже другой уровень понимания базы. Курс «Администрирование PostgreSQL. Экспертный уровень» — для тех, кто хочет разбираться в таких сценариях не на уровне догадок.
А если хотите сначала точечно посмотреть на смежные темы в открытом формате, можно обратить внимание на несколько открытых уроков:
21 апреля, 20:00 «Функции в SQL: вычисления и переиспользование кода». Записаться
5 мая, 20:00 «Postgres + JSON: реляционная мощь, документная гибкость». Записаться
