SQL-Ex blog

Популярные языки разработки, включая JavaScript, имеют встроенную поддержку генерации и обработки JSON без сериализации, что обеспечивает им гибкость, сохраняя самоописательность без необходимости в схеме, которая требуется в XML.

Основы JSON

Текстовый контент JSON представляет собой последовательность токенов, содержащих кодовые точки, которые соответствуют грамматике значения JSON. Значения могут быть либо примитивными (строки, числа, булевы значения или NULL), либо сложными (объекты или массивы).

Объект JSON определяется как коллекция "нуля или более" именованных членов пар ключ-значение, которые записываются в парных скобках. Ключи и значения разделяются двоеточием, а объекты разделяются запятой. Ключ является строкой, а значение может быть любым примитивным или сложным типом данных. Массив JSON - это упорядоченный список нуля или более значений, разделенных запятыми и заключенными в квадратные скобки.

Поскольку JSON проектировался по возможности легковесным, он поддерживает только четыре примитивных типа данных - числа (плавающее двойной точности), строки (текст Unicode в двойных кавычках), true/false (булевы значения должны записываться в нижнем регистре) и null. Здесь нет специального типа "date" - даты представляются как строки. В JSON строки являются последовательностями, заключенными в кавычки, и все символы должны быть внутри, за исключением экранированных символов.


Базовая структура JSON

Получение данных SQL Server в формате JSON

Когда мы начинаем работать с JSON в SQL Server, то обычно сначала хотим получить в этом формате табличные данные. Microsoft впервые реализовала предложение FOR JSON в SQL Server 2017 - это предложение может естественно использоваться с предложением SELECT подобно FOR XML, которое мы используем для получения данных в формате XML.

FOR JSON предлагает выбор из двух методов:

• FOR JSON AUTO - вывод будет отформатирован в соответствии со структурой оператора SELECT.


• FOR JSON PATH - Вывод будет отформатирован в соответствии с определенной пользователем структурой, позволяя использовать вложенные объекты и свойства.

Какую бы модель вы не выбрали, SQL Server будет извлекать реляционные данные в операторах SELECT. Типы данных базы данных будут автоматически преобразованы к типам JSON и реализованы правила экранирования символов. Наконец, вывод будет отформатирован в соответствии с явно или неявно определенными правилами форматирования.

При FOR JSON AUTO форматом вывода управляет структура оператора SELECT. Таким образом, использование этой модели требует таблицы или представления базы данных.

USE AdventureWorks2019

GO

SELECT GETDATE() FOR JSON AUTO

Мы получаем следующее сообщение об ошибке:

Msg 13600, Level 16, State 1, Line 4

FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.

(FOR JSON AUTO требует, по крайней мере, одну таблицу для генерации объектов JSON. Используйте FOR JSON PATH или добавьте предложение FROM с именем таблицы.)

Теперь мы покажем как SQL Server автоматически генерирует данные JSON. Сначала это вывод в Management Studio, который затем отформатирован в текстовом редакторе:

USE AdventureWorks2019

GO

SELECT TOP(2) JobTitle, FirstName, LastName, City 

  FROM HumanResources.vEmployee 

   FOR JSON AUTO

[
{
"JobTitle": "Chief Executive Officer",
"FirstName": "Ken",
"LastName": "Sánchez",
"City": "Newport Hills"
},
{
"JobTitle": "Vice President of Engineering",
"FirstName": "Terri",
"LastName": "Duffy",
"City": "Renton"
}
]
Каждая строка в исходном результирующем наборе создается как плоская структура свойств. Если сравнить её со стандартным XML, то увидим намного меньше текста. Это связано с тем, что имена таблиц не выводятся в JSON.

Разница в размере становится заметной, когда вы начинаете использовать опцию ELEMENTS в XML вместо значения по умолчанию RAW. Чтобы продемонстрировать это, используем оператор SELECT, который сравнивает длину данных в байтах вывода XML и JSON:

USE AdventureWorks2019

GO

SELECT DATALENGTH( CAST 

                           (( SELECT * 

                                 FROM HumanResources.vEmployee FOR XML AUTO 

                           ) AS NVARCHAR(MAX))) AS XML_SIZE_RAW

,             DATALENGTH( CAST 

                          (( SELECT *

                           FROM HumanResources.vEmployee FOR XML AUTO, ELEMENTS 

                           )  AS NVARCHAR(MAX))) AS XML_SIZE_ELEMENTS

,             DATALENGTH( CAST (( SELECT * 

                                          FROM HumanResources.vEmployee FOR JSON AUTO 

                           ) AS NVARCHAR(MAX))) AS JSON_SIZE

Как можно увидеть из результатов запроса, размер элемента XML примерно на 65% больше размера JSON. С другой стороны, если выразить через атрибуты XML, JSON и XML приблизительно одинаковы.

Вывод использования FOR JSON AUTO представляет плоскую структуру с одноуровневыми свойствами. Если этого недостаточно, вам нужно использовать расширение FOR JSON PATH.

FOR JSON PATH позволяет иметь полный контроль над выводом JSON, создавая объекты-обертки и используя сложные свойства. Окончательный вариант представляется как массив объектов JSON. Расширение будет использовать имя алиаса/столбца для определения имени ключа в выводе. Если алиас содержит точки, будет создан вложенный объект.

Расширяя предыдущий пример, мы хотим представить столбцы FirstName и LastName как вложенные свойства нового столбца PersonName. Мы делаем это добавлением алиаса, чтобы создать столбцы, которые мы вкладываем, и используем точечный синтаксис, чтобы получить надлежащий вывод:

