Работа с JSON в Microsoft SQL Server

В СУБД Microsoft SQL Server, начиная с 2016 версии, существует возможность встроенной работы с форматом данных JSON, и сегодня мы поговорим о том, что это за формат, где и для чего он используется и, конечно же, подробно рассмотрим функционал SQL сервера для работы с этим форматом.

Как я уже сказал, функционал для работы с JSON в SQL сервере появился в 2016 версии, обзор всех нововведений этой версии можете посмотреть в материале — Microsoft SQL Server 2016 – обзор новых возможностей. Сегодня, как Вы уже поняли, мы остановимся на одном нововведении SQL Server 2016 — это поддержка JSON.

Скриншот 1

Что такое 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 2016 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

Скриншот 2

ISJSON

Данная функция, как я уже сказал, нужна нам для того, чтобы определять является ли строка данными JSON. Принимает она один параметр, понятное дело строку для проверки, и возвращает 1, если строка представляет собой JSON данные, 0, если нет и NULL, если мы передали NULL.

Давайте напишем простой запрос, который нам покажет, какая из строк является данными JSON, а какая нет.

   
   SELECT ID, ISJSON (StrJSON) AS Status,
           Header, StrJSON
   FROM TestTable

Скриншот 3

Как видим, функция отработала правильно.

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

Скриншот 4

Для того чтобы работать только с JSON данными я отфильтровал строки нашей таблицы, указав в предложение WHERE функцию ISJSON.

JSON_QUERY

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

   
   SELECT JSON_QUERY(StrJSON, '$.Goods') AS [Все товары в заказе],
           StrJSON AS [Заказ]
   FROM TestTable
   WHERE ISJSON (StrJSON) = 1

Скриншот 5

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

Скриншот 6

Как видим, все получилось.

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'
                         )

Скриншот 7

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]

Скриншот 8

Более подробную информацию о работе с JSON в MS SQL Server можете найти в официальной технической документации, а у меня на этом все, пока!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.