SQL, Анализ данных

Работа с JSON в MS SQL Server 2016

Время прочтения: 3 мин.

Аудитору всё чаще приходится сталкиваться с данными в формате JSON хранящиеся в базе данных. JSON — это распространённый формат текстовых данных, который используется для хранения неструктурированных данных в не реляционных базах данных NoSQL.

Рассмотрим какие возможности предоставляет нам SQL Server 2016 для работы с данным форматом. В MS SQL нет отдельного типа для хранения JSON, хранится в полях типов varchar или nvarchar.

Работу с данным форматом разберем примере таблицы с данными о продаже товаров.  Для начала создадим таблицу:

CREATE TABLE [dbo].[Sale](
	[Id] [int] NOT NULL,
	[Client_id] [int] NULL,
	[Cheque] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

В поле Cheque будут заносится проданные товары в формате JSON. Добавим несколько строк в таблицу:

INSERT INTO [dbo].[Sale] VALUES (1,1,'{"Products":[{"Name":"Яйца", "Count":1, "Price":60, "Summa":60},{"Name":"Молоко", "Count":2, "Price":55, "Summa":110}]}')
INSERT INTO [dbo].[Sale] VALUES (2,5,'{"Products":[{"Name":"Хлеб", "Count":1, "Price":35, "Summa":35},{"Name":"Печенье", "Count":1, "Price":75, "Summa":75}]}')
INSERT INTO [dbo].[Sale] VALUES (3,8,'{"Products":[{"Name":"Шоколад", "Count":2, "Price":200, "Summa":100},{"Name":"Кофе", "Count":1, "Price":550, "Summa":550}]}')
GO

Для извлечения данных в Transact-SQL есть 2 основные функции: JSON_VALUE, JSON_QUERY.

JSON_VALUE позволяет извлечь значение по определенному пути. В следующем примере получаем наименования и цены товаров, указанные первой строкой в чеках.

SELECT [id]
      ,[Client_id]
	  ,JSON_VALUE([cheque],'$.Products[0].Name') Product
	  ,JSON_VALUE([cheque],'$.Products[0].Price') Price
FROM [Edu].[dbo].[Sale]

JSON_QUERY позволяет извлечь массив строки. В следующем примере получаем массив данных, указанный второй строкой в чеках.

SELECT [id]
      ,[Client_id]
	  ,JSON_QUERY([cheque],'$.Products[1]') Product
  FROM [Edu].[dbo].[Sale]

И подробнее рассмотрим из чего состоят выражения пути JSON для создания ссылок на объекты JSON используемые в функциях, описанных выше. Путь может включать в себя ключи, например $.P1 или $.P1.P2. Если имя ключа содержит пробел или доллар, то его имя должно быть заключено в кавычки. И элементы массива, например $.ARR[0] (массивы отсчитываются от нуля).

В начале выражения пути можно объявить режим пути lax или strict. Если указано ключевое слово lax, если выражение пути содержит ошибку, то функция возвращает пустые значения. Если указано ключевое слово strict, если выражение пути содержит ошибку, то функция возвращает ошибку. Если ничего не указано, используется режим lax.

OPENJSON позволяет преобразовать данные JSON в таблицу. В следующем примере выведем все покупки, совершенные покупателями

select A.id, A.Client_id, B.*
  FROM [Edu].[dbo].[Sale] A
  cross apply OPENJSON(A.[cheque],'$.Products')
 WITH (
	Name NVARCHAR(200) N'$.Name',
	Count NVARCHAR(200) N'$.Count',
	Price NVARCHAR(200) N'$.Price',
	Summa NVARCHAR(200) N'$.Summa'
	) B

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

Советуем почитать