SQL Server 2016 中的原生 JSON 支持

Manjuke Fernando

5.00/5 (4投票s)

2016年9月8日

CPOL

7分钟阅读

25285

对 SQL Server 2016 中原生 JSON 支持的简要介绍。

引言

在现代 Web 开发中，JSON 是当您需要应用程序之间交换信息时最知名的技术之一。在 JSON 之前，XML 被用于（并且仍在各种应用程序和技术中使用）完成这项工作。但与 XML 相比，JSON 的冗余度较低（像 XML 一样，JSON 没有闭合标签），并且 JSON 数据的大小会相对较小，最终使数据流更快。也许 JSON 相对于 XML 最显著的优势是 JSON 是 JavaScript 的一个子集，因此解析和打包它的代码非常自然地融入 JavaScript 代码中。这对于 JavaScript 程序来说似乎非常有益，而且这恰好是 JSON 在 Web 应用程序开发人员中如此受欢迎的一个好原因。

然而，使用 XML 还是 JSON 取决于个人偏好和需求。

在 SQL Server 2016 之前，早期版本没有对 JSON 的任何支持。因此，原生 JSON 支持是 Microsoft 在 SQL Server 2016 中引入的新功能之一。

在 SQL Server 2016 之前，已有其他数据库支持 JSON。

MongoDB
CouchDB
eXistDB
Elastisearch
BaseX
MarkLogic
OrientDB
Oracle Database
PostgresSQL
Riak

但我在这篇文章中的主要重点将是 SQL Server 2016 中的 JSON 支持。

为了支持 JSON，SQL 2016 中引入了以下内置函数：

ISJSON
JSON_VALUE
JSON_QUERY
JSON_MODIFY
OPENJSON
FOR JSON

SQL Server 中没有用于 JSON 的特定数据类型（与 XML 不同）。当您在 SQL Server 中处理 JSON 时，必须使用 NVARCHAR。

这是我们将 JSON 数据分配给变量的方式。

DECLARE @varJData AS NVARCHAR(4000)
SET @varJData = 
N'{
	"OrderInfo":{
		"Tag":"#ONLORD_12546_45634",
		"HeaderInfo":{
			"CustomerNo":"CUS0001",
			"OrderDate":"04-Jun-2016",
			"OrderAmount":1200.00,
			"OrderStatus":"1",
			"Contact":["+0000 000 0000000000", 
			"info@abccompany.com", "finance@abccompany.com"]
		},
		"LineInfo":[
			{"ProductNo":"P00025", "Qty":3, "Price":200},
			{"ProductNo":"P12548", "Qty":2, "Price":300}
		]
	}
}'

我们将通过一些示例仔细研究上述函数的使用方法。

ISJSON()

顾名思义，ISJSON 函数用于验证给定的 JSON 字符串。该函数将返回一个 INT 值，如果提供的 string 格式正确为 JSON，则返回 1，否则返回 0。

示例

SELECT ISJSON(@varJData)

JSON_VALUE()

JSON_VALUE 函数可用于从 JSON string 返回标量值。

示例

DECLARE @varJData AS NVARCHAR(4000)
SET @varJData = 
N'{
	"OrderInfo":{
		"Tag":"#ONLORD_12546_45634",
		"HeaderInfo":{
			"CustomerNo":"CUS0001",
			"OrderDate":"04-Jun-2016",
			"OrderAmount":1200.00,
			"OrderStatus":"1",
			"Contact":["+0000 000 0000000000", 
			"info@abccompany.com", "finance@abccompany.com"]
		},
		"LineInfo":[
			{"ProductNo":"P00025", "Qty":3, "Price":200},
			{"ProductNo":"P12548", "Qty":2, "Price":300}
		]
	}
}'

SELECT JSON_VALUE(@varJData,'$.OrderInfo.Tag')
SELECT JSON_VALUE(@varJData,'$.OrderInfo.HeaderInfo.CustomerNo')

请注意，提供的键是区分大小写的，如果将“Tag”而不是“tag”传递，它将返回 NULL，因为函数找不到该键。

SELECT JSON_VALUE(@varJData,'$.OrderInfo.tag') /* This will returns NULL */

