Как использовать JSON в СУБД РЕД База Данных 5. Основные функции

Привет, Хабр, это команда разработки СУБД РЕД База Данных от компании «РЕД СОФТ»! Недавно мы представили мажорный релиз РЕД Базы Данных 5.0, в котором добавили поддержку работы с JSON‑форматом. В этом материале мы расскажем, как пользоваться основными функциями JSON в СУБД РЕД База Данных. И, так как РЕД База Данных основана на Firebird, в ближайшее время формат будет портирован и в эту open source СУБД.

Немного теории

Немного теории не помешает. Многие слышали про JSON, почти каждый второй материал подробно его описывает, но в общих чертах напомнить о стандарте стоит.

JSON (JavaScript Object Notation) — это текстовый формат для хранения и передачи структурированных данных. Стандарт основан на синтаксисе объекта в JavaScript, но не привязан к нему. Этот формат состоит из следующих элементов и их комбинаций: «Объект», «Массив», «Скаляр», «null». «Объект» представляет собой пару или набор пар ключ‑значения. «Массив» состоит из упорядоченного списка значений, заключённых в квадратные скобки. «Скаляр» используется для представления простых значений, таких как строки, числа или булевые значения. «null», указывающий на отсутствие значения, определяется в JSON, как и в SQL, отдельно.

{    "website": {      "url": "https://www.example.com",      "description": "Тут есть печеньки",      "contact": {        "email": "info@example.com",        "phone": "+7 (123) 456-7890"      },      "features": [        "Блог",        "Онлайн-магазин",        "Форум"      ]    }  } 

В Ред Базе Данных 5.0 представлена практически полная поддержка стандарта SQL JSON, который подробно описан в ISO/IEC TR 19 075–6. Кроме того, добавлены некоторые дополнения, упрощающие жизнь пользователям, о которых мы также расскажем в этой статье.

Функции JSON в СУБД РЕД База Данных 5

Переходим к сути, поговорим о возможностях JSON в СУБД РЕД База Данных. Инструментарий включает различные функции для генерации, запроса и валидации данных в JSON: JSON_VALUE, JSON_QUERY, JSON_ARRAY, JSON_OBJECT, JSON_ARRAYAGG, JSON_OBJECTAGG, IS JSON, JSON_EXISTS, JSON_MODIFY. Это основные функции, которые будут представлены в этом материале. Также реализован JSON_TABLE, но про него планируется отдельная статья.

Пример

Попробуем представить реальную задачу, с одной стороны достаточно простую, чтобы её можно было воспроизвести, а с другой — не слишком оторванную от реальности.

Предположим, в нашем приложении необходимо сохранять статистику о погоде каждый день.

 weather.json'   {  "latitude": 52.52,  "longitude": 13.419998,  "generationtime_ms": 0.08499622344970703,  "utc_offset_seconds": 0,  "timezone": "GMT",  "timezone_abbreviation": "GMT",  "elevation": 38.0,  "current_units": {  "time": "iso8601",  "interval": "seconds",  "temperature_2m": "°C",  "wind_speed_10m": "km/h"  },  "current": {  "time": "2024-07-31T08:00",  "interval": 900,  "temperature_2m": 24.2,  "wind_speed_10m": 2.9  },  "hourly_units": {  "time": "iso8601",  "temperature_2m": "°C",  "relative_humidity_2m": "%",  "wind_speed_10m": "km/h"  },  "hourly": {  "time": [ "2024-07-31T00:00", "2024-07-31T01:00", "2024-07-31T02:00", "2024-07-31T03:00" ],  "temperature_2m": [ 16.9, 16.4, 15.8, 15.3 ],  "relative_humidity_2m": [ 85, 84, 84, 88],  "wind_speed_10m": [ 5.2, 5.0, 4.7, 3.4 ]  }  }

Теперь нужно его парсить. Обычно для этого на backend используется библиотека, и затем нужная информация передаётся в СУБД, где заносится в таблицу. Но на практике всё гораздо сложнее. Вставка может происходить в несколько таблиц с использованием процедур, поэтому есть соблазн сделать как можно больше действий на уровне СУБД.

JSON_VALUE

Первая наша задача — вытащить время, для которого собирается статистика. Видно, что оно записано в поле time, которое хранится в объекте у поля current. Чтобы обращаться к таким объектам, на языке программирования необходимо написать что‑то такое:

json["current"]["time"].asString()

В JSON SQL принцип схожий. Тут для обращения к элементам JSON существует язык JSON Path. Через JSON Path обращение выглядит следующим образом:

$.current.time

Здесь $ — корневой элемент, затем идут названия полей. В этом языке есть два режима: lax (по умолчанию) и strict. Первый более дружелюбный, он допускает работу с нечёткой схемой, например, когда ожидается поле, а на самом деле JSON массив. Также этот режим добавляет авторазворачивание и автосворачивание элементов при необходимости и скрывает некоторые ошибки.

