SQL-Ex blog

Давайте начнем рассмотрение с примера простых данных. Вот слегка модифицированный пример из документации Microsoft, который демонстрирует две структуры данных JSON: пары ключ/значение и массивы (упорядоченный список значений).

{  

   "NullValue":null,  

   "StringValue":"John",  

   "IntValue":45,  

   "DecimalValue":6.7,

   "TrueValue":true,  

   "FalseValue":false,  

   "ArrayValue":["a","r","r","a","y"],  

   "ObjectValue":{"obj":"ect"}  

}

Объекты заключаются в фигурные скобки. Весь блок данных заключается в фигурные скобки - это объект JSON. Он состоит из пар ключ/значение, влючающих "StringValue":"John", "IntValue":45, и "FalseValue":false. Последний ключ "ObjectValue" имеет значение, которое само является объектом - очень простым объектом, состоящим из одной пары ключ/значение: {"obj":"ect"}.

Значения массива заключаются в квадратные скобки внутри данных JSON. Ключ "ArrayValue" имеет массив в качестве значения. Он состоит из пяти односимвольных строк: ["a","r","r","a","y"]. Мы скоро доберемся до типов JSON, сейчас же быстро замечу, что значения массива не обязаны быть одного и того же типа.

OPENJSON

SQL Server предоставляет функцию OPENJSON для "чтения" JSON-данных. Это табличнозначная функция, которая выполняет разбор текста JSON (вход) и возвращает объекты и свойства в виде строк и столбцов. Вот пример данных из примера выше, разобранный функцией OPENSJON:

DECLARE @json NVARCHAR(4000) = N'

{  

   "NullValue":null,  

   "StringValue":"John",  

   "IntValue":45,  

   "DecimalValue":6.7,

   "TrueValue":true,  

   "FalseValue":false,  

   "ArrayValue":["a","r","r","a","y"],  

   "ObjectValue":{"obj":"ect"}  

}'

SELECT *

FROM OPENJSON(@json) j; 

Ключи и значения JSON возвращаются в виде столбцов в результирующем наборе, наряду с типом JSON. Документация JSON указывает, что имеется только шесть типов JSON, что значительно меньше количества типов данных SQL. Например, как 45, так и 6.7, имеют один и тот же тип JSON (тип 2), в то время как в SQL Server имеются типы данных TINYINT, SMALLINT, INT, BIGINT и NUMERIC/DECIMAL.

OPENROWSET

Чтение JSON данных из жестко закодированной переменной, вероятно, не вполне реальный вариант использования для большинства из нас. Чтобы прочитать данные из файла *.json, мы можем привлечь нашего старого друга - функцию OPENROWSET. Здесь мы прочитаем данные JSON из файла Sample.json, сохраним содержимое файла в переменной @json и разберем с помощью OPENJSON:

DECLARE @json AS NVARCHAR(MAX);

SELECT @json = r.BulkColumn

FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r

SELECT * FROM OPENJSON(@json);

Оператор CROSS APPLY в следующем примере дает тот же результат на выходе, что и предыдущий пример. Здесь меньше кода, и он более прозрачен:

SELECT j.*

FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r

CROSS APPLY OPENJSON(BulkColumn) AS j;

Еще раз тот же вывод. Вы заметили, как отличаются значения в последних двух строках? Функция OPENJSON возвращает значения массива и значения объекта как текст JSON.

JSON_VALUE

Как получить доступ к значениям массива и вложенным объектам? Один из способов использовать функцию JSON_VALUE, которая извлекает скалярное значение из строки JSON. Она имеет два аргумента: выражение текста JSON и путь, задающий свойство для извлечения. Запрос ниже извлекает каждый элемент из массива ArrayValue и значение ключа obj в объекте ObjectValue.

--Используйте функцию JSON_VALUE для извлечения скалярных значений.

--Отметим, что выражения пути JSON начинаются с символа "$".

SELECT j.[key], j.value, 

 JSON_VALUE(BulkColumn, '$.ArrayValue[0]') AS Element1,

 JSON_VALUE(BulkColumn, '$.ArrayValue[1]') AS Element2,

 JSON_VALUE(BulkColumn, '$.ArrayValue[2]') AS Element3,

 JSON_VALUE(BulkColumn, '$.ArrayValue[3]') AS Element4,

 JSON_VALUE(BulkColumn, '$.ArrayValue[4]') AS Element5,

 JSON_VALUE(BulkColumn, '$.ObjectValue.obj') AS ObjValue

FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r

CROSS APPLY OPENJSON(BulkColumn) AS j;

Строка пути в этом контексте состоит из символа доллара ($), который представляет контекстный элемент (всю строку объекта JSON). За ним следует оператор ".", указывающий на члена объекта. $.ArrayValue есть путь к ключу с именем "ArrayValue", а $.ObjectValue - путь к ключу с именем "ObjectValue". Оба являются членами объекта текста JSON. Для массивов ссылка на элемент осуществляется посредством индексного номера в квадратных скобках. Индексные номера элементов массива начинаются с нуля. Поэтому первый элемент имеет индекс 0, второй - 1 и т.д. Для объекта имеется только одна пара ключ/значение. Для ссылки на значение используется второй оператор "." и имя ключа ("obj").

Что произойдет, если мы зададим путь по имени ключа или массива элементов, которого не существует? Давайте проверим:

--Не существует 6-го элемента с индексом 5.

--"Obj" записано с ошибкой в регистре символов.

SELECT j.[key], j.value, 

 JSON_VALUE(BulkColumn, '$.ArrayValue[5]') AS Element6,

 JSON_VALUE(BulkColumn, '$.ObjectValue.Obj') AS ObjValue

FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r

CROSS APPLY OPENJSON(BulkColumn) AS j;

Возвращается NULL, когда мы пытаемся получить доступ к несуществующему 6-му элементу и значению ключа, который не существует (мы написали его с ошибкой, сделав заглавной первую букву). Следует знать, что имеется два режима пути: строгий и нестрогий. Нестрогий принимается по умолчанию. В этом режиме функция вернет NULL, если выражение пути содержит ошибку, как в приведенном выше примере. В строгом режиме функция выдает ошибку, в случае ошибочного выражения пути:

--Не существует 6-го элемента с индексом 5.

--"Obj" записано с ошибкой в регистре символов.

SELECT j.[key], j.value, 

 JSON_VALUE(BulkColumn, 'strict $.ArrayValue[5]') AS Element6,

 JSON_VALUE(BulkColumn, 'strict $.ObjectValue.Obj') AS ObjValue

FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r

CROSS APPLY OPENJSON(BulkColumn) AS j;



Msg 13608, Level 16, State 2, Line 51

Property cannot be found on the specified JSON path.

Поскольку имеется один вложенный объект ("ObjectValue"), данные JSON в этих примерах достаточно просты. Недолго работая с данными JSON, мне встречались многоуровневые вложения, значительно более сложные, чем показанные здесь.