在这种情况下，如果您需要查看确切的错误或根本原因，则需要在键之前指定“strict”。例如：

SELECT JSON_VALUE(@varJData,'strict $.OrderInfo.tag') /* This will thorw an Error */

这将返回以下错误消息，而不是返回 NULL 值。

Msg 13608, Level 16, State 1, Line 62
在指定的 JSON 路径上找不到属性。

此外，JSON_VALUE 可用于从简单的数组（而不是对象数组）中获取元素。在我们示例的 JSON 中，有两个数组，“Contact”和“LineInfo”，其中第一个是简单的 string 数组，第二个是对象数组。

假设我们需要仅从联系人详细信息中获取电话号码，我们可以使用以下查询：

SELECT JSON_VALUE(@varJData,'$.OrderInfo.HeaderInfo.Contact[0]')

另外，当我们需要从数组元素中获取属性时，也可以使用此功能。假设我们需要从“LineInfo”的第一个元素中获取产品编号，我们可以使用：

SELECT JSON_VALUE(@varJData, '$.OrderInfo.LineInfo[0].ProductNo')

JSON_QUERY()

当您需要从 JSON 中提取数据数组或对象时，使用 JSON_QUERY 函数。在这种情况下，我们可以按如下方式提取作为数组的联系人详细信息和线路详细信息：

SELECT JSON_QUERY(@varJData, '$.OrderInfo.HeaderInfo.Contact')
SELECT JSON_QUERY(@varJData, '$.OrderInfo.LineInfo')

此函数也可用于从对象数组中获取特定元素。假设我们想获取“LineInfo”部分中第二个产品的详细信息，我们可以使用以下方法：

SELECT JSON_QUERY(@varJData, '$.OrderInfo.LineInfo[1]')

**注意：如果 JSON 文本包含重复的属性 - 例如，同一级别上有两个同名的键 - JSON_VALUE 和 JSON_QUERY 函数将返回第一个匹配路径的值。

JSON_MODIFY()

JSON_MODIFY 函数用于更新 JSON string 中属性的值，并返回更新后的 JSON string。此函数的语法如下：

JSON_MODIFY(expression, path, new_value)

使用此函数，您可以更新、插入、删除或追加一个值到 JSON string。我们将使用上面的 JSON string 查看这些操作的每个示例。

更新现有值

要更新现有值，您需要提供确切的路径，后跟要更新为的值。

SET @varJData = JSON_MODIFY(@varJData,'$.OrderInfo.Tag','#NEWTAG_00001')
SELECT JSON_VALUE(@varJData,'$.OrderInfo.Tag')

删除现有值

要删除现有值，您需要提供确切的路径，后跟值“NULL”。

SET @varJData = JSON_MODIFY(@varJData,'$.OrderInfo.Tag',NULL)
SELECT JSON_VALUE(@varJData,'$.OrderInfo.Tag')
PRINT @varJData

当打印值时，您可以看到“Tag”属性已从 JSON string 中完全删除。

插入值

要插入属性以及值，您需要提供 JSON 中当前不存在的路径，后跟值。如果提供的路径已存在，则现有值将被新值替换。新值始终会添加到现有 JSON string 的末尾。

SET @varJData = JSON_MODIFY(@varJData,'$.OrderInfo.Batch','#B_100000')
SELECT JSON_VALUE(@varJData,'$.OrderInfo.Batch')
PRINT @varJData

追加值

要向 JSON 中的现有数组追加内容，您需要在路径前使用“append”。假设我们需要向以下数组添加另一个元素：

SET @varJData = JSON_MODIFY
(@varJData, 'append $.OrderInfo.HeaderInfo.Contact','+0010 111 1111111111')
SELECT JSON_QUERY(@varJData,'$.OrderInfo.HeaderInfo.Contact')

JSON_MODIFY 只能一次处理一个值。因此，如果需要在单个查询中更改多个值，则需要多次使用 JSON_MODIFY 函数。假设我们需要更改“LineInfo”中第一个产品的“ProductNo”和“Price”，我们可以使用以下语法。

