SQL Server JSON：性能秘籍
5.00/5 (2投票s)
这里是您需要了解的关于 SQL Server 高性能 JSON 解析的所有信息
引言
多年来，微软一直用各种新奇的玩意儿在不同的极端之间给大家带来惊喜，久而久之，你就会习惯，并且带着一定程度的怀疑态度去等待那些更新。随着时间的推移，这种感觉只会越来越强烈，并且你会潜意识里不期待好事发生。
然而，有时结果恰恰相反。出乎意料地，微软发布了一些功能完善的功能，彻底打破了所有现有的刻板印象。起初，你可能会期待同样的“坑”和尴尬的场景，但每一分钟你都会意识到，这正是你多年来一直缺少的、功能完善的 SQL Server JSON 解析器。
如此详尽的介绍是有一定根据的，因为在微软的 Connect 网站上，支持 SQL Server JSON 操作长期以来一直是需求最旺盛的功能之一。岁月流逝，在 SQL Server 2016 发布时，这项功能出乎意料地得以实现。展望未来，可以说它的表现非常好，但微软并未止步于此，在 SQL Server 2017/2019 中，他们显著提高了这个已快速的 JSON 解析器的性能。
目录
- #1. 数据类型
- #2. 存储
- #3. 压缩/解压缩
- #4. 压缩
- #5. 列存储
- #6. 创建 JSON
- #7. 检查 JSON
- #8. JsonValue
- #9. OpenJson
- #10. 字符串拆分
- #11. 松散模式 & 严格模式
- #12. 修改
- #13. 隐式转换
- #14. 索引
- #15. 解析器性能
- #结论
- #Git
1. 数据类型
SQL Server 对 JSON 的支持对所有版本都原生可用。同时，微软并没有像 XML 那样提供单独的数据类型。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 存储数据，则每个字符 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. 存储
现在让我们看看将 JSON 存储为 NVARCHAR/VARCHAR 与将类似数据表示为 XML 相比有多大优势。此外，我们将尝试以原生格式存储 XML，并将其表示为 string。
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 实现了新的 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. 压缩
您还可以使用数据压缩来减小表的大小。以前，压缩仅在 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
PAGE 压缩使用算法查找相似的数据块并用较小的值替换它们。ROW 压缩则将它们截断到最小数据类型，并截断多余的字符。例如，我们的列是 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 索引，它在 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 中存储任何长度的 string。
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 格式是否有效，有一个 ISJSON 函数，它在是 JSON 时返回 1，不是时返回 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 及以上版本的数据库上才能工作。
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. 字符串拆分
随着 SQL Server 2016 的发布，引入了 STRING_SPLIT 函数。大家都松了一口气，觉得现在不必再费力发明方法来分割字符串了。然而，还有一个替代方案——OPENJSON 构造，我们之前已经讨论过。让我们测试几种拆分 string 的方法。
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. 松散模式 & 严格模式
从 SQL Server 2005 开始，通过使用 XML SCHEMA COLLECTION，就可以从数据库端验证 XML。我们描述 XML 的模式，然后基于该模式，我们可以验证数据。虽然 JSON 没有这样的显式功能，但有一个变通方法。
据我所知，JSON 有两种表达式类型：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. 修改
有一个 JSON_MODIFY 函数用于修改 JSON 内部的数据。示例非常简单，因此没有必要详细描述。
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 时，您需要记住一个细微之处——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 对象。如前所述，SQL Server 2016 与 XML 不同，没有为 JSON 添加单独的数据类型。因此，您可以使用任何 string 数据类型来存储它。
如果您有 XML 方面的经验，请记住，对于这种格式，SQL Server 中有几种类型的索引可以加快某些选择的速度。对于应该存储 JSON 的 string 类型，这些索引根本不存在。
唉，JSONB 没有被实现。开发团队急于发布 JSON 功能，并明确表示：“如果您觉得速度不够快，我们将在下一个主要版本中添加 JSONB。”但随着 SQL Server 2017/2019 的发布，没有任何新的东西被添加。
这时，计算列派上了用场，它们可以表示 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. 解析器性能
最后，我们来到了本文可能最有趣的部分。与 XML 相比，SQL Server 中 JSON 的解析速度有多快？为了回答这个问题，我准备了一系列测试。
我们准备两个大型的 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
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":"Sergii 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":"Sergii 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 = N'
<Users>
<User Name="Sergii 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
结果
data_type path type 2016 SP2(CU17) 2017(CU25) 2019(CU11)
---------- -------------------------------------- --------- ------------- ----------- -----------
@jsonu $[0].Age INT 1030 314 250
@jsonu_f $[0].Age INT 1014 333 290
@json $[0].Age INT 1100 393 337
@json_f $[0].Age INT 1166 444 396
@xml (Users/User[1]/Age/text())[1] INT 28037 30513 34427
@jsonu $[1].User NVARCHAR 1223 513 490
@jsonu_f $[1].User NVARCHAR 1367 647 673
@json $[1].User VARCHAR 1343 623 604
@json_f $[1].User VARCHAR 1527 790 800
@xml (Users/User[2]/@Name)[1] NVARCHAR 22487 25094 26840
@xml (Users/User[2]/@Name)[1] VARCHAR 22816 25190 26676
@jsonu $[2].Skills[0] NVARCHAR 1654 856 814
@jsonu_f $[2].Skills[0] NVARCHAR 1926 1124 1103
@json $[2].Skills[0] VARCHAR 1790 960 907
@json_f $[2].Skills[0] VARCHAR 2090 1250 1196
@xml (Users/User[3]/Skills/Skill/text())[1] VARCHAR 23530 25426 27840
还有一个有趣的细微之处：您不需要混合使用 JSON_VALUE 和 OPENJSON 调用。另外，尽量只指定解析后真正需要的列。对于 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 SP2(CU17):
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(CU25):
CPU time = 1016 ms, elapsed time = 1034 ms
CPU time = 718 ms, elapsed time = 736 ms
CPU time = 1282 ms, elapsed time = 1286 ms
2019(CU11):
CPU time = 998 ms, elapsed time = 1011 ms
CPU time = 721 ms, elapsed time = 745 ms
CPU time = 1301 ms, elapsed time = 1316 ms
*/
结论
- 与 XML 相比，从 JSON 解析数据的速度快 2 倍到 10 倍。
- JSON 存储通常比 XML 存储更冗余。
- 以 Unicode 解析 JSON 数据速度快 5-15%。
- 使用 JSON 时，与 XML 相比，可以显著降低服务器 CPU 的负载。
- 从 SQL Server 2017 开始，从 JSON 解析标量值的速度显著加快。
- 如果可以用 JSON 替换 XML，那就去做吧！
Git
您可以在我的 Git 仓库中获取所有最新的代码示例。谢谢！