SQL Server 2017 JSON19.12.2017 00:33

4a62mja5edpjipft3eatedefpc4.png

Когда много лет подряд Microsoft лихорадит из одной крайности в другую, то понемногу начинаешь привыкать к этому и все новое ждешь с неким скепсисом. Со временем это чувство становится только сильнее и подсознательно ничего хорошего уже не ожидаешь.

Но иногда все получается в точности да наоборот. Microsoft вываливает из коробки идеально работающий функционал, который рвет все устоявшиеся жизненные стереотипы. Ты ждешь от новой функционала очередных граблей, но, с каждой минутой, все больше понимаешь, что именно этого тебе не хватало все эти годы.

Такое пафосное вступление имеет определенные на то основания, поскольку долгое время на Microsoft Connect поддержка работы с JSON на SQL Server была одной из самых востребованных фич. Шли годы и неожиданно данный функционал реализовали вместе с релизом SQL Server 2016. Забегая вперед скажу, что вышло очень даже хорошо, но Microsoft не остановилась на этом и в SQL Server 2017 существенно улучшили производительность и без того быстрого JSON парсера.

Содержание:


1. Datatypes
2. Storage
3. Compress/Decompress
4. Compression
5. ColumnStore
6. Create JSON
7. Check JSON
8. JsonValue
9. OpenJson
10. String Split
11. Lax & strict
12. Modify
13. Convert implicit
14. Indexes
15. Parser performance
Видео

1. Datatypes


Поддержка JSON на SQL Server изначально доступна для всех редакций. При этом отдельного типа данных, как в случае с XML, Microsoft не предусмотрела. Данные в JSON на SQL Server хранятся как обычный текст: в Unicode (NVARCHAR / NCHAR) либо ANSI (VARCHAR / CHAR) формате.

DECLARE @JSON_ANSI VARCHAR(MAX) =      '[{"Nąme":"Lenōvo モデ460"}]'
      , @JSON_Unicode NVARCHAR(MAX) = N'[{"Nąme":"Lenōvo モデ460"}]'

SELECT DATALENGTH(@JSON_ANSI), @JSON_ANSI
UNION ALL
SELECT DATALENGTH(@JSON_Unicode), @JSON_Unicode


Главное, о чем нужно помнить: сколько места занимает тот или иной тип данных (2 байта на символ, если храним данные как Unicode, или 1 байт для ANSI строк). Также не забываем перед Unicode константами ставить »N». В противном случае можно нарваться на кучу веселых ситуаций: 

--- ----------------------------
25  [{"Name":"Lenovo ??460"}]
50  [{"Nąme":"Lenōvo モデ460"}]


Вроде все просто, но нет. Дальше мы увидим, что выбранный тип данных влияет не только на размер, но и на скорость парсинга.

Кроме того, Microsoft настоятельно рекомендует не использовать deprecated типы данных — NTEXT / TEXT. Для тех, кто в силу привычки их до сих пор использует, мы сделаем небольшой следственный эксперимент: 

DROP TABLE IF EXISTS #varchar
DROP TABLE IF EXISTS #nvarchar
DROP TABLE IF EXISTS #ntext
GO

CREATE TABLE #varchar  (x VARCHAR(MAX))
CREATE TABLE #nvarchar (x NVARCHAR(MAX))
CREATE TABLE #ntext    (x NTEXT)
GO

DECLARE @json NVARCHAR(MAX) =
    N'[{"Manufacturer":"Lenovo","Model":"ThinkPad E460","Availability":1}]'

SET STATISTICS IO, TIME ON

INSERT INTO #varchar
SELECT TOP(50000) @json
FROM [master].dbo.spt_values s1
CROSS JOIN [master].dbo.spt_values s2
OPTION(MAXDOP 1)

INSERT INTO #nvarchar
SELECT TOP(50000) @json
FROM [master].dbo.spt_values s1
CROSS JOIN [master].dbo.spt_values s2
OPTION(MAXDOP 1)

INSERT INTO #ntext
SELECT TOP(50000) @json
FROM [master].dbo.spt_values s1
CROSS JOIN [master].dbo.spt_values s2
OPTION(MAXDOP 1)

SET STATISTICS IO, TIME OFF


Скорость вставки в последнем случае будет существенно различаться: 

varchar:  CPU time = 32 ms,  elapsed time = 28 ms
nvarchar: CPU time = 31 ms,  elapsed time = 30 ms
ntext:    CPU time = 172 ms, elapsed time = 190 ms


Кроме того, нужно помнить, что NTEXT / TEXT всегда хранятся на LOB страницах: 

SELECT obj_name = OBJECT_NAME(p.[object_id])
     , a.[type_desc]
     , a.total_pages
     , total_mb = a.total_pages * 8 / 1024.
FROM sys.allocation_units a
JOIN sys.partitions p ON p.[partition_id] = a.container_id
WHERE p.[object_id] IN (
        OBJECT_ID('#nvarchar'),
        OBJECT_ID('#ntext'),
        OBJECT_ID('#varchar')
    )


obj_name      type_desc      total_pages  total_mb
------------- -------------- ------------ -----------
varchar       IN_ROW_DATA    516          4.031250
varchar       LOB_DATA       0            0.000000
nvarchar      IN_ROW_DATA    932          7.281250
nvarchar      LOB_DATA       0            0.000000
ntext         IN_ROW_DATA    188          1.468750
ntext         LOB_DATA       1668         13.031250


Для справки, начиная с SQL Server 2005 для типов с переменной длиной поменяли правило «На каких страницах хранить данные». В общем случае, если размер превышает 8060 байт, то данные помещаются на LOB страницу, иначе хранятся в IN_ROW. Понятно, что в таком случае SQL Server оптимизирует хранение данных на страницах.

И последний довод не использовать NTEXT / TEXT — это тот факт, что все JSON функции с deprecated типами данных банально не дружат: 

SELECT TOP(1) 1
FROM #ntext
WHERE ISJSON(x) = 1


Msg 8116, Level 16, State 1, Line 63
Argument data type ntext is invalid for argument 1 of isjson function.


2. Storage


Теперь посмотрим, насколько выгодно хранение JSON как NVARCHAR / VARCHAR по сравнению с аналогичными данными, представленными в виде XML. Кроме того, попробуем XML хранить в нативном формате, а также представить в виде строки: 

DECLARE @XML_Unicode NVARCHAR(MAX) = N'

  
    
      i7-6500U
      16
      256
    
    
      i5-6200U
      8
      1000
    
    
      i5-6200U
      4
      500
    
  
'