SET @varJData =
	JSON_MODIFY( 
		JSON_MODIFY(@varJData,'$.OrderInfo.LineInfo[0].ProductNo','P99999')
		,'$.OrderInfo.LineInfo[0].Price'
		,150
	)

FOR JSON

FOR JSON 功能用于将 SQL 表格数据导出为 JSON 数据。这非常类似于“FOR XML”的功能。每一行将被格式化为一个 JSON 对象，单元格中的值将作为这些相应 JSON 对象的属性值生成。列名（或别名）将用作键名。根据提供的选项，“FOR JSON”有两种用法变体：

FOR JSON AUTO - 这将根据查询中使用的表层次结构自动创建嵌套的 JSON 子数组。（类似于 FOR XML AUTO）
FOR JSON PATH - 这使您可以使用列名/别名定义输出 JSON 的结构。如果在列别名中使用点分隔的名称，JSON 属性将遵循命名约定。（这类似于 FOR XML PATH，您可以使用斜杠分隔的路径）

为了说明上述概念，我们需要准备一些示例数据。请使用以下脚本生成必要的数据。

--== Generate Required Schemas ==--
CREATE TABLE OrderHeader(
	TAG					VARCHAR(24)
	,ORD_NO				VARCHAR(10)
	,CUST_NO			VARCHAR(8)
	,ORD_DATE			DATE
	,ORD_AMOUNT			MONEY
	,ORD_STATUS			TINYINT
)

CREATE TABLE OrderLine(
	ORD_NO				VARCHAR(10)
	,LINE_NO			INT
	,PROD_NO			VARCHAR(8)
	,ORD_QTY			INT
	,ITEM_PRICE			MONEY
)

CREATE TABLE CustomerContact(
	CONT_ID				INT
	,CUST_NO			VARCHAR(8)
	,CONTACT_DETAILS	VARCHAR(24)
)

--== Insert Sample Data ==--
INSERT INTO dbo.OrderHeader(TAG,ORD_NO,CUST_NO,ORD_DATE,ORD_AMOUNT,ORD_STATUS)
VALUES('#ONLORD_12546_45634','ORD_1021','CUS0001','04-Jun-2016',1200.00,1)

INSERT INTO dbo.OrderLine(ORD_NO,LINE_NO,PROD_NO,ORD_QTY,ITEM_PRICE)
VALUES ('ORD_1021',1,'P00025',3,200.00), ('ORD_1021',1,'P12548',2,300.00)

INSERT INTO dbo.CustomerContact(CONT_ID, CUST_NO, CONTACT_DETAILS)
VALUES (1,'CUS0001','+0000 000 0000000000') ,_
(2,'CUS0001','info@abccompany.com'),(3,'CUS0001','finance@abccompany.com')

使用 FOR JSON AUTO 将数据导出为 JSON

SELECT 
    H.TAG
   ,H.ORD_NO
   ,H.CUST_NO
   ,H.ORD_DATE
   ,H.ORD_AMOUNT
   ,H.ORD_STATUS
   ,L.ORD_NO
   ,L.LINE_NO
   ,L.PROD_NO
   ,L.ORD_QTY
   ,L.ITEM_PRICE
FROM
	dbo.OrderHeader AS H
	JOIN dbo.OrderLine AS L
		ON L.ORD_NO = H.ORD_NO
WHERE
	H.ORD_NO = 'ORD_1021'
FOR JSON AUTO

您将获得如下所示的结果：

[
    {
        "TAG":"#ONLORD_12546_45634",
        "ORD_NO":"ORD_1021",
        "CUST_NO":"CUS0001",
        "ORD_DATE":"2016-06-04",
        "ORD_AMOUNT":1200.0000,
        "ORD_STATUS":1,
        "L":[
            {"ORD_NO":"ORD_1021","LINE_NO":1,
            "PROD_NO":"P00025","ORD_QTY":3,"ITEM_PRICE":200.0000},
            {"ORD_NO":"ORD_1021","LINE_NO":1,
            "PROD_NO":"P12548","ORD_QTY":2,"ITEM_PRICE":300.0000}
        ]
    }
]

如前所述，“FOR JSON AUTO”将简单地将列名或别名转换为键并生成 JSON。表别名将用于创建子数组。