Например, есть JSON {“value”:42}. И мы хотим получить значение 42. Мы обращаемся по этому полю в пути:

$.value

Однако почему‑то нам передали JSON {“value”:[42]}. Получается, в поле value не скаляр, а массив. И надо менять путь на $.value[0]. Но режим lax может адаптировать путь, и сам добавляет [0] при необходимости.

Другой режим — strict. В нём нет авторазворачивания/сворачивания, и он выбрасывает ошибки при любом несоответствии пути (должен выбрасывать, но мы решили отложить внедрение этого поведения). В strict всё строго. Хочешь скаляр, а там массив — выдаётся ошибка. Если необходимо обращаться к массиву, то используется индекс (нумерация с 0). Нам как раз нужно узнать первую временную метку из массива hourly.

Для этого подойдёт следующий путь:

lax $.hourly.time[0]

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

$.hourly.time[5 to last] или $.hourly.time[0,1,3, -1]

Отрицательные индексы работают как в языке Python. last — это эквивалент -1. В стандарте используется только last, а обращаться к остальным предлагается как last -1, last -2 и тому подобное, но мы решили немного упростить жизнь пользователям.

Зная путь и тип объекта (время хранится в строке, то есть это скаляр), мы должны воспользоваться функцией для извлечения скалярных значений (число, строку, булево значение) — JSON_VALUE. Первый аргумент у неё текст JSON, второй путь:

SELECT JSON_VALUE(READ_FILE('json_files/weather.json'), '$.current.time') FROM rdb$database;

Ответ: 2024-07-31T08:00

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

Что касается JSON null, то он автоматически конвертируется в SQL null. Для остальных значений необходимо явно указать возвращаемый тип с помощью предложения returning. Например:

SELECT JSON_VALUE(READ_FILE('json_files/weather.json'), '$.current.temperature_2m' returning float) from rdb$database;

В случае, если указанного пути нет в JSON, по умолчанию возвращается SQL null. Это поведение можно регулировать предложением ON EMPTY. Среди опций доступны:

ERROR ON EMPTY,

NULL ON EMPTY,

DEFAULT <value> ON EMPTY.

Вместо <value> подставляется нужное значение.

По аналогии с контролем «пустого» значения, есть и контроль поведения при ошибке: ON ERROR. Для него опции аналогичны.

JSON_QEURY

weather.json'   {   "latitude": 52.52,   "longitude": 13.419998,   "generationtime_ms": 0.08499622344970703,   "utc_offset_seconds": 0,   "timezone": "GMT",   "timezone_abbreviation": "GMT",   "elevation": 38.0,   "current_units": {    "time": "iso8601",⁷    "interval": "seconds",    "temperature_2m": "°C",    "wind_speed_10m": "km/h"   },   "current": {    "time": "2024-07-31T08:00",    "interval": 900,    "temperature_2m": 24.2,    "wind_speed_10m": 2.9   },   "hourly_units": {    "time": "iso8601",    "temperature_2m": "°C",    "relative_humidity_2m": "%",    "wind_speed_10m": "km/h"   },   "hourly": {    "time": [ "2024-07-31T00:00", "2024-07-31T01:00", "2024-07-31T02:00", "2024-07-31T03:00" ],    "temperature_2m": [ 16.9, 16.4, 15.8, 15.3 ],    "relative_humidity_2m": [ 85, 84, 84, 88],    "wind_speed_10m": [ 5.2, 5.0, 4.7, 3.4 ]   }  }

Например, нам надо сохранить метаинформацию из hourly_units (там хранятся системы отсчёта). Так как количество полей может разниться, мы решили сохранять объект hourly_units целиком, а не разбивать его на столбцы.

Чтобы его вытащить, надо воспользоваться функцией JSON_QUERY. Она извлекает либо объект, либо массив. Нам надо извлечь текущие значения погоды из JSON в виде объекта, для этого мы используем следующий запрос:

SELECT JSON_QUERY(READ_FILE('json_files/weather.json'), '$.hourly_units') from rdb$database;

Ответ:

 { "time": "iso8601",   "temperature_2m": "°C",   "relative_humidity_2m": "%",   "wind_speed_10m": "km/h"}

Далее необходимо посмотреть, как часто бывало, что температура превышала 30 °С. Для этого можно запросить массив, где эта температура хранится за каждый час. Путь будет таким:

$.hourly.temperature_2m

Нам надо отсеять ненужные элементы через фильтры.

Фильтры — это ещё один механизм путей, который позволяет выполнять простую математику, сравнения и использовать методы. В фильтрах используется контекстная переменная @, в которой хранится элемент из основного пути. Синтаксис фильтра выглядит следующим образом:

<JSON Путь> ? (<булевское выражение>)