DECLARE @JSON_Unicode NVARCHAR(MAX) = N'
[
  {
    "Manufacturer": {
      "Name": "Lenovo",
      "Product": {
        "Name": "ThinkPad E460",
        "Model": [
          {
            "Name": "20ETS03100",
            "CPU": "Intel Core i7-6500U",
            "Memory": 16,
            "SSD": "256"
          },
          {
            "Name": "20ETS02W00",
            "CPU": "Intel Core i5-6200U",
            "Memory": 8,
            "HDD": "1000"
          },
          {
            "Name": "20ETS02V00",
            "CPU": "Intel Core i5-6200U",
            "Memory": 4,
            "HDD": "500"
          }
        ]
      }
    }
  }
]'

DECLARE @XML_Unicode_D NVARCHAR(MAX) = N'i7-6500U16256i5-6200U81000i5-6200U4500'
      , @JSON_Unicode_D NVARCHAR(MAX) = N'[{"Manufacturer":{"Name":"Lenovo","Product":{"Name":"ThinkPad E460","Model":[{"Name":"20ETS03100","CPU":"Intel Core i7-6500U","Memory":16,"SSD":"256"},{"Name":"20ETS02W00","CPU":"Intel Core i5-6200U","Memory":8,"HDD":"1000"},{"Name":"20ETS02V00","CPU":"Intel Core i5-6200U","Memory":4,"HDD":"500"}]}}}]'

DECLARE @XML XML = @XML_Unicode
      , @XML_ANSI VARCHAR(MAX) = @XML_Unicode
      , @XML_D XML = @XML_Unicode_D
      , @XML_ANSI_D VARCHAR(MAX) = @XML_Unicode_D
      , @JSON_ANSI VARCHAR(MAX) = @JSON_Unicode
      , @JSON_ANSI_D VARCHAR(MAX) = @JSON_Unicode_D

SELECT *
FROM (
    VALUES ('XML Unicode', DATALENGTH(@XML_Unicode), DATALENGTH(@XML_Unicode_D))
         , ('XML ANSI', DATALENGTH(@XML_ANSI), DATALENGTH(@XML_ANSI_D))
         , ('XML', DATALENGTH(@XML), DATALENGTH(@XML_D))
         , ('JSON Unicode', DATALENGTH(@JSON_Unicode), DATALENGTH(@JSON_Unicode_D))
         , ('JSON ANSI', DATALENGTH(@JSON_ANSI), DATALENGTH(@JSON_ANSI_D))
) t(DataType, Delimeters, NoDelimeters)


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

DataType     Delimeters  NoDelimeters
------------ ----------- --------------
XML Unicode  914         674
XML ANSI     457         337
XML          398         398
JSON Unicode 1274        604
JSON ANSI    637         302


Может показаться, что самый выгодный вариант — нативный XML. Это отчасти правда, но есть нюансы. XML всегда хранится как Unicode. Кроме того, за счет того, что SQL Server использует бинарный формат хранения этих данных — все сжимается в некий стандартизированный словарь с указателями. Именно поэтому форматирование внутри XML не влияет на конечный размер данных.

Со строками все иначе, поэтому я не стал бы рекомендовать хранить форматированный JSON. Лучший вариант — вырезать все лишние символы при сохранении и форматировать данные по запросу уже на клиенте.

Если хочется еще сильнее сократить размер JSON данных, то в нашем распоряжении несколько возможностей.

3. Compress/Decompress


В SQL Server 2016 реализовали новые функции COMPRESS / DECOMPRESS, которые добавляют поддержку GZIP сжатия: 

SELECT *
FROM (
    VALUES ('XML Unicode', DATALENGTH(COMPRESS(@XML_Unicode)),
                           DATALENGTH(COMPRESS(@XML_Unicode_D)))
         , ('XML ANSI', DATALENGTH(COMPRESS(@XML_ANSI)),
                        DATALENGTH(COMPRESS(@XML_ANSI_D)))
         , ('JSON Unicode', DATALENGTH(COMPRESS(@JSON_Unicode)),
                            DATALENGTH(COMPRESS(@JSON_Unicode_D)))
         , ('JSON ANSI', DATALENGTH(COMPRESS(@JSON_ANSI)),
                         DATALENGTH(COMPRESS(@JSON_ANSI_D)))
) t(DataType, CompressDelimeters, CompressNoDelimeters)


Результаты для предыдущего примера: 

DataType     CompressDelimeters   CompressNoDelimeters
------------ -------------------- --------------------
XML Unicode  244                  223
XML ANSI     198                  180
JSON Unicode 272                  224
JSON ANSI    221                  183


Все хорошо ужимается, но нужно помнить об одной особенности. Предположим, что изначально данные приходили в ANSI, а потом тип переменной поменялся на Unicode: 

DECLARE @t TABLE (val VARBINARY(MAX))
INSERT INTO @t
VALUES (COMPRESS('[{"Name":"ThinkPad E460"}]')) -- VARCHAR(8000)
     , (COMPRESS(N'[{"Name":"ThinkPad E460"}]')) -- NVARCHAR(4000)

SELECT val
     , DECOMPRESS(val)
     , CAST(DECOMPRESS(val) AS NVARCHAR(MAX))
     , CAST(DECOMPRESS(val) AS VARCHAR(MAX))
FROM @t


Функция COMPRESS возвращает разные бинарные последовательности для ANSI/Unicode и при последующем чтении мы столкнемся с ситуацией, что часть данных сохранено как ANSI, а часть — в Unicode. Крайне тяжело потом угадать, к какому типу делать приведение: 

---------------------------- -------------------------------------------------------
筛丢浡≥∺桔湩偫摡䔠㘴∰嵽        [{"Name":"ThinkPad E460"}]
[{"Name":"ThinkPad E460"}]   [ { " N a m e " : " T h i n k P a d   E 4 6 0 " } ]


Если мы захотим построить нагруженную систему, то использование функции COMPRESS замедлит вставку: 

USE tempdb
GO

DROP TABLE IF EXISTS #Compress
DROP TABLE IF EXISTS #NoCompress
GO

CREATE TABLE #NoCompress (DatabaseLogID INT PRIMARY KEY, JSON_Val NVARCHAR(MAX))
CREATE TABLE #Compress   (DatabaseLogID INT PRIMARY KEY, JSON_CompressVal VARBINARY(MAX))
GO

SET STATISTICS IO, TIME ON

INSERT INTO #NoCompress
SELECT DatabaseLogID
     , JSON_Val = (
            SELECT PostTime, DatabaseUser, [Event], [Schema], [Object], [TSQL]
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )
FROM AdventureWorks2014.dbo.DatabaseLog
OPTION(MAXDOP 1)

INSERT INTO #Compress
SELECT DatabaseLogID
     , JSON_CompressVal = COMPRESS((
            SELECT PostTime, DatabaseUser, [Event], [Schema], [Object], [TSQL]
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
         ))
FROM AdventureWorks2014.dbo.DatabaseLog
OPTION(MAXDOP 1)

SET STATISTICS IO, TIME OFF