但是，通过调整上述 select 语句，我们可以获得与我们之前示例中类似的结果集：

SELECT 
    H.TAG AS Tag
   ,H.ORD_NO AS OrderNo
   ,H.CUST_NO AS CustNo
   ,H.ORD_DATE AS OrderDate
   ,H.ORD_AMOUNT AS OrderAmount
   ,H.ORD_STATUS AS  OrderStatus
   ,LineInfo.ORD_NO AS [OrderNo]
   ,LineInfo.LINE_NO AS [LineNo]
   ,LineInfo.PROD_NO AS [ProdNo]
   ,LineInfo.ORD_QTY AS [Qty]
   ,LineInfo.ITEM_PRICE AS [ItemPrice]
FROM
	dbo.OrderHeader AS H
	JOIN dbo.OrderLine AS LineInfo
		ON LineInfo.ORD_NO = H.ORD_NO
WHERE
	H.ORD_NO = 'ORD_1021'
FOR JSON AUTO, ROOT ('OrderInfo')

然后，我们将能够获得以下 JSON string。

{
    "OrderInfo":[
        {
            "Tag":"#ONLORD_12546_45634",
            "OrderNo":"ORD_1021",
            "CustNo":"CUS0001",
            "OrderDate":"2016-06-04",
            "OrderAmount":1200.0000,
            "OrderStatus":1,
            "LineInfo":[
                {"OrderNo":"ORD_1021","LineNo":1,
                "ProdNo":"P00025","Qty":3,"ItemPrice":200.0000},
                {"OrderNo":"ORD_1021","LineNo":1,
                "ProdNo":"P12548","Qty":2,"ItemPrice":300.0000}
            ]
        }
    ]
}

使用 FOR JSON PATH 将数据导出为 JSON

我们可以使用 FOR JSON PATH 功能轻松地按照我们要求的方式格式化输出 JSON。但是，在使用“FOR JSON PATH”提取数据时存在一个限制，即您不能在多个列中拥有重复的列名（或别名）。这将导致错误。

我们将看到如何使用“FOR JSON PATH”获取详细信息。

SELECT 
    H.TAG
   ,H.ORD_NO
   ,H.CUST_NO
   ,H.ORD_DATE
   ,H.ORD_AMOUNT
   ,H.ORD_STATUS
   --,L.ORD_NO	--If this line is uncommented it will throw an error	
   ,L.LINE_NO
   ,L.PROD_NO
   ,L.ORD_QTY
   ,L.ITEM_PRICE
FROM
	dbo.OrderHeader AS H
	JOIN dbo.OrderLine AS L
		ON L.ORD_NO = H.ORD_NO
WHERE
	H.ORD_NO = 'ORD_1021'
FOR JSON PATH

我们将获得以下 JSON 结果：

[
    {
        "TAG":"#ONLORD_12546_45634",
        "ORD_NO":"ORD_1021",
        "CUST_NO":"CUS0001",
        "ORD_DATE":"2016-06-04",
        "ORD_AMOUNT":1200.0000,
        "ORD_STATUS":1,
        "LINE_NO":1,
        "PROD_NO":"P00025",
        "ORD_QTY":3,
        "ITEM_PRICE":200.0000
    },
    {
        "TAG":"#ONLORD_12546_45634",
        "ORD_NO":"ORD_1021",
        "CUST_NO":"CUS0001",
        "ORD_DATE":"2016-06-04",
        "ORD_AMOUNT":1200.0000,
        "ORD_STATUS":1,
        "LINE_NO":1,
        "PROD_NO":"P12548",
        "ORD_QTY":2,
        "ITEM_PRICE":300.0000
    }
]

使用“FOR JSON PATH”的优势在于您可以控制结构，使用列名/别名。当使用点分隔的别名时，JSON 属性将遵循命名约定。请考虑以下查询及其结果。

