SQL Server 2017 JSON

Microsoftが長年にわたって極端から極端へと熱狂していたとき、少しずつあなたはそれに慣れ始め、特定の懐疑心を持って新しいものを待っています。 時間が経つにつれて、この感情は強くなり、無意識のうちに良いものは期待しません。

しかし、時にはすべてが正反対になることがあります。 マイクロソフトは 、すべての確立された人生のステレオタイプを打ち破る完全に機能する機能を箱から出しています。 あなたは新しい機能から次のレーキを期待していますが、毎分、これがまさにあなたが長年欠いていたものであることにますます気づきます。

長い間、 Microsoft ConnectでSQL Server上のJSONを操作するサポートが最も一般的な機能の1つであったため、このパトスの導入にはいくつかの理由があります。 数年が経ち、予想外に、この機能はSQL Server 2016のリリースと共に実装されました。 将来的には非常にうまくいったと言いますが、 Microsoftはそこで止まらず、 SQL Server 2017では既に高速なJSONパーサーのパフォーマンスを大幅に改善しました。

内容：

1. データ型
2. ストレージ
3. 圧縮/解凍
4. 圧縮
5. ColumnStore
6. JSONを作成する
7. JSONを確認する
8. JsonValue
9. OpenJson
10. 文字列の分割
11. 緩い
12. 変更
13. 暗黙の変換
14. インデックス
15. パーサーのパフォーマンス
ビデオ

1.データ型

SQL Serverでの JSONサポートは、最初はすべてのエディションで利用できます。 同時に、 XMLの場合のように、 Microsoftは個別のデータ型を提供しませんでした。 SQL Server上のJSONのデータはプレーンテキストとして保存されます： 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 

留意すべき主なことは、このデータ型またはそのデータ型がどれだけのスペースを使用するかです（ Unicodeとしてデータを保存する場合は1文字あたり2バイト、 ANSI文字列の場合は1バイト）。 また、 Unicode定数の前に「 N 」を置くことを忘れないでください。 そうでなければ、あなたはたくさんの楽しい状況に出くわすことができます：

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

すべてがシンプルに思えますが、違います。 さらに、選択したデータ型がサイズだけでなく、解析速度にも影響することがわかります。

さらに、 非推奨のデータ型-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関数が面白くないという事実です。

 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.ストレージ

XMLとして提示される同様のデータと比較して、 JSONをNVARCHAR / VARCHARとして保存することの利点を見てみましょう。 さらに、 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.圧縮/解凍

SQL Server 2016では、 GZIP圧縮のサポートを追加する新しいCOMPRESS / DECOMPRESS機能が導入されました。

 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 

すべてがうまく圧縮されていますが、1つの機能を覚えておく必要があります。 最初にデータが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 ame " : " T hink P ad 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. 

私にとって、プロジェクトの1つでネットワークトラフィックを削減しようとすると、「ライフライン」になりました。

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

4.圧縮

テーブルのサイズを小さくするために、データ圧縮を使用することもできます。 以前は、圧縮は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の主なアイデアは、テーブル内のデータを約100万行ごとにRowGroupsに分割し、このグループ内で列内のデータを圧縮することです。 これにより、ディスク容量の大幅な節約、論理読み取り値の削減、分析クエリの高速化が実現します。 したがって、 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圧縮に対応します。 さらに、 COMPRESSION_DELAYオプションにより、プロセスをOLTPロードに微調整できます。

 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. JSONを作成する

次に、 JSONを生成する方法を見てみましょう。 SQL Serverで既にXMLを使用している場合、すべては類推によって行われます。

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. JSONを確認する

JSON形式の正確さを検証するために、 JSONの場合は1を返し、そうでない場合は0を返し 、 NULLが渡された場合はNULLを返すISJSON関数があります 。

 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以上のベースでのみ機能することに注意してください。

OPENSON機能には2つの操作モードがあります。 最も単純な-結果の選択にスキーマを指定せずに：

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

2番目のモードでは、返された結果がどのようになるかを説明できます。列名、その番号、値の取得場所：

 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.文字列の分割

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 tcvalue('(./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 

結果を見ると、 XMLとCTEの松葉杖は言うまでもなく、 OPENJSONがSTRING_SPLIT関数よりも高速である場合があります。

  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.緩い

SQL Server 2005以降、 XML SCHEMA COLLECTIONを使用してデータベースからXMLを検証する機会。 XMLのスキーマを記述し、それに基づいてデータの正確性を確認できます。 JSONの明示的な形式ではそのような機能はありませんが、回避策があります。

私が覚えている限り、 JSONには2つのタイプの式があります ： strictとlax （デフォルトで使用）。 違いは、構文解析時に存在しないパスまたは誤ったパスを指定すると、 緩い式ではNULLが取得され、 厳格な場合はエラーになるということです。

 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.変更

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.暗黙の変換

そして今、私たちは最も興味深いもの、つまりパフォーマンスに関連する問題に取り組み始めています。

JSONを解析する場合、留意すべき点が1つあります 。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.インデックス

次に、 JSONオブジェクトにインデックスを付ける方法を検討します。 最初に述べたように、XMLとは異なり、 JSONの個別のデータ型はSQL Server 2016に追加されませんでした。 したがって、任意の文字列データ型を使用して保存できます。

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.パーサーのパフォーマンス

最後に、この記事の最も興味深い部分にたどり着きます。SQL Server上のXMLと比較してJSON解析はどれくらい高速ですか？この質問に答えるために、一連のテストを準備しました。JSONおよびXML形式の2つの大きなファイルを準備します。

 /* 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 = tcvalue('(ProductID/text())[1]', 'INT') , [Name] = tcvalue('(Name/text())[1]', 'NVARCHAR(50)') , ProductNumber = tcvalue('(ProductNumber/text())[1]', 'NVARCHAR(25)') , OrderQty = tcvalue('(OrderQty/text())[1]', 'SMALLINT') , UnitPrice = tcvalue('(UnitPrice/text())[1]', 'MONEY') , ListPrice = tcvalue('(ListPrice/text())[1]', 'MONEY') , Color = tcvalue('(Color/text())[1]', 'NVARCHAR(15)') , MakeFlag = tcvalue('(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 

ここで、XQueryに対するスカラー関数JSON_VALUEのパフォーマンスを確認しましょう。

 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 

また、もう1つの興味深いニュアンスがあります。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からのデータ抽出は、XMLからの2〜10倍高速です。
• 多くの場合、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) 

映像

この情報をすべて読むのは非常に面倒なので、ファンが「聞く」ために、最近のconf：SQL Server 2016/2017：JSONからのビデオがあります。このビデオは、ほんの2、3の例がないため、投稿とは異なります。

+この記事を英語圏の聴衆と共有したい場合：SQL Server 2017：JSON

そしてあとがき...

たまたま私は非常に長い間記事を書くことを断念しました。仕事の変更、24時間年中無休の2つのプロジェクト、1杯のココアに対する定期的なフラストレーション、およびすぐにGitHubに送られる私たち自身のペットプロジェクト。そして、私は再びコミュニティと何か有用なものを共有し、技術情報の2ページ以上を読者を魅了したいという認識に至りました。

簡潔さは私のものではないことを知っています。しかし、最後まで読んでいただければ、それが役に立てば幸いです。いずれにせよ、SQL Server 2016/2017でJSONを使用したあなたの人生経験について建設的なコメントを歓迎します。最後の2つの例の速度を確認してくれてありがとう。JSONが疑われる 必ずしもそれほど速くなく、再現性を見つけるのも面白いです。