Причем очень существенно: 

NoCompress: CPU time = 15 ms,  elapsed time = 25 ms
Compress:   CPU time = 218 ms, elapsed time = 280 ms


При этом размер таблицы сократится: 

SELECT obj_name = OBJECT_NAME(p.[object_id])
     , a.[type_desc]
     , a.total_pages
     , total_mb = a.total_pages * 8 / 1024.
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE p.[object_id] IN (
        OBJECT_ID('#Compress'),
        OBJECT_ID('#NoCompress')
    )


obj_name       type_desc     total_pages  total_mb
-------------- ------------- ------------ ---------
NoCompress     IN_ROW_DATA   204          1.593750
NoCompress     LOB_DATA      26           0.203125
Compress       IN_ROW_DATA   92           0.718750
Compress       LOB_DATA      0            0.000000


Кроме того, чтение из таблицы сжатых данных потом сильно замедляет функция DECOMPRESS: 

SET STATISTICS IO, TIME ON

SELECT *
FROM #NoCompress
WHERE JSON_VALUE(JSON_Val, '$.Event') = 'CREATE_TABLE'

SELECT DatabaseLogID, [JSON] = CAST(DECOMPRESS(JSON_CompressVal) AS NVARCHAR(MAX))
FROM #Compress
WHERE JSON_VALUE(CAST(DECOMPRESS(JSON_CompressVal) AS NVARCHAR(MAX)), '$.Event') =
    N'CREATE_TABLE'

SET STATISTICS IO, TIME OFF


Логические чтения сократятся, но скорость выполнения останется крайне низкой: 

Table 'NoCompress'. Scan count 1, logical reads 187, ...
    CPU time = 16 ms, elapsed time = 37 ms

Table 'Compress'. Scan count 1, logical reads 79, ...
    CPU time = 109 ms, elapsed time = 212 ms


Как вариант, можно добавить PERSISTED вычисляемый столбец: 

ALTER TABLE #Compress ADD EventType_Persisted
    AS CAST(JSON_VALUE(CAST(
            DECOMPRESS(JSON_CompressVal) AS NVARCHAR(MAX)), '$.Event')
        AS VARCHAR(200)) PERSISTED


Либо создать вычисляемый столбец и на основе него индекс: 

ALTER TABLE #Compress ADD EventType_NonPersisted
    AS CAST(JSON_VALUE(CAST(
            DECOMPRESS(JSON_CompressVal) AS NVARCHAR(MAX)), '$.Event')
        AS VARCHAR(200))

CREATE INDEX ix ON #Compress (EventType_NonPersisted)


Иногда задержки по сети намного сильнее влияют на производительность, нежели те примеры, что я привел выше. Представьте, что на клиенте мы можем ужать JSON данные GZIP и отправить их на сервер: 

DECLARE @json NVARCHAR(MAX) = (
        SELECT t.[name]
             , t.[object_id]
             , [columns] = (
                     SELECT c.column_id, c.[name], c.system_type_id
                     FROM sys.all_columns c
                     WHERE c.[object_id] = t.[object_id]
                     FOR JSON AUTO
                 )
        FROM sys.all_objects t
        FOR JSON AUTO
    )

SELECT InitialSize = DATALENGTH(@json) / 1048576.
     , CompressSize = DATALENGTH(COMPRESS(@json)) / 1048576.


Для меня это стало «спасительный кругом», когда пытался сократить сетевой трафик на одном из проектов: 

InitialSize    CompressSize
-------------- -------------
1.24907684     0.10125923


4. Compression


Чтобы уменьшить размер таблиц, можно также воспользоваться сжатием данных. Ранее сжатие было доступно только в Enterprise редакции. Но с выходом SQL Server 2016 SP1 использовать данную функциональность можно хоть на Express: 

USE AdventureWorks2014
GO

DROP TABLE IF EXISTS #InitialTable
DROP TABLE IF EXISTS #None
DROP TABLE IF EXISTS #Row
DROP TABLE IF EXISTS #Page
GO

CREATE TABLE #None (ID INT, Val NVARCHAR(MAX), INDEX ix CLUSTERED (ID)
    WITH (DATA_COMPRESSION = NONE))

CREATE TABLE #Row  (ID INT, Val NVARCHAR(MAX), INDEX ix CLUSTERED (ID)
    WITH (DATA_COMPRESSION = ROW))

CREATE TABLE #Page (ID INT, Val NVARCHAR(MAX), INDEX ix CLUSTERED (ID)
    WITH (DATA_COMPRESSION = PAGE))
GO

SELECT h.SalesOrderID
     , JSON_Data = 
           (
                SELECT p.[Name]
                FROM Sales.SalesOrderDetail d
                JOIN Production.Product p ON d.ProductID = p.ProductID
                WHERE d.SalesOrderID = h.SalesOrderID
                FOR JSON AUTO
           )
INTO #InitialTable
FROM Sales.SalesOrderHeader h

SET STATISTICS IO, TIME ON

INSERT INTO #None
SELECT *
FROM #InitialTable
OPTION(MAXDOP 1)

INSERT INTO #Row
SELECT *
FROM #InitialTable
OPTION(MAXDOP 1)

INSERT INTO #Page
SELECT *
FROM #InitialTable
OPTION(MAXDOP 1)

SET STATISTICS IO, TIME OFF


None: CPU time = 62 ms,  elapsed time = 68 ms
Row:  CPU time = 94 ms,  elapsed time = 89 ms
Page: CPU time = 125 ms, elapsed time = 126 ms


Сжатие на уровне страниц использует алгоритмы, которые находят похожие куски данных и заменяют их на меньшие по объёму значения. Сжатие на уровне строк урезает типы до минимально необходимых, а также обрезает лишние символы. Например, у нас столбец имеет тип INT, который занимает 4 байта, но хранятся там значения меньше 255. Для таких записей тип усекается, и данные на диске занимают место как будто это TINYINT.

USE tempdb
GO

SELECT obj_name = OBJECT_NAME(p.[object_id])
     , a.[type_desc]
     , a.total_pages
     , total_mb = a.total_pages * 8 / 1024.
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE p.[object_id] IN (OBJECT_ID('#None'), OBJECT_ID('#Page'), OBJECT_ID('#Row'))


obj_name   type_desc     total_pages  total_mb
---------- ------------- ------------ ---------
None      IN_ROW_DATA   1156         9.031250
Row       IN_ROW_DATA   1132         8.843750
Page      IN_ROW_DATA   1004         7.843750


5. ColumnStore


Но что мне нравится больше всего — это ColumnStore индексы, которые от версии к версии в SQL Server становятся все лучше и лучше.