Например:

$ = {“myArray”:[1,2,3,4,5]}

$.myArray[*] ? (@ < 4)

В этом примере в переменную @ по очереди попадает каждый элемент массива. Если условие выполняется, то элемент попадает в выходную последовательность. Если нет — отсеивается.

Также в фильтре доступны методы, такие как floor(), abs(), like_rexes, starts with и другие.

В нашем примере мы получим перечисление всех температур, чей нижний порог больше 30 °С. Стоит заметить, что в режиме пути lax (который стоит по умолчанию) фильтр разворачивает свой аргумент, то есть массив temperature_2m. Аналогичного эффекта можно добиться, если написать $.hourly.temperature_2m[*]. То есть фильтр выполняется для всех элементов массива по очереди.

Казалось бы, мы узнали всё, чтобы получить нужные данные, но тут есть нюанс. JSON_QUERY возвращает либо массив, либо объект, а у нас перечисление. Нам необходимо обернуть его в массив через оператор WITH ARRAY WRAPPER. Полный пример:

SELECT JSON_QUERY(READ_FILE('json_files/weather.json'), '$.hourly.temperature_2m ? (@.floor() > 30)' WITH ARRAY WRAPPER) from rdb$database;

Ответ: [31, 31, 31.8, 32.8, 33.3, 33.5, 33.1, 31.9] .

JSON_EXISTS

Мир SQL таков, что схема не всегда соответствует ожиданиям, поэтому иногда приходится проверять её. Для этого существует функция JSON_EXISTS. Она проверяет наличие значения JSON по указанному пути:

SELECT JSON_EXISTS(READ_FILE('json_files/weather.json'),

'$.hourly.relative_humidity_2m[100]') from rdb$database;

Ответ: <true>.

В данном случае мы удостоверились, что в массиве есть сотый элемент. Это также можно проверить через фильтр с помощью метода size(), который возвращает размер массива:

SELECT JSON_EXISTS(READ_FILE('json_files/weather.json'),

'$.hourly.relative_humidity_2m.size() ? (@ > 100)') from rdb$database;

Сперва может показаться, что метод size можно перенести в фильтр:

'$.hourly.relative_humidity_2m ? (@.size() > 100)

Однако тут кроется ошибка, так как режим lax, разворачивает массив. Из‑за этого контекстная переменная @ будет содержать элементы массива, а не сам массив. Чтобы исправить это, необходимо использовать режим strict:

'strict $.hourly.relative_humidity_2m ? (@.size() > 100)

Также существует метод exists, который можно использовать внутри пути. Он полезен в режиме strict. В примере ниже после фильтра продолжается путь, то есть, если существует сотый элемент temperature_2m, то он будет запрошен:

'strict $.hourly_units ? (exists(@.temperature_2m[100])).temperature_2m'

IS JSON

Если мы хотим надёжное приложение, мы должны убедиться, что данные правильно сформированы, особенно при их получении из сторонних источников. Проверить JSON текст на валидность (корректность формата) можно через предикат IS JSON. Например, у нас есть список файлов в таблице FILES, и там надо проверить, какие из них являются JSON:

SELECT NAME FROM FILES WHERE CONTENT IS JSON;

Ответ: weather.json.

Функция представляет собой булевский предикат и проверяет, является ли аргумент JSON-ом в целом или одним из его видов в частности (скаляром, объектом, массивом).

Примеры:

SELECT '42' IS JSON FROM RDB$DATABASE;

Ответ: <true>.

SELECT '42' IS JSON OBJECT FROM RDB$DATABASE;

Ответ: <false>.

JSON_ARRAY

Мы рассмотрели функции запроса данных, теперь посмотрим функции генерации JSON, для этого нам потребуется таблица:

CREATE TABLE FEATURES(ID INT PRIMARY KEY, NAME VARCHAR(100) CHARACTER SET UTF8, STATUS VARCHAR(300) CHARACTER SET UTF8, version char(5)); INSERT INTO FEATURES VALUES(1, 'JSON Функции', 'Реализация в РБД5, Финальное тестирование', '5.0'); INSERT INTO FEATURES VALUES(2, 'Тип JSON', 'Обсуждение, проектирование', NULL); INSERT INTO FEATURES VALUES(3, 'Пакетные константы', 'Реализованы, написание Proposal в Firebird', '6.0'); INSERT INTO FEATURES VALUES(4, 'Tablespaces', 'Актуализация Proposal', '5.0'); INSERT INTO FEATURES VALUES(5, 'Составной тип', 'Реализация', '6.0'); COMMIT;   ID NAME                   STATUS                                       VERSION  ==== ====================== ============================================ =======     1 JSON Функции           Выпущено    5.0         2 Тип JSON               Обсуждение, проектирование                   <null>      3 Пакетные константы     Реализованы, написание Proposal в Firebird   6.0         4 Tablespaces            Готов PR                        5.0         5 Составной тип          Реализация                                   6.0  

