В СУБД Microsoft SQL Server, начиная с 2016 версии, существует возможность встроенной работы с форматом данных JSON, и сегодня мы поговорим о том, что это за формат, где и для чего он используется и, конечно же, подробно рассмотрим функционал SQL сервера для работы с этим форматом.
Как я уже сказал, функционал для работы с JSON в SQL сервере появился в 2016 версии, обзор всех нововведений этой версии можете посмотреть в материале — Microsoft SQL Server 2016 – обзор новых возможностей. Сегодня, как Вы уже поняли, мы остановимся на одном нововведении SQL Server 2016 — это поддержка JSON.
Что такое JSON?
JSON (Object Notation JavaScript) – это простой, удобный для чтения как человеком, так и компьютером текстовый формат обмена данными. Он представляет собой текст в виде пар параметр-значение. JSON позволяет передавать не только сами данные, но и их структуру, что напоминает формат данных XML.
JSON на текущий момент очень популярный формат обмена данными, так как он простой и понятный, в связи с этим он широко используется для обмена данными в современных веб — и мобильных приложениях. Встроенная поддержка JSON есть во многих языках программирования и СУБД, например, в языке программирования PHP такая поддержка существует уже достаточно давно, функционал PHP для работы с JSON мы с Вами уже рассматривали в материале PHP — примеры работы с форматом JSON.
В качестве типа значения параметра в формате JSON могут выступать:
- Объект (значение в фигурных скобках {});
- Массив (значение в квадратных скобках []);
- Строка (значение в кавычках «Пример»);
- Число;
- Логический тип (true, false);
- NULL.
Благодаря тому, что в JSON есть возможность выделять объекты и массивы мы можем с помощью простой строки JSON передать целую структуру. Ниже представлен пример строки JSON, т.е. формата JSON:
{ "string": "Пример строки", "numeric": 100, "boolean": true, "NULL": NULL, "array": [ { "ArrElement1Field_1": "Первое свойство первого элемента", "ArrElement1Field_2": 100, "ArrElement1Field_3": true }, { "ArrElement2Field_1": "Первое свойство второго элемента", "ArrElement2Field_2": 200, "ArrElement2Field_3": false } ], "object": { "name": "Пример свойства объекта" } }
Для более подробного изучения формата JSON можете посетить на официальный сайт JSON.
Функционал MS SQL Server для работы с JSON
Ранее, до 2016 версии SQL сервера, разработчикам баз данных в случае необходимости обработки данных в формате JSON, приходилось изобретать собственные инструменты (функции, процедуры), в SQL Server 2016 компания Microsoft решила облегчить жизнь программистам и добавила встроенные инструменты для работы с JSON, а какие мы сейчас и узнаем.
И для начала хотелось бы сказать, что в SQL сервере для хранения данных в формате JSON не предусмотрен отдельный тип данных (как например, для XML), для хранения данных JSON необходимо использовать обычный тестовый тип VARCHAR.
Как и в случае с XML мы можем преобразовывать табличные данные в формат JSON и, наоборот, при получении данных в формате JSON преобразовывать их в табличные или просто извлекать какие-то скалярные значения.
Итак, для работы с форматом JSON в SQL сервере существуют следующие функции и инструкции:
- ISJSON – функция для проверки строки на наличие данных JSON;
- JSON_VALUE — это функция для извлечения скалярного значения из строки JSON;
- JSON_QUERY – это функция для извлечения объекта или массива из строки JSON;
- JSON_MODIFY – функция для изменения данных в строке JSON;
- OPENJSON – табличная функция для преобразования данных JSON в табличный вид;
- FOR JSON – инструкция для преобразования данных SQL сервера в формат JSON.
А теперь давайте посмотрим примеры использования этих функций в языке T-SQL.
Полезные материалы по теме:
Исходные данные для примеров
Для того чтобы посмотреть на работу представленных выше функций, давайте создадим тестовую базу данных TestBase, а в ней тестовую таблицу TestTable, в которой будет три столбца: идентификатор, столбец для хранения JSON данных и столбец, характеризующий эти данные.
Затем добавим в тестовую таблицу три строки, первая с JSON данными, вторая будет содержать просто текст, а третья ничего не будет содержать (NULL).
Для примера давайте представим, что строка с JSON данными будет содержать некий заказ и хранить информацию об этом заказе в структурированном виде, а именно: несколько общих свойств заказа, товары, входящие в этот заказ, с указанием их цены и количества, и список телефонов ответственных лиц, т.е. менеджеров по этому заказу.
В данной структуре я попытался задействовать все те возможные типы значений, которые можно использовать для формирования JSON строки. Другими словами, в представленной ниже строке JSON будут значения и в виде простого текста (Title), чисел (Id), значения в виде вложенного объекта (Properties), в виде массива с объектами (Goods), а также массива с простыми значениями (ManagerPhones).
Примечание! В качестве SQL сервера у меня выступает редакция Microsoft SQL Server 2019 Express.
Скрипт создания тестовых объектов на SQL сервере
CREATE DATABASE TestBase GO --Переходим в созданную базу USE TestBase GO --Создаем тестовую таблицу CREATE TABLE TestTable( ID INT IDENTITY(1,1) NOT NULL, StrJSON VARCHAR(MAX) NULL, Header VARCHAR(100) NULL, CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ID ASC) ) GO --Заполняем таблицу тестовыми данными --Переменная для JSON строки DECLARE @JSON VARCHAR(MAX) --Записываем в переменную JSON данные SET @JSON = '{ "Id": 12345, "Title": "Заказ 1", "Buyer": "Иванов Иван", "Goods":[ { "Name": "Системный блок", "Price": 500, "Quantity": 1 }, { "Name": "Монитор", "Price": 400, "Quantity": 1 }, { "Name": "Клавиатура", "Price": 100, "Quantity": 1 } ], "Properties":{ "Created": "21.10.2016", "Status": "Completed", "DeliveryType": "Самовывоз" }, "ManagerPhones":[ "123-145-789", "987-654-321" ] } ' --Вставляем данные INSERT INTO TestTable (StrJSON, Header) SELECT @JSON, 'Срока JSON' UNION ALL SELECT 'Просто текст', 'Просто строка' UNION ALL SELECT NULL, 'Пустая строка' GO --Посмотрим результат SELECT * FROM TestTable
ISJSON
Данная функция, как я уже сказал, нужна нам для того, чтобы определять является ли строка данными JSON. Принимает она один параметр, понятное дело строку для проверки, и возвращает 1, если строка представляет собой JSON данные, 0, если нет и NULL, если мы передали NULL.
Давайте напишем простой запрос, который нам покажет, какая из строк является данными JSON, а какая нет.
SELECT ID, ISJSON (StrJSON) AS Status, Header, StrJSON FROM TestTable
Как видим, функция отработала правильно.
JSON_VALUE
Для того чтобы получить скалярное значение из JSON данных в SQL сервере существует функция JSON_VALUE. У нее два параметра: первый это непосредственно сами данные JSON, например, переменная или столбец, содержащий JSON строку и второй это путь до значения, которое нужно извлечь. Для примера давайте извлечем из наших тестовых JSON данных наименование второго товара, его стоимость, дату создания заказа и первый телефон менеджера, для этого пишем следующий запрос.
SELECT JSON_VALUE(StrJSON, '$.Goods[1].Name') AS [Товар], JSON_VALUE(StrJSON, '$.Goods[1].Price') AS [Цена], JSON_VALUE(StrJSON, '$.Properties.Created') AS [Дата создания заказа], JSON_VALUE(StrJSON, '$.ManagerPhones[0]') AS [Телефон менеджера] FROM TestTable WHERE ISJSON (StrJSON) = 1
Для того чтобы работать только с JSON данными я отфильтровал строки нашей таблицы, указав в предложение WHERE функцию ISJSON.
JSON_QUERY
С помощью данной функции можно извлекать из JSON строки ее часть, т.е. мы можем извлечь объект или массив. Функция принимает два параметра: первый это также данные JSON, а второй это путь до объекта или массива, который необходимо извлечь. Возвращает данная функция также JSON данные.
SELECT JSON_QUERY(StrJSON, '$.Goods') AS [Все товары в заказе], StrJSON AS [Заказ] FROM TestTable WHERE ISJSON (StrJSON) = 1
JSON_MODIFY
Для изменения данных JSON в SQL сервере предусмотрена функция JSON_MODIFY. Для примера давайте в наших тестовых данных изменим цену товара (например, монитора), а также добавим дополнительный номер телефона менеджера.
--Изменение цены UPDATE TestTable SET StrJSON = JSON_MODIFY(StrJSON, '$.Goods[1].Price', '450') WHERE ISJSON (StrJSON) = 1 --Добавление номера телефона UPDATE TestTable SET StrJSON = JSON_MODIFY(StrJSON, 'append $.ManagerPhones', '555-666-777') WHERE ISJSON (StrJSON) = 1 --Проверяем SELECT JSON_VALUE(StrJSON, '$.Goods[1].Price') AS [Новая цена монитора], JSON_QUERY(StrJSON, '$.ManagerPhones') AS [Телефонные номера] FROM TestTable WHERE ISJSON (StrJSON) = 1
Как видим, все получилось.
OPENJSON
Этой табличной функцией можно преобразовать JSON данные в табличный вид. Принимает она два параметра: первый это как всегда JSON данные, а второй, необязательный параметр, путь к объекту или массиву. Для указания конкретной спецификации табличного вида можно использовать инструкцию WITH. Например, давайте получим в табличном виде список товаров в заказе. Сначала попробуем это сделать без инструкции WITH, а потом с ней и посмотрим на результаты.
--Переменная для JSON данных DECLARE @JSON VARCHAR(MAX) --Получаем JSON данные SELECT @JSON = (SELECT StrJSON FROM TestTable WHERE ISJSON (StrJSON) = 1) --Преобразовываем их в табличный вид (без WITH) SELECT * FROM OPENJSON(@JSON, '$.Goods') --Преобразовываем их в табличный вид (с WITH) SELECT * FROM OPENJSON(@JSON, '$.Goods') WITH( Name VARCHAR(200) '$.Name', Price MONEY '$.Price', Quantity INT '$.Quantity' )
FOR JSON
Инструкция FOR JSON используется в SQL сервере для преобразования табличных данных в JSON строку. У данной инструкции два режима AUTO и PATH. В случае с AUTO SQL сервер автоматически распознает инструкцию SELECT и преобразует данные в строку JSON. В случае с PATH структуру JSON документа Вы можете задать сами. Давайте сделаем простую выборку из нашей таблицы, и полученные данные преобразуем в JSON документ.
--Переменная для JSON данных DECLARE @JSON VARCHAR(MAX) --Преобразовываем данные в JSON строку SELECT @JSON = (SELECT Id, Header FROM TestTable FOR JSON AUTO) SELECT @JSON AS [Строка JSON]
Более подробную информацию о работе с JSON в MS SQL Server можете найти в официальной технической документации, а у меня на этом все, пока!