Главная идея ColumnStore — разбивать данные в таблице на RowGroup-ы примерно по 1 миллиону строк и в рамках этой группы сжимать данные по столбцам. За счет этого достигается существенная экономия дискового пространства, сокращение логических чтений и ускорение аналитических запросов. Поэтому если есть необходимость хранения архива с JSON информацией, то можно создать кластерный ColumnStore индекс: 

USE AdventureWorks2014
GO

DROP TABLE IF EXISTS #CCI
DROP TABLE IF EXISTS #InitialTable
GO

CREATE TABLE #CCI (ID INT, Val NVARCHAR(MAX), INDEX ix CLUSTERED COLUMNSTORE)
GO

SELECT h.SalesOrderID
     , JSON_Data = CAST(
           (
                SELECT p.[Name]
                FROM Sales.SalesOrderDetail d
                JOIN Production.Product p ON d.ProductID = p.ProductID
                WHERE d.SalesOrderID = h.SalesOrderID
                FOR JSON AUTO
           )
       AS VARCHAR(8000)) -- SQL Server 2012..2016
INTO #InitialTable
FROM Sales.SalesOrderHeader h

SET STATISTICS TIME ON

INSERT INTO #CCI
SELECT *
FROM #InitialTable

SET STATISTICS TIME OFF


Скорость вставки в таблицу при этом будет примерно соответствовать PAGE сжатию. Кроме того, можно более тонко настроить процесс под OLTP нагрузку за счет опции COMPRESSION_DELAY.

CCI: CPU time = 140 ms, elapsed time = 136 ms


До SQL Server 2017 ColumnStore индексы не поддерживали типы данных [N]VARCHAR (MAX), но вместе с релизом новой версии нам разрешили хранить строки любой длины в ColumnStore.

USE tempdb
GO

SELECT o.[name]
     , s.used_page_count / 128.
FROM sys.indexes i
JOIN sys.dm_db_partition_stats s ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE i.[object_id] = OBJECT_ID('#CCI')


Выигрыш от этого иногда бывает очень внушительный: 

------ ---------
CCI   0.796875


6. Create JSON


Теперь рассмотрим, каким образом можно сгенерировать JSON. Если вы уже работали с XML в SQL Server, то здесь все делается по аналогии.

Для формирования JSON проще всего использовать FOR JSON AUTO. В этом случае будет сгенерирован массив JSON из объектов: 

DROP TABLE IF EXISTS #Users
GO

CREATE TABLE #Users (
      UserID INT
    , UserName SYSNAME
    , RegDate DATETIME
)

INSERT INTO #Users
VALUES (1, 'Paul Denton', '20170123')
     , (2, 'JC Denton', NULL)
     , (3, 'Maggie Cho', NULL)

SELECT *
FROM #Users
FOR JSON AUTO


[
    {
        "UserID":1,
        "UserName":"Paul Denton",
        "RegDate":"2029-01-23T00:00:00"
    },
    {
        "UserID":2,
        "UserName":"JC Denton"
    },
    {
        "UserID":3,
        "UserName":"Maggie Cho"
    }
]


Важно заметить, что NULL значения игнорируются. Если мы хотим их включать в JSON, то можем воспользоваться опцией INCLUDE_NULL_VALUES: 

SELECT UserID, RegDate
FROM #Users
FOR JSON AUTO, INCLUDE_NULL_VALUES


[
    {
        "UserID":1,
        "RegDate":"2017-01-23T00:00:00"
    },
    {
        "UserID":2,
        "RegDate":null
    },
    {
        "UserID":3,
        "RegDate":null
    }
]


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

SELECT TOP(1) UserID, UserName
FROM #Users
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER


{
    "UserID":1,
    "UserName":"Paul Denton"
}


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

SELECT UserID, UserName
FROM #Users
FOR JSON AUTO, ROOT('Users')


{
    "Users":[
        {
            "UserID":1,
            "UserName":"Paul Denton"
        },
        {
            "UserID":2,
            "UserName":"JC Denton"
        },
        {
            "UserID":3,
            "UserName":"Maggie Cho"
        }
    ]
}


Если требуется создать JSON с более сложной структурой, присвоить нужные название свойствам, сгруппировать их, то необходимо использовать выражение FOR JSON PATH: 

SELECT TOP(1) UserID
            , UserName AS [Detail.FullName]
            , RegDate AS [Detail.RegDate]
FROM #Users
FOR JSON PATH


[
    {
        "UserID":1,
        "Detail":{
            "FullName":"Paul Denton",
            "RegDate":"2017-01-23T00:00:00"
        }
    }
]


SELECT t.[name]
     , t.[object_id]
     , [columns] = (
             SELECT c.column_id, c.[name]
             FROM sys.columns c
             WHERE c.[object_id] = t.[object_id]
             FOR JSON AUTO
         )
FROM sys.tables t
FOR JSON AUTO


[
    {
        "name":"#Users",
        "object_id":1483152329,
        "columns":[
            {
            "column_id":1,
            "name":"UserID"
            },
            {
            "column_id":2,
            "name":"UserName"
            },
            {
            "column_id":3,
            "name":"RegDate"
            }
        ]
    }
]


7. Check JSON


Для проверки правильности JSON формата существует функция ISJSON, которая возвращает 1, если это JSON, 0 — если нет и NULL, если был передан NULL.

DECLARE @json1 NVARCHAR(MAX) = N'{"id": 1}'
      , @json2 NVARCHAR(MAX) = N'[1,2,3]'
      , @json3 NVARCHAR(MAX) = N'1'
      , @json4 NVARCHAR(MAX) = N''
      , @json5 NVARCHAR(MAX) = NULL

SELECT ISJSON(@json1) -- 1
     , ISJSON(@json2) -- 1
     , ISJSON(@json3) -- 0
     , ISJSON(@json4) -- 0
     , ISJSON(@json5) -- NULL


8. JsonValue


Чтобы извлечь скалярное значение из JSON, можно воспользоваться функцией JSON_VALUE: 

DECLARE @json NVARCHAR(MAX) = N'
    {
        "UserID": 1,
        "UserName": "JC Denton",
        "IsActive": true,
        "Date": "2016-05-31T00:00:00",
        "Settings": [
             {
                "Language": "EN"
             },
             {
                "Skin": "FlatUI"
             }
          ]
    }'

SELECT JSON_VALUE(@json, '$.UserID')
     , JSON_VALUE(@json, '$.UserName')
     , JSON_VALUE(@json, '$.Settings[0].Language')
     , JSON_VALUE(@json, '$.Settings[1].Skin')
     , JSON_QUERY(@json, '$.Settings')


9. OpenJson


Для парсинга табличных данных используется табличная функция OPENJSON. Сразу стоит заметить, что она будет работать только на базах с уровнем совместимости 130 и выше.

Существует 2 режима работы функции OPENSON. Самый простой — без указания схемы для результирующей выборки: 

