Парсинг данных JSON в SQL Server

Пересказ статьи Dave Mason. Parsing JSON Data In SQL Server

Майкрософт добавил поддержку данных JSON, начиная с SQL Server 2016. JSON представляет собой формат файлов открытого стандарта, состоящих из пар атрибут-значение и данных типа массивов. Он обычно используется для передачи объектов данных при асинхронном взаимодействии браузера с сервером. Но он также используется для хранения неструктурированных данных в файлах или базах данных NoSQL, таких как Microsoft Azure CosmosDB. Для большинства из нас поддержка JSON в SQL Server, вероятно, означает две вещи: мы можем конвертировать реляционные данные в JSON и обратно. Здесь я сосредоточусь на преобразовании JSON в реляционные данные.

Давайте начнем рассмотрение с примера простых данных. Вот слегка модифицированный пример из документации 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, мне встречались многоуровневые вложения, значительно более сложные, чем показанные здесь.

Добавить комментарий