JSON_TABLE¶

JSON_TABLE — это табличная функция языка SQL, предназначенная для преобразования данных в формате JSON (JavaScript Object Notation) в реляционное табличное представление. Функция позволяет извлекать значения из JSON-документов, сопоставлять их с колонками реляционной таблицы и возвращать результат в виде набора строк, который можно использовать в запросах, подзапросах и операциях объединения. JSON_TABLE является частью стандарта SQL/JSON (ISO/IEC 19075-6) и реализована в ряде систем управления базами данных (СУБД), включая Oracle Database, MySQL, MariaDB, PostgreSQL (начиная с версии 12) и IBM Db2.

¶История и стандартизация

Функция JSON_TABLE была введена как часть расширения стандарта SQL для работы с JSON-данными. Первая значимая реализация появилась в Oracle Database 12c (2013 год), где она стала ключевым инструментом для интеграции документоориентированного подхода в реляционную модель. В 2016 году стандарт SQL:2016 включил JSON_TABLE в спецификацию SQL/JSON, определив синтаксис и семантику для извлечения данных из JSON-документов. Последующие версии стандарта (SQL:2020, SQL:2023) уточнили поведение функции, добавив поддержку вложенных путей, обработку массивов и типов данных.

В PostgreSQL поддержка JSON_TABLE была добавлена в версии 12 (2019 год) с использованием оператора jsonb_path_query и последующим расширением до полноценной функции в версии 15 (2022 год). В MySQL функция появилась в версии 8.0 (2018 год) с ограниченной функциональностью, которая была значительно расширена в версии 8.4 (2024 год). В MariaDB функция реализована в версии 10.6 (2021 год).

¶Синтаксис

Общий синтаксис функции JSON_TABLE в соответствии со стандартом SQL/JSON выглядит следующим образом:

``sql JSON_TABLE( json_expr, path_expression COLUMNS ( column_name type PATH path_expression [ON EMPTY | ON ERROR], ... ) ) ``

Основные элементы:

• json_expr — выражение, возвращающее JSON-документ (столбец таблицы, литерал, результат другой функции).
• path_expression — JSON Path-выражение, определяющее корневой путь для извлечения данных (например, '$.items[*]' для массива объектов).
• COLUMNS — описание колонок результирующей таблицы. Каждая колонка задаётся именем, типом данных и JSON Path-выражением для извлечения значения. Дополнительно могут указываться опции обработки пустых значений (ON EMPTY) или ошибок (ON ERROR).

В различных СУБД синтаксис может отличаться. Например, в Oracle Database используется ключевое слово NESTED PATH для работы с вложенными объектами, а в PostgreSQL — оператор FOR ORDINALITY для генерации порядкового номера строки.

¶Примеры использования

¶Базовое извлечение данных

Пусть имеется таблица employees с колонкой data, содержащей JSON-документы вида:

``json {"id": 1, "name": "Иван", "department": "Разработка"} ``

Запрос с использованием JSON_TABLE:

``sql SELECT jt.* FROM employees, JSON_TABLE( employees.data, '$' COLUMNS ( emp_id NUMBER PATH '$.id', emp_name VARCHAR2(100) PATH '$.name', dept VARCHAR2(50) PATH '$.department' ) ) jt; ``

Результат:

¶Работа с массивами

Если JSON-документ содержит массив объектов:

``json {"items": [{"product": "Ноутбук", "price": 50000}, {"product": "Мышь", "price": 1500}]} ``

Запрос:

``sql SELECT jt. FROM orders, JSON_TABLE( orders.data, '$.items[]' COLUMNS ( product VARCHAR2(100) PATH '$.product', price NUMBER PATH '$.price' ) ) jt; ``

Результат:

¶Вложенные объекты и массивы

В Oracle Database поддерживается синтаксис NESTED PATH для извлечения данных из вложенных структур:

``sql SELECT jt. FROM documents, JSON_TABLE( documents.data, '$' COLUMNS ( doc_id NUMBER PATH '$.id', NESTED PATH '$.authors[]' COLUMNS ( author_name VARCHAR2(100) PATH '$.name', author_email VARCHAR2(100) PATH '$.email' ) ) ) jt; ``

¶Особенности реализации в различных СУБД

¶Oracle Database

• Первая и наиболее полная реализация. Поддерживает NESTED PATH, ORDINALITY, ON EMPTY, ON ERROR, а также функции преобразования типов.
• Позволяет создавать материализованные представления на основе JSON_TABLE.

¶PostgreSQL

• Реализована через оператор jsonb_path_query и функцию jsonb_path_exists. В версии 15 добавлен синтаксис, близкий к стандарту.
• Поддерживает FOR ORDINALITY для генерации порядкового номера строки.
• Требует явного указания типа данных для каждой колонки.

¶MySQL

• В версии 8.0 поддерживается только базовый синтаксис без NESTED PATH. В версии 8.4 добавлена поддержка вложенных путей.
• Не поддерживает ON EMPTY и ON ERROR в полном объёме.
• Для работы с массивами требуется использование оператора ->> в сочетании с JSON_TABLE.

¶MariaDB

• Реализация близка к MySQL, но с некоторыми отличиями в синтаксисе JSON Path.
• Поддерживает FOR ORDINALITY с версии 10.6.

¶Применение

JSON_TABLE используется в следующих сценариях:

• Интеграция данных: преобразование JSON-данных из веб-сервисов, API и NoSQL-хранилищ в реляционную форму для аналитики и отчётности.
• Обработка логов: извлечение структурированных данных из JSON-логов (например, веб-серверов, приложений).
• Работа с документами: хранение документов в колонках JSON и их последующее реляционное представление для выполнения сложных запросов.
• Миграция данных: перенос данных из документоориентированных баз (MongoDB, Couchbase) в реляционные СУБД.

¶Производительность

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

• Использовать индексы на JSON-колонках (например, функциональные индексы в Oracle или GIN-индексы в PostgreSQL).
• Ограничивать количество извлекаемых путей.
• Применять фильтрацию на уровне JSON Path (например, $.items[?(@.price > 1000)]).

¶Ограничения

• Не все СУБД поддерживают полный синтаксис стандарта SQL/JSON.
• JSON_TABLE не может изменять исходные данные — она только читает JSON-документы.
• Для очень больших JSON-документов (более 1 МБ) может наблюдаться снижение производительности.
• В некоторых реализациях (MySQL, MariaDB) отсутствует поддержка вложенных массивов и объектов.

¶Источники

• ISO/IEC 19075-6:2021 — Information technology — Guidance for the use of database language SQL — Part 6: JSON support.
• Oracle Database SQL Language Reference, 21c — JSON_TABLE.
• PostgreSQL Documentation, Chapter 9 — JSON Functions and Operators.
• MySQL Reference Manual, 8.4 — JSON_TABLE Function.
• MariaDB Knowledge Base, JSON_TABLE.