DECLARE @json NVARCHAR(MAX) = N'
    {
        "UserID": 1,
        "UserName": "JC Denton",
        "IsActive": true,
        "RegDate": "2016-05-31T00:00:00"
    }'

SELECT * FROM OPENJSON(@json)


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

DECLARE @json NVARCHAR(MAX) = N'
    [
        {
            "User ID": 1,
            "UserName": "JC Denton",
            "IsActive": true,
            "Date": "2016-05-31T00:00:00",
            "Settings": [
                 {
                    "Language": "EN"
                 },
                 {
                    "Skin": "FlatUI"
                 }
              ]
        },
        {
            "User ID": 2,
            "UserName": "Paul Denton",
            "IsActive": false
        }
    ]'

SELECT * FROM OPENJSON(@json)
SELECT * FROM OPENJSON(@json, '$[0]')
SELECT * FROM OPENJSON(@json, '$[0].Settings[0]')

SELECT *
FROM OPENJSON(@json)
    WITH (
          UserID INT '$."User ID"'
        , UserName SYSNAME
        , IsActive BIT
        , RegDate DATETIME '$.Date'
        , Settings NVARCHAR(MAX) AS JSON
        , Skin SYSNAME '$.Settings[1].Skin'
    )


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

DECLARE @json NVARCHAR(MAX) = N'
    [
        {
            "FullName": "JC Denton",
            "Children": [
                { "FullName": "Mary", "Male": "0" },
                { "FullName": "Paul", "Male": "1" }
            ]
        },
        {
            "FullName": "Paul Denton"
        }
    ]'

SELECT t.FullName, c.*
FROM OPENJSON(@json)
    WITH (
          FullName SYSNAME
        , Children NVARCHAR(MAX) AS JSON
    ) t
OUTER APPLY OPENJSON(Children)
    WITH (
          ChildrenName SYSNAME '$.FullName'
        , Male TINYINT
    ) c


10. String Split


Вместе с релизом SQL Server 2016 появилась функция STRING_SPLIT. И все вздохнули с облегчением, что теперь не надо придумывать велосипед для разделения строки на токены. Однако, есть еще одна альтернатива — конструкция OPENJSON, который мы рассматривали ранее. Давайте протестируем несколько вариантов сплита строки: 

SET NOCOUNT ON
SET STATISTICS TIME OFF

DECLARE @x VARCHAR(MAX) = '1' + REPLICATE(CAST(',1' AS VARCHAR(MAX)), 1000)

SET STATISTICS TIME ON

;WITH cte AS
(
    SELECT s = 1
         , e = COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1)
         , v = SUBSTRING(@x, 1, COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1) - 1)
    UNION ALL
    SELECT s = CONVERT(INT, e) + 1
         , e = COALESCE(NULLIF(CHARINDEX(',', @x, e + 1), 0), LEN(@x) + 1)
         , v = SUBSTRING(@x, e + 1, COALESCE(NULLIF(CHARINDEX(',',  @x, e + 1), 0), LEN(@x) + 1)- e - 1)
    FROM cte
    WHERE e < LEN(@x) + 1
)
SELECT v
FROM cte
WHERE LEN(v) > 0
OPTION (MAXRECURSION 0)

SELECT t.c.value('(./text())[1]', 'INT')
FROM ( 
    SELECT x = CONVERT(XML, '' + REPLACE(@x, ',', '') + '').query('.')
) a
CROSS APPLY x.nodes('i') t(c)

SELECT *
FROM STRING_SPLIT(@x, N',') -- NCHAR(1)/CHAR(1)

SELECT [value]
FROM OPENJSON(N'[' + @x + N']') -- [1,2,3,4]

SET STATISTICS TIME OFF


Если посмотреть на результаты, то можно заметить что OPENJSON в некоторых случаях может быть быстрее функции STRING_SPLIT не говоря уже о костылях с XML и CTE: 

              500k    100k   50k    1000
------------- ------- ------ ------ ------
CTE           29407   2406   1266   58
XML           6520    1084   553    259
STRING_SPLIT  4665    594    329    27
OPENJSON      2606    506    273    19


При этом если у нас высоконагруженный OLTP, то явной разницы OPENJSON и STRING_SPLIT не наблюдается (1000 итераций + 10 значений через запятую): 

CTE          = 4629 ms
XML          = 4397 ms
STRING_SPLIT = 4011 ms
OPENJSON     = 4047 ms


11. Lax & strict


Начиная с SQL Server 2005, появилась возможность валидации XML со стороны базы за счет использования XML SCHEMA COLLECTION. Мы описываем схему для XML, а затем на ее основе можем проверять корректность данных. Такого функционала в явном виде для JSON нет, но есть обходной путь.

Насколько я помню, для JSON существует 2 типа выражений: strict и lax (используется по умолчанию). Отличие заключается в том, что если мы указываем несуществующие или неправильные пути при парсинге, то для lax выражения мы получим NULL, а в случае strict — ошибку: 

DECLARE @json NVARCHAR(MAX) = N'
    {
        "UserID": 1,
        "UserName": "JC Denton"
    }'

SELECT JSON_VALUE(@json, '$.IsActive')
     , JSON_VALUE(@json, 'lax$.IsActive')
     , JSON_VALUE(@json, 'strict$.UserName')

SELECT JSON_VALUE(@json, 'strict$.IsActive')


Msg 13608, Level 16, State 2, Line 12
Property cannot be found on the specified JSON path.


12. Modify


Для модификации данных внутри JSON присутствует функция JSON_MODIFY. Примеры достаточно простые, поэтому нет смысла их детально расписывать: 

DECLARE @json NVARCHAR(MAX) = N'
    {
        "FirstName": "JC",
        "LastName": "Denton",
        "Age": 20,
        "Skills": ["SQL Server 2014"]
    }'

-- 20 -> 22
SET @json = JSON_MODIFY(@json, '$.Age', CAST(JSON_VALUE(@json, '$.Age') AS INT) + 2)

-- "SQL 2014" -> "SQL 2016"
SET @json = JSON_MODIFY(@json, '$.Skills[0]', 'SQL 2016')

SET @json = JSON_MODIFY(@json, 'append $.Skills', 'JSON')

SELECT * FROM OPENJSON(@json)

-- delete Age
SELECT * FROM OPENJSON(JSON_MODIFY(@json, 'lax$.Age', NULL))

-- set NULL
SELECT * FROM OPENJSON(JSON_MODIFY(@json, 'strict$.Age', NULL))
GO

DECLARE @json NVARCHAR(100) = N'{ "price": 105.90 }' -- rename
SET @json = 
    JSON_MODIFY( 
        JSON_MODIFY(@json, '$.Price',
            CAST(JSON_VALUE(@json, '$.price') AS NUMERIC(6,2))),
                '$.price', NULL)

SELECT @json


13. Convert implicit