USE AdventureWorks2019

GO

SELECT TOP(2) JobTitle, City,

      FirstName AS 'PersonName.FirstName', LastName AS 'PersonName.LastName'

  FROM HumanResources.vEmployee 

   FOR JSON PATH

[

	{

		"JobTitle": "Chief Executive Officer",

		"City": "Newport Hills",

		"PersonName": {

			"FirstName": "Ken",

			"LastName": "Sánchez"

		}

	},

	{

		"JobTitle": "Vice President of Engineering",

		"City": "Renton",

		"PersonName": {

			"FirstName": "Terri",

			"LastName": "Duffy"

		}

	}

]

Если мы не изменим установки по умолчанию, NULL-значения не будут включены в результаты. Если ваш оператор соединяет несколько таблиц в одном запросе, на выходе будет плоский список, в котором FOR JSON PATH вкладывает каждый столбец в соответствии с заданным алиасом столбца.

Расширения FOR JSON PATH не требуют таблицы баз данных, как мы можем увидеть в следующем примере:

SELECT GETDATE() AS TadayDate FOR JSON PATH

Преобразование табличных данных с помощью JSON

Чтобы использовать JSON с реляционными данными или работать с таблицами, вам нужно сопоставить эти данные с JSON и импортировать их в таблицы базы данных.

Если вы используете SQL Server 2016 или более позднюю версию, вы можете использовать функцию OPENJSON. Это новая табличнозначная функция, добавленная в ядро базы данных. Она возвращает объект, который может использоваться как представление или таблица.

Она преобразует пары объекты/свойства JSON в комбинации строки/столбцы, принимая два входных параметра: Выражение (текст JSON на основе UNICODE) и Путь (выражение пути JSON, необязательный аргумент, используемый для указания фрагмента входного выражения).

Если уровень совместимости вашей базы данных не равен 130 или выше, вы получите следующее исключение при попытке использовать функцию OPENJSON:

Msg 208, Level 16, State 1, Line 78

Invalid object name ‘OPENJSON’.

(Неверное имя объекта ‘OPENJSON’.)

Если вы не укажете схему для возвращаемых результатов, будет создана таблица с тремя столбцами:

• Key (имя свойства или индекс элемента, тип столба - NOT NULL VARCHAR(4000));


• Value (значение свойства или индекса элемента, тип столбца - NOT NULL NVARCHAR(MAX));


• Type (тип данных JSON-значения, тип столбца - TINYINT).

OPENJSON вернет одну таблицу, свойства первого уровня будут строками, и каждая строка будет представлять одно свойство JSON или элемент массива. Для демонстрации мы будем использовать образец данных JSON в виде строки, чтобы посмотреть вывод:

DECLARE @JSON NVARCHAR(MAX) = N'

{

	"JobTitle": "Chief Executive Officer",

	"City": "Newport Hills",

	"PersonName": {

		"FirstName": "Ken",

		"LastName": "Sánchez"

	}

}';

SELECT * FROM OPENJSON(@JSON)

Если входные данные имеют некорректный формат, будет выводиться следующая ошибка:

DECLARE @JSON NVARCHAR(MAX) = N'

{

	"JobTitle": "Chief Executive Officer",

	"City": "Newport Hills",

	“SOMETHING_WRONG”,

	"PersonName": {

		"FirstName": "Ken",

		"LastName": "Sánchez"

	}

}';

SELECT * FROM OPENJSON(@JSON)

В этом примере мы возвращали только свойства первого уровня. Если мы хотим вернуть сложные значения документов JSON (объекты и массивы), нам потребуется указать аргумент пути. Посмотрите следующий пример, который возвращает элемент PersonName:

DECLARE @JSON NVARCHAR(MAX) = N'

{

	"JobTitle": "Chief Executive Officer",

	"City": "Newport Hills",

	"PersonName": {

		"FirstName": "Ken",

		"LastName": "Sánchez"

	}

}';

SELECT * FROM OPENJSON(@JSON, '$.PersonName')

Опять таки, если JSON неправильно отформатирован, ядро SQL Server выбросит исключение. Давайте сделаем намеренную ошибку в том же самом тексте JSON - опустим символы кавычки у FirstName:

DECLARE @JSON NVARCHAR(MAX) = N'

{

	"JobTitle": "Chief Executive Officer",

	"City": "Newport Hills",

	"PersonName": {

		FirstName: "Ken",

		"LastName": "Sánchez"

	}

}';

SELECT * FROM OPENJSON(@JSON, '$.PersonName')

Частой ошибкой является загрузка данных в формате значений-разделенных-запятыми (csv), и тут нам может помочь использование OPENJSON:

USE AdventureWorks2019

DECLARE @NationalIDNumberIDs AS VARCHAR(100) = '295847284,245797967';

SELECT [BusinessEntityID]

      ,[NationalIDNumber]

      ,[LoginID]

      ,[OrganizationNode]

      ,[OrganizationLevel]

  FROM [HumanResources].[Employee] Emp

    INNER JOIN ( SELECT value FROM OPENJSON('[' + @NationalIDNumberIDs + ']') ) 

                   AS JSONTbl ON Emp.NationalIDNumber = JSONTbl.value;

Имеется более легкий способ работать с кодом JSON - современные технологии предоставляют огромное число инструментов. Например, dbForge SQL Complete позволяет добавить этот плагин в SSMS, а затем просматривать код JSON непосредственно в прилагаемом окне Data Viewer.