SELECT 
    H.TAG		AS 'HeaderInfo.Tag'
   ,H.ORD_NO		AS 'HeaderInfo.OrderNo'
   ,H.CUST_NO		AS 'HeaderInfo.CustNo'
   ,H.ORD_DATE		AS 'HeaderInfo.OrderDate'
   ,H.ORD_AMOUNT	AS 'HeaderInfo.OrderAmount'
   ,H.ORD_STATUS	AS 'HeaderInfo.OrderStatus'
   ,L.ORD_NO		AS 'LineInfo.OrderNo'
   ,L.LINE_NO		AS 'LineInfo.LineNo'
   ,L.PROD_NO		AS 'LineInfo.ProdNo'
   ,L.ORD_QTY		AS 'LineInfo.Qty'
   ,L.ITEM_PRICE	AS 'LineInfo.ItemPrice'
FROM
	dbo.OrderHeader AS H
	JOIN dbo.OrderLine AS L
		ON L.ORD_NO = H.ORD_NO
WHERE
	H.ORD_NO = 'ORD_1021'
FOR JSON PATH

您将看到以下 JSON 结果。

[
    {
        "HeaderInfo":{
            "Tag":"#ONLORD_12546_45634",
            "OrderNo":"ORD_1021",
            "CustNo":"CUS0001",
            "OrderDate":"2016-06-04",
            "OrderAmount":1200.0000,
            "OrderStatus":1
        },
        "LineInfo":{"OrderNo":"ORD_1021","LineNo":1,_
        "ProdNo":"P00025","Qty":3,"ItemPrice":200.0000}
    },
    {
        "HeaderInfo":{
            "Tag":"#ONLORD_12546_45634",
            "OrderNo":"ORD_1021",
            "CustNo":"CUS0001",
            "OrderDate":"2016-06-04",
            "OrderAmount":1200.0000,
            "OrderStatus":1
        },
        "LineInfo":{"OrderNo":"ORD_1021","LineNo":1,_
        "ProdNo":"P12548","Qty":2,"ItemPrice":300.0000}
    }
]

OPENJSON

OPENJSON 是一个表值函数，它会遍历给定的 JSON string 并返回一个包含其内容的关联表。它将遍历 JSON 对象数组、元素并为每个元素生成一行。此功能有两种变体：

无预定义架构，其中值将作为键值对返回，包括其类型以识别返回的是什么类型的值。
有预定义的架构。此架构将在 OPENJSON 语句中由我们提供。

无预定义架构的 OPENJSON

我们将使用以下 JSON 数据 string 来找出基于数据类型将返回的类型。

{ "Null Data":null, "String Data":"Some String Data", "Numeric Data": 1000.00, "Boolean Data": true, "Array Data":["A","B","C"], "Object Data":{"SomeKey":"Some Value"} }

DECLARE @vJSON AS NVARCHAR(4000) = N'{
	"Null Data":null,
	"String Data":"Some String Data",
	"Numeric Data": 1000.00,
	"Boolean Data": true,
	"Array Data":["A","B","C"],
	"Object Data":{"SomeKey":"Some Value"}
	}';  
  
SELECT * FROM OPENJSON(@vJSON)

使用一套更真实的 JSON 数据。

DECLARE @vJSON AS NVARCHAR(4000) = N'{
	"Tag":"#ONLORD_12546_45634",
	"OrderNo":"ORD_1021",
	"CustNo":"CUS0001",
	"OrderDate":"2016-06-04",
	"OrderAmount":1200.0000,
	"OrderStatus":1
}';  
  
SELECT * FROM OPENJSON(@vJSON)

带预定义架构的 OPENJSON

我们将使用与上一个示例相同的 JSON string，并使用预定义的架构生成结果集。

DECLARE @vJSON AS NVARCHAR(4000) = N'{
	"Tag":"#ONLORD_12546_45634",
	"OrderNo":"ORD_1021",
	"CustNo":"CUS0001",
	"OrderDate":"2016-06-04",
	"OrderAmount":1200.0000,
	"OrderStatus":1
}';  
  
SELECT * FROM OPENJSON(@vJSON) WITH(
	Tag				VARCHAR(24)
	,OrderNo		VARCHAR(8)
	,CustNo			VARCHAR(8)
	,OrderDate		DATE
	,OrderAmount	MONEY
	,OrderStatus	INT
)

这基本上是 SQL 2016 原生支持 JSON 数据的内容。希望这对您有所帮助。