SQL Server 2017 JSON



    Когда много лет подряд 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'
    <Manufacturer Name="Lenovo">
      <Product Name="ThinkPad E460">
        <Model Name="20ETS03100">
          <CPU>i7-6500U</CPU>
          <Memory>16</Memory>
          <SSD>256</SSD>
        </Model>
        <Model Name="20ETS02W00">
          <CPU>i5-6200U</CPU>
          <Memory>8</Memory>
          <HDD>1000</HDD>
        </Model>
        <Model Name="20ETS02V00">
          <CPU>i5-6200U</CPU>
          <Memory>4</Memory>
          <HDD>500</HDD>
        </Model>
      </Product>
    </Manufacturer>'
    
    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'<Manufacturer Name="Lenovo"><Product Name="ThinkPad E460"><Model Name="20ETS03100"><CPU>i7-6500U</CPU><Memory>16</Memory><SSD>256</SSD></Model><Model Name="20ETS02W00"><CPU>i5-6200U</CPU><Memory>8</Memory><HDD>1000</HDD></Model><Model Name="20ETS02V00"><CPU>i5-6200U</CPU><Memory>4</Memory><HDD>500</HDD></Model></Product></Manufacturer>'
          , @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, '<i>' + REPLACE(@x, ',', '</i><i>') + '</i>').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:



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



    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'
    <Users>
        <User Name="Sergey Syrovatchenko">
            <Age>28</Age>
            <Skills>
                <Skill>SQL Server</Skill>
                <Skill>T-SQL</Skill>
                <Skill>JSON</Skill>
                <Skill>XML</Skill>
            </Skills>
        </User>
        <User Name="JC Denton">
            <Skills>
                <Skill>Microfibral Muscle</Skill>
                <Skill>Regeneration</Skill>
                <Skill>EMP Shield</Skill>
            </Skills>
        </User>
        <User Name="Paul Denton">
            <Age>28</Age>
            <Skills>
                <Skill>Vision Enhancement</Skill>
            </Skills>
        </User>
    </Users>'
    
    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. Видео отличается от поста отсутствием лишь пары примеров.

    + если хотите поделиться этой статьей с англоязычной аудиторией: SQL Server 2017: JSON

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


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

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

    Подробнее
    Реклама
    Комментарии 14
    • 0
      Латентное хейтерство не приведет к улучшению качества данного поста. Можно попросить, когда минусуете написать по какой причине. Заранее спасибо.
      • +1
        Обычно подобные преждевременные сообщения, вызванные парой минусов-выбросов, тоже не помогают. Что про посты, что про комменты.
      • +2
        То есть если сравнивать с постгресом, то никакого аналога GIN пока нет? Так, чтобы без всяких колонок кинуть индекс на json(b) поле, и получить индексированный поиск по произвольным структурам?

        PS Отличная статья, сразу захотелось аналогичной глубины описания на PostgreSQL :)
        • +2
          Пока что в SQL Server 2016 / 2017 не предусмотрена возможность создания индексов по аналогии с GIN. Аргументация у разработчиков примерно такая: «все и так быстро, но если не устраивает скорость, то может в следующей версии добавим». Примерно по такому принципу в SQL Server 2012 SP1 добавили селективные XML индексы.

          В тоже время, есть некоторые обходные пути как можно ускорить поиск по произвольной JSON структуре. Можно создать кластерный ColumnStore и хранить в нем JSON. При парсинге значений будет использоваться batch режим вместо построчной обработки — это даст выигрыш при парсинге. Опять же тестировал у себя данный пример и не могу сказать, что batch режим кардинально быстрее. Репро на работе нет, но смогу вечером добавить.
          • 0
            Небольшое репро с использованием кластерного ColumnStore:

            SET NOCOUNT ON
            USE AdventureWorks2014 -- SQL Server 2017
            GO
            
            DROP TABLE IF EXISTS #CCI
            DROP TABLE IF EXISTS #Heap
            GO
            
            CREATE TABLE #CCI (JSON_Data NVARCHAR(4000))
            GO
            
            SELECT JSON_Data =
                (
                    SELECT h.SalesOrderID
                         , h.OrderDate
                         , Product = p.[Name]
                         , d.OrderQty
                         , p.ListPrice
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                )
            INTO #Heap
            FROM Sales.SalesOrderHeader h
            JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
            JOIN Production.Product p ON d.ProductID = p.ProductID
            
            INSERT INTO #CCI
            SELECT * FROM #Heap
            
            CREATE CLUSTERED COLUMNSTORE INDEX CCI ON #CCI

            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] IN (OBJECT_ID('#CCI'), OBJECT_ID('#Heap'))

            ------- -------------
            #CCI    10.687500
            #Heap   30.859375

            Режим batch для колумнстора при последовательном плане более эффективный. Параллельный план дает одинаковую производительностью с поправкой на размер данных:

            SET STATISTICS IO, TIME ON
            
            SELECT JSON_VALUE(JSON_Data, '$.OrderDate')
                 , AVG(CAST(JSON_VALUE(JSON_Data, '$.ListPrice') AS MONEY))
            FROM #CCI
            GROUP BY JSON_VALUE(JSON_Data, '$.OrderDate')
            OPTION(MAXDOP 1)
            --OPTION(RECOMPILE, QUERYTRACEON 8649)
            
            SELECT JSON_VALUE(JSON_Data, '$.OrderDate')
                 , AVG(CAST(JSON_VALUE(JSON_Data, '$.ListPrice') AS MONEY))
            FROM #Heap
            GROUP BY JSON_VALUE(JSON_Data, '$.OrderDate')
            OPTION(MAXDOP 1)
            --OPTION(RECOMPILE, QUERYTRACEON 8649)
            
            SET STATISTICS IO, TIME ON
            
            /*
                OPTION(MAXDOP 1)
            
                #CCI:  CPU = 516 ms,  Elapsed = 568 ms
                #Heap: CPU = 1015 ms, Elapsed = 1137 ms
            
                OPTION(RECOMPILE, QUERYTRACEON 8649)
            
                #CCI:  CPU = 531 ms,  Elapsed = 569 ms
                #Heap: CPU = 828 ms,  Elapsed = 511 ms
            */

            При использовании OPENJSON при последовательном плане различий никаких. При параллельном выполнении на моем компе чтение в batch режиме менее эффективное:

            SET STATISTICS IO, TIME ON
            
            SELECT OrderDate, AVG(ListPrice)
            FROM #CCI
            CROSS APPLY OPENJSON(JSON_Data)
                WITH (
                      OrderDate DATE
                    , ListPrice MONEY
                )
            GROUP BY OrderDate
            OPTION(MAXDOP 1)
            --OPTION(RECOMPILE, QUERYTRACEON 8649)
            
            SELECT OrderDate, AVG(ListPrice)
            FROM #Heap
            CROSS APPLY OPENJSON(JSON_Data)
                WITH (
                      OrderDate DATE
                    , ListPrice MONEY
                )
            GROUP BY OrderDate
            OPTION(MAXDOP 1)
            --OPTION(RECOMPILE, QUERYTRACEON 8649)
            
            SET STATISTICS IO, TIME OFF
            
            /*
                OPTION(MAXPOD 1)
                #CCI:  CPU = 875 ms, Elapsed = 902 ms
                #Heap: CPU = 812 ms, Elapsed = 927 ms
            
                OPTION(RECOMPILE, QUERYTRACEON 8649)
                #CCI:  CPU = 875 ms, Elapsed = 909 ms
                #Heap: CPU = 859 ms, Elapsed = 366 ms
            */

          • 0
            интересная статья, спасибо, но насколько это востребовано — напрямую брать json из базы?
            или для нагруженных систем более чем актуально?
            • +2
              У нас вот REST API, приходит JSON и уходит JSON, все делается в базе, и входной JSON там парсится и выходные данные сразу конвертятся в него, время выполнения уменьшилось в 2-3 раза по сравнению с использование Entity Framework.
              • +1

                Тоже думаем уходить от ORM-a из-за трудностей с динамической схемой. Фактически планируем создавать записи хранящие JSON + некоторое количество полей по которым ведется связывание и выборки (вычисляются на основе самого объекта который храним). NoSQL использовать не можем из-за регуляций. Пока прототип выглядит очень достойно в плане использования (код контроллеров сильно похудел, много boilerplate code вроде PUT, PATCH, валидации и прочего ушло из контроллеров) и производительности (для сильно связанных данных, которые были разнесены по десятку таблиц из-за нормализации, разница в десятки раз, главным образом из-за отсутствия дорогих джойнов).


                Кто так пробовал делать, какие подводные камни?


                Относительно JSON, очень хотелось бы хранить данные в BSON (с упаковкой). Много не-текста в схеме.

            • +1
              Спасибо. Как всегда очень интересно, полезно, и без «воды»
              • +1
                Проверил на одной из сильно нагруженных машин (некоторые примеры). Вот результаты:
                1. Datatypes:

                varchar: CPU time = 93 ms, elapsed time = 28 ms
                nvarchar: CPU time = 94 ms, elapsed time = 92 ms
                ntext: CPU time = 469 ms, elapsed time = 1397 ms

                2. Storage:

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

                3. Compress/Decompress:

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

                10. String Split:

                CTE = 5817 ms
                XML = 5461 ms
                STRING_SPLIT = 5239 ms
                OPENJSON = 5304 ms

                15. Parser performance (последний скрипт ток на 2016 скуле, т к 2017 пока не используется в продакшене-ток как тест):

                CPU time = 1763 ms, elapsed time = 1809 ms
                CPU time = 1072 ms, elapsed time = 1079 ms
                CPU time = 3028 ms, elapsed time = 3082 ms

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

                Прошу прощение за редактирование, просто времени было мало-кинул с студии как было(
                • +1

                  Отличная статья, большое спасибо за большое количество кода для собственного тестирования (было бы совсем шоколадно, если добавить весь код в виде одного скрипта на Github для удобства тестирования).
                  Можно больше подробностей про личный проект на Github?

                  • +1
                    По правде, глобальная задумка сделать набор бесплатных тулов для обслуживания SQL Server, которыми сам же и буду пользоваться. Но на эту задачу требуется уж очень много времени, потому и решил начать с чего-то относительно простого — сделать тул для анализа и обслуживания индексов с учетом ошибок конкурирующих решений. А потом на основе этого клепать следующие тулы. Если будет желание поучаствовать в бета-тестировании или просто советом помочь, то буду рад.

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.