И вот мы начинаем добираться до самого интересного, а именно вопросов, связанных с производительностью.

При парсинге JSON нужно помнить об одном нюансе — OPENJSON и JSON_VALUE возвращают результат в Unicode, если мы это не переопределяем. В базе AdventureWorks столбец AccountNumber имеет тип данных VARCHAR: 

USE AdventureWorks2014
GO

DECLARE @json NVARCHAR(MAX) = N'{ "AccountNumber": "AW00000009" }'

SET STATISTICS IO ON

SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE AccountNumber = JSON_VALUE(@json, '$.AccountNumber')

SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE AccountNumber = CAST(JSON_VALUE(@json, '$.AccountNumber') AS VARCHAR(10))

SET STATISTICS IO OFF


Разница в логических чтениях: 

Table 'Customer'. Scan count 1, logical reads 37, ...
Table 'Customer'. Scan count 0, logical reads 2, ...


Из-за того, что типы данных между столбцом и результатом функции у нас не совпадают, SQL Server приходится выполнять неявное преобразование типа, исходя из старшинства. В нашем случае к NVARCHAR. Увы, но все вычисления и преобразования на индексном столбце чаще всего приводят к IndexScan:

lxtzwh-3ltippjhrixmp5xafxzm.png

Если же указать явно тип, как и у столбца, то мы получим IndexSeek:

bem8xhmvawxe8sq0l6ski6pvohk.png

14. Indexes


Теперь рассмотрим, как можно индексировать JSON объекты. Как я уже говорил вначале, в SQL Server 2016 не был добавлен отдельный тип данных для JSON, в отличие от XML. Поэтому для его хранения вы можете использовать любые строковые типы данных.

Если кто-то имеет опыт работы с XML, то помнит, что для этого формата в SQL Server существует несколько типов индексов, позволяющих ускорить определенные выборки. Для строковых же типов, в которых предполагается хранение JSON, таких индексов просто не существует.

Увы, но JSONB не завезли. Команда разработки торопилась при релизе JSON функционала и сказала буквально следующее: «Если вам будет не хватать скорости, то мы добавим JSONB в следующей версии». С релизом SQL Server 2017 этого не произошло.

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

USE AdventureWorks2014
GO

DROP TABLE IF EXISTS #JSON
GO

CREATE TABLE #JSON (
      DatabaseLogID INT PRIMARY KEY
    , InfoJSON NVARCHAR(MAX) NOT NULL
)
GO

INSERT INTO #JSON
SELECT DatabaseLogID
     , InfoJSON = (
            SELECT PostTime, DatabaseUser, [Event], [Schema], [Object], [TSQL]
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
         )
FROM dbo.DatabaseLog


Каждый раз парсить один и те же данные не очень рационально: 

SET STATISTICS IO, TIME ON

SELECT *
FROM #JSON
WHERE JSON_VALUE(InfoJSON, '$.Schema') + '.' + JSON_VALUE(InfoJSON, '$.Object') =
    'Person.Person'

SET STATISTICS IO, TIME OFF


Table 'JSON'. Scan count 1, logical reads 187, ...
    CPU time = 16 ms, elapsed time = 29 ms


Поэтому создание вычисляемого столбца и последующее включение его в индекс бывает иногда оправданным: 

ALTER TABLE #JSON
    ADD ObjectName AS
        JSON_VALUE(InfoJSON, '$.Schema') + '.' + JSON_VALUE(InfoJSON, '$.Object')
GO

CREATE INDEX IX_ObjectName ON #JSON (ObjectName)
GO

SET STATISTICS IO, TIME ON

SELECT *
FROM #JSON
WHERE JSON_VALUE(InfoJSON, '$.Schema') + '.' + JSON_VALUE(InfoJSON, '$.Object') =
    'Person.Person'

SELECT *
FROM #JSON
WHERE ObjectName = 'Person.Person'

SET STATISTICS IO, TIME OFF


При этом оптимизатор SQL Server весьма умный, поэтому менять в коде ничего не потребуется: 

Table 'JSON'. Scan count 1, logical reads 13, ...
    CPU time = 0 ms, elapsed time = 1 ms

Table 'JSON'. Scan count 1, logical reads 13, ...
    CPU time = 0 ms, elapsed time = 1 ms


Кроме того, можно создавать как обычные индексы, так и полнотекстовые, если мы хотим получить поиск по содержимому массивов или целых частей объектов.

При этом полнотекстовый индекс не имеет каких-то специальных правил обработки JSON, он всего лишь разбивает текст на отдельные токены, используя в качестве разделителей двойные кавычки, запятые, скобки — то из чего состоит сама структура JSON: 

USE AdventureWorks2014
GO

DROP TABLE IF EXISTS dbo.LogJSON
GO

CREATE TABLE dbo.LogJSON (
      DatabaseLogID INT
    , InfoJSON NVARCHAR(MAX) NOT NULL
    , CONSTRAINT pk PRIMARY KEY (DatabaseLogID)
)
GO

INSERT INTO dbo.LogJSON
SELECT DatabaseLogID
     , InfoJSON = (
            SELECT PostTime, DatabaseUser, [Event], ObjectName = [Schema] + '.' + [Object]
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
         )
FROM dbo.DatabaseLog
GO

IF EXISTS(
    SELECT *
    FROM sys.fulltext_catalogs
    WHERE [name] = 'JSON_FTC'
)
    DROP FULLTEXT CATALOG JSON_FTC
GO

CREATE FULLTEXT CATALOG JSON_FTC WITH ACCENT_SENSITIVITY = ON AUTHORIZATION dbo
GO

IF EXISTS (
        SELECT *
        FROM sys.fulltext_indexes
        WHERE [object_id] = OBJECT_ID(N'dbo.LogJSON')
    ) BEGIN
    ALTER FULLTEXT INDEX ON dbo.LogJSON DISABLE
    DROP FULLTEXT INDEX ON dbo.LogJSON
END
GO

CREATE FULLTEXT INDEX ON dbo.LogJSON (InfoJSON) KEY INDEX pk ON JSON_FTC
GO

SELECT *
FROM dbo.LogJSON
WHERE CONTAINS(InfoJSON, 'ALTER_TABLE')


15. Parser performance


И наконец мы подошли, пожалуй, к самой интересной части этой статьи. Насколько быстрее парсится JSON по сравнению с XML на SQL Server? Чтобы ответить на этот вопрос, я подготовил серию тестов.

Подготавливаем 2 больших файла в JSON и XML формате: 

/*
    EXEC sys.sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO

    EXEC sys.sp_configure 'xp_cmdshell', 1
    GO
    RECONFIGURE WITH OVERRIDE
    GO
*/

USE AdventureWorks2014
GO

DROP PROCEDURE IF EXISTS ##get_xml
DROP PROCEDURE IF EXISTS ##get_json
GO

