В СУБД 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 можете найти в официальной технической документации, а у меня на этом все, пока!