Функция JSON_ARRAY работает с перечислением. Пример:

SELECT JSON_ARRAY('Агрегатный трейс',  'JSON Функции', 'Tablespaces') fFROM RDB$DATABASE;

Ответ: ["Агрегатный трейс", "JSON Функции", "Tablespaces"].

Есть и более интересный вариант использования — через подзапрос:

SELECT JSON_ARRAY(select name from features where version = '5.0') FROM RDB$DATABASE;

Ответ: ["JSON Функции", "Tablespaces"].

По умолчанию функция возвращает BLOB, так как выходной JSON обладает неизвестной размерностью. Но можно не беспокоиться о производительности, если использовать оператор returning. Так BLOB не создастся даже внутри движка, если массив будет достаточно маленьким:

SELECT JSON_ARRAY('Агрегатный трейс',  'JSON Функции', 'Tablespaces' returning varchar(50)) FROM RDB$DATABASE;

При возможности лучше всегда указывать выходной тип у всех функций JSON. Это вопрос не только оптимизации, но и контроль NoSQL.

JSON_OBJECT

Рассмотрим более интересный пример. Необходимо нашу таблицу превратить в набор объектов. Это можно сделать с помощью JSON_OBJECT:

SELECT JSON_OBJECT(name : trim(version)) from  features;

Получим 5 записей:

{"JSON Функции": "5.0"}

{"Тип JSON": null}

{"Пакетные константы": "6.0"}

{"Tablespaces": "5.0"}

{"Составной тип": "6.0"}

Видно, что у одного из объектов ключом сделался null. Если нам не нужны такие пары, то мы можем указать, чтобы такой ключ не создавался:

SELECT JSON_OBJECT(NAME: trim(version) absent on null) from  features;

У JSON_ARRAY предикат on null также есть, но, в отличие от JSON_OBJECT, по умолчанию он опускает NULL.

JSON_ARRAYAGG

Если JSON_ARRAY больше рассчитан на генерацию простых массивов из ограниченного набора элементов, то JSON_ARRAYAGG позволяет превратить в один массив целый столбец путём агрегирования. По сути, это почти то же самое, что и конструктор JSON_ARRAY с подзапросом:

SELECT JSON_ARRAYAGG(name) from features where version = '5.0';

Ответ: [“JSON Функции”, “Tablespaces”].

JSON_OBJECTAGG

Ещё одна агрегатная функция — JSON_OBJECTAGG. Она создаёт JSON объект из пары ключ‑значение путём агрегирования переданной пары элементов. Тут разница, если null отсеивается или убирается, гораздо актуальнее:

SELECT JSON_OBJECTAGG(name : trim(version) absent on null) from features;

{"JSON Функции": "5.0", "Пакетные константы": "6.0", "Tablespaces": "5.0", "Составной тип": "6.0"}

SELECT JSON_OBJECTAGG(name : trim(version) null on null) from features;

{"JSON Функции": "5.0", "Тип JSON": null, "Пакетные константы": "6.0", "Tablespaces": "5.0", "Составной тип": "6.0"}

Стандарт JSON подразумевает, что все ключи в объекте должны быть уникальными, однако это требование не всегда соблюдается. Настолько не всегда, что по стандарту проверка уникальности будет действовать, только если указать WITH UNIQUE KEYS. Это может быть полезно для раннего обнаружения ошибок, например, если в запросе перепутали ключ и значение местами: 

SELECT JSON_OBJECTAGG(trim(version) : name with unique keys) from  features where id > 2;

JSON_OBJECTAGG  =================  Statement failed, SQLSTATE = HY000  There is a non-unique key with the name '6.0' at indexes 0 and 2

Заключение

В этом материале мы рассмотрели основы работы с JSON в SQL и показали функции, разработанные для работы с JSON в СУБД РЕД База Данных 5, а в будущем и в СУБД Firebird.

Что касается стандарта ISO/IEC TR 19075-6, то в нашей СУБД будет реализовано больше функций и возможностей, чем мы рассказали в этом материале. И, так как у нас вышел новый релиз, то более подробно о новых возможностях можно прочитать на сайте СУБД РЕД База Данных.

Если вам интересно попробовать наш продукт и посмотреть, как в нём работают функции JSON, то вот ссылка. Пробуйте и делитесь своими впечатлениями. Также хотим сообщить, что сейчас ведётся работа над доработками JSON. В следующих материалах мы раскроем ещё несколько новых функций в нашей СУБД. Пишите свои пожелания и рекомендации о том, как вам хотелось бы работать с JSON в СУБД и каких функций вам не хватает. Мы ценим ваше мнение и будем рады учесть ваши предложения в будущих обновлениях. Спасибо за прочтение!