CREATE PROCEDURE ##get_xml
AS
    SELECT r.ProductID
         , r.[Name]
         , r.ProductNumber
         , d.OrderQty
         , d.UnitPrice
         , r.ListPrice
         , r.Color
         , r.MakeFlag
    FROM Sales.SalesOrderDetail d
    JOIN Production.Product r ON d.ProductID = r.ProductID
    FOR XML PATH ('Product'), ROOT('Products')
GO

CREATE PROCEDURE ##get_json
AS
    SELECT (
        SELECT r.ProductID
             , r.[Name]
             , r.ProductNumber
             , d.OrderQty
             , d.UnitPrice
             , r.ListPrice
             , r.Color
             , r.MakeFlag
        FROM Sales.SalesOrderDetail d
        JOIN Production.Product r ON d.ProductID = r.ProductID
        FOR JSON PATH
    )
GO

DECLARE @sql NVARCHAR(4000)
SET @sql =
    'bcp "EXEC ##get_xml" queryout "X:\sample.xml" -S ' + @@servername + ' -T -w -r -t'
EXEC sys.xp_cmdshell @sql

SET @sql =
    'bcp "EXEC ##get_json" queryout "X:\sample.txt" -S ' + @@servername + ' -T -w -r -t'
EXEC sys.xp_cmdshell @sql


Проверяем производительность OPENJSON, OPENXML и XQuery: 


SET NOCOUNT ON
SET STATISTICS TIME ON

DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'X:\sample.xml', SINGLE_BLOB) x

DECLARE @jsonu NVARCHAR(MAX)
SELECT @jsonu = BulkColumn
FROM OPENROWSET(BULK 'X:\sample.txt', SINGLE_NCLOB) x

/*
    XML:      CPU = 891 ms, Time = 886 ms
    NVARCHAR: CPU = 141 ms, Time = 166 ms
*/

SELECT ProductID =     t.c.value('(ProductID/text())[1]', 'INT')
     , [Name] =        t.c.value('(Name/text())[1]', 'NVARCHAR(50)')
     , ProductNumber = t.c.value('(ProductNumber/text())[1]', 'NVARCHAR(25)')
     , OrderQty =      t.c.value('(OrderQty/text())[1]', 'SMALLINT')
     , UnitPrice =     t.c.value('(UnitPrice/text())[1]', 'MONEY')
     , ListPrice =     t.c.value('(ListPrice/text())[1]', 'MONEY')
     , Color =         t.c.value('(Color/text())[1]', 'NVARCHAR(15)')
     , MakeFlag =      t.c.value('(MakeFlag/text())[1]', 'BIT')
FROM @xml.nodes('Products/Product') t(c)

/*
    CPU time = 6203 ms, elapsed time = 6492 ms
*/

DECLARE @doc INT
EXEC sys.sp_xml_preparedocument @doc OUTPUT, @xml

SELECT *
FROM OPENXML(@doc, '/Products/Product', 2)
    WITH (
          ProductID INT
        , [Name] NVARCHAR(50)
        , ProductNumber NVARCHAR(25)
        , OrderQty SMALLINT
        , UnitPrice MONEY
        , ListPrice MONEY
        , Color NVARCHAR(15)
        , MakeFlag BIT
    )

EXEC sys.sp_xml_removedocument @doc

/*
    CPU time = 2656 ms, elapsed time = 3489 ms
    CPU time = 3844 ms, elapsed time = 4482 ms
    CPU time = 0 ms, elapsed time = 4 ms
*/

SELECT *
FROM OPENJSON(@jsonu)
    WITH (
          ProductID INT
        , [Name] NVARCHAR(50)
        , ProductNumber NVARCHAR(25)
        , OrderQty SMALLINT
        , UnitPrice MONEY
        , ListPrice MONEY
        , Color NVARCHAR(15)
        , MakeFlag BIT
    )

/*
    CPU time = 1359 ms, elapsed time = 1642 ms
*/

SET STATISTICS TIME, IO OFF


Теперь проверим производительность скалярной функции JSON_VALUE относительно XQuery: 

SET NOCOUNT ON

DECLARE @jsonu NVARCHAR(MAX) = N'[
    {"User":"Sergey Syrovatchenko","Age":28,"Skills":["SQL Server","T-SQL","JSON","XML"]},
    {"User":"JC Denton","Skills":["Microfibral Muscle","Regeneration","EMP Shield"]},
    {"User":"Paul Denton","Age":32,"Skills":["Vision Enhancement"]}]'

DECLARE @jsonu_f NVARCHAR(MAX) = N'[
   {
      "User":"Sergey Syrovatchenko",
      "Age":28,
      "Skills":[
         "SQL Server",
         "T-SQL",
         "JSON",
         "XML"
      ]
   },
   {
      "User":"JC Denton",
      "Skills":[
         "Microfibral Muscle",
         "Regeneration",
         "EMP Shield"
      ]
   },
   {
      "User":"Paul Denton",
      "Age":32,
      "Skills":[
         "Vision Enhancement"
      ]
   }
]'

DECLARE @json VARCHAR(MAX) = @jsonu
      , @json_f VARCHAR(MAX) = @jsonu_f

DECLARE @xml XML = N'

    
        28
        
            SQL Server
            T-SQL
            JSON
            XML
        
    
    
        
            Microfibral Muscle
            Regeneration
            EMP Shield
        
    
    
        28
        
            Vision Enhancement
        
    
'

DECLARE @i INT
      , @int INT
      , @varchar VARCHAR(100)
      , @nvarchar NVARCHAR(100)
      , @s DATETIME
      , @runs INT = 100000

DECLARE @t TABLE (
      iter INT IDENTITY PRIMARY KEY
    , data_type VARCHAR(100)
    , [path] VARCHAR(1000)
    , [type] VARCHAR(1000)
    , time_ms INT
)

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @int = JSON_VALUE(@jsonu, '$[0].Age')
         , @i += 1
INSERT INTO @t
SELECT '@jsonu', '$[0].Age', 'INT', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @int = JSON_VALUE(@jsonu_f, '$[0].Age')
         , @i += 1
INSERT INTO @t
SELECT '@jsonu_f', '$[0].Age', 'INT', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @int = JSON_VALUE(@json, '$[0].Age')
         , @i += 1
INSERT INTO @t
SELECT '@json', '$[0].Age', 'INT', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @int = JSON_VALUE(@json_f, '$[0].Age')
         , @i += 1
INSERT INTO @t
SELECT '@json_f', '$[0].Age', 'INT', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @int = @xml.value('(Users/User[1]/Age/text())[1]', 'INT')
         , @i += 1
INSERT INTO @t
SELECT '@xml', '(Users/User[1]/Age/text())[1]', 'INT', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @nvarchar = JSON_VALUE(@jsonu, '$[1].User')
         , @i += 1
INSERT INTO @t
SELECT '@jsonu', '$[1].User', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @nvarchar = JSON_VALUE(@jsonu_f, '$[1].User')
         , @i += 1
INSERT INTO @t
SELECT '@jsonu_f', '$[1].User', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @varchar = JSON_VALUE(@json, '$[1].User')
         , @i += 1
INSERT INTO @t
SELECT '@json', '$[1].User', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @varchar = JSON_VALUE(@json_f, '$[1].User')
         , @i += 1
INSERT INTO @t
SELECT '@json_f', '$[1].User', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @nvarchar = @xml.value('(Users/User[2]/@Name)[1]', 'NVARCHAR(100)')
         , @i += 1
INSERT INTO @t
SELECT '@xml', '(Users/User[2]/@Name)[1]', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @varchar = @xml.value('(Users/User[2]/@Name)[1]', 'VARCHAR(100)')
         , @i += 1
INSERT INTO @t
SELECT '@xml', '(Users/User[2]/@Name)[1]', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @nvarchar = JSON_VALUE(@jsonu, '$[2].Skills[0]')
         , @i += 1
INSERT INTO @t
SELECT '@jsonu', '$[2].Skills[0]', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @nvarchar = JSON_VALUE(@jsonu_f, '$[2].Skills[0]')
         , @i += 1
INSERT INTO @t
SELECT '@jsonu_f', '$[2].Skills[0]', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @varchar = JSON_VALUE(@json, '$[2].Skills[0]')
         , @i += 1
INSERT INTO @t
SELECT '@json', '$[2].Skills[0]', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @varchar = JSON_VALUE(@json_f, '$[2].Skills[0]')
         , @i += 1
INSERT INTO @t
SELECT '@json_f', '$[2].Skills[0]', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
    SELECT @varchar = @xml.value('(Users/User[3]/Skills/Skill/text())[1]', 'VARCHAR(100)')
         , @i += 1
INSERT INTO @t
SELECT '@xml', '(Users/User[3]/Skills/Skill/text())[1]', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())

SELECT * FROM @t


Полученные результаты: 

iter   data_type  path                                    type      2016 SP1    2017 RTM
------ ---------- --------------------------------------- --------- ----------- -----------
1      @jsonu     $[0].Age                                INT       830         273
2      @jsonu_f   $[0].Age                                INT       853         300
3      @json      $[0].Age                                INT       963         374
4      @json_f    $[0].Age                                INT       987         413
5      @xml       (Users/User[1]/Age/text())[1]           INT       23333       24717

6      @jsonu     $[1].User                               NVARCHAR  1047        450
7      @jsonu_f   $[1].User                               NVARCHAR  1153        567
8      @json      $[1].User                               VARCHAR   1177        570
9      @json_f    $[1].User                               VARCHAR   1303        693
10     @xml       (Users/User[2]/@Name)[1]                NVARCHAR  18864       20070
11     @xml       (Users/User[2]/@Name)[1]                VARCHAR   18913       20117

12     @jsonu     $[2].Skills[0]                          NVARCHAR  1347        746
13     @jsonu_f   $[2].Skills[0]                          NVARCHAR  1563        980
14     @json      $[2].Skills[0]                          VARCHAR   1483        860
15     @json_f    $[2].Skills[0]                          VARCHAR   1717        1094
16     @xml       (Users/User[3]/Skills/Skill/text())[1]  VARCHAR   19510       20767


И есть еще один интересный нюанс — не нужно смешивать вызовы JSON_VALUE и OPENJSON. Кроме того старайтесь указывать только те столбцы, которые реально нужны после парсинга.

C JSON все предельно просто — чем меньше столбцов необходимо парсить, тем быстрее мы получим результат: 

SET NOCOUNT ON
SET STATISTICS TIME ON

DECLARE @json NVARCHAR(MAX)
SELECT @json = BulkColumn
FROM OPENROWSET(BULK 'X:\sample.txt', SINGLE_NCLOB) x

SELECT COUNT_BIG(*)
FROM OPENJSON(@json)
WITH (
      ProductID INT
    , ProductNumber NVARCHAR(25)
    , OrderQty SMALLINT
    , UnitPrice MONEY
    , ListPrice MONEY
    , Color NVARCHAR(15)
)
WHERE Color = 'Black'

SELECT COUNT_BIG(*)
FROM OPENJSON(@json) WITH (Color NVARCHAR(15))
WHERE Color = 'Black'

SELECT COUNT_BIG(*)
FROM OPENJSON(@json)
WHERE JSON_VALUE(value, '$.Color') = 'Black'

/*
    2016 SP1:

    CPU time = 1140 ms, elapsed time = 1144 ms
    CPU time = 781 ms, elapsed time = 789 ms
    CPU time = 2157 ms, elapsed time = 2144 ms

    2017 RTM:

    CPU time = 1016 ms, elapsed time = 1034 ms
    CPU time = 718 ms, elapsed time = 736 ms
    CPU time = 1282 ms, elapsed time = 1286 ms
*/


Краткие выводы


  • Извлечение данных из JSON происходит от 2 до 10 раз быстрее, чем из XML.
  • Хранение JSON зачастую более избыточное, нежели в XML формате.
  • Процессинг JSON данных в Unicode происходит на 5–15% быстрее.
  • При использовании JSON можно существенно снизить нагрузку на CPU сервера.
  • В SQL Server 2017 существенно ускорили парсинг скалярных значений из JSON.


Железо / софт


Windows 8.1 Pro 6.3 x64
Core i5 3470 3.2GHz, DDR3-1600 32Gb, Samsung 850 Evo 250Gb
SQL Server 2016 SP1 Developer (13.0.4001.0)
SQL Server 2017 RTM Developer (14.0.1000.169)


Видео


Читать всю эту информацию весьма утомительно, поэтому для любителей «послушать» есть видео с недавней конфы: SQL Server 2016 / 2017: JSON. Видео отличается от поста отсутствием лишь пары примеров.

И небольшое послесловие…


Так уж вышло, что я очень надолго забросил написание статей. Смена работы, два проекта 24/7, периодическая фрустрация за чашечкой какао и собственный пет-проект, который скоро отправится на GitHub. И вот пришел к осознанию того, что мне снова хочется поделиться чем-то полезным с комьюнити и увлечь читателя больше, чем на две страницы технической информации.

Знаю, что краткость — не мой конек. Но если вы дочитали до конца, то надеюсь, это было полезным. В любом случае буду рад конструктивным комментариям о вашем жизненном опыте использования JSON на SQL Server 2016 / 2017. Отдельная благодарность, если вы проверите скорость последних двух примеров. Есть подозрение, что JSON не всегда такой быстрый, и интересно найти репро.

© Habrahabr.ru