SQLServer 2017 JSON初探

从SQL Server 2016 开始，SQLServer已经原生支持JSON数据格式。目前SQL支持将结果输出为JSON或从JSON中获取数据和修改JSON。

使用JSON AUTO输出数据

如下示例：

SELECT TOP 5 CompanyShortName, Country, CompanyAddress
FROM VendorBaseInfo

加上JSON AUTO即可输出JSON格式，如下：

SELECT TOP 5 CompanyShortName, Country, CompanyAddress
FROM VendorBaseInfo 
FOR JSON AUTO

仔细观察，第一个JSON对象没有返回Country属性，这是JSON的默认行为，如果值为NULL，则不返回该属性，如果希望返回包括NULL的属性，则需要使用INCLUDE_NULL_VALUES，如下：

SELECT TOP 5 CompanyShortName, Country, CompanyAddress
FROM VendorBaseInfo 
FOR JSON AUTO, INCLUDE_NULL_VALUES

JSON默认的输出是一个JSON数组，如果希望给输出的属性增加一个根节点，则需要使用ROOT()，如下：

SELECT TOP 5 CompanyShortName, Country, CompanyAddress
FROM VendorBaseInfo 
FOR JSON AUTO, INCLUDE_NULL_VALUES, ROOT('root')

JSON AUTO的默认行为

1、JSON AUTO不会将包括点(.)的属性转为对象，如下：

SELECT TOP 5 CompanyShortName AS 'Company.Name', Country, CompanyAddress
FROM VendorBaseInfo 
FOR JSON AUTO

上面的结果，属性名也只是Company.Name，如果希望输出一个对象，可使用JSON PATH，如下：

SELECT TOP 5 CompanyShortName AS 'Company.Name', Country, CompanyAddress
FROM VendorBaseInfo 
FOR JSON PATH

2、如果关联多个表，那么第一个表会作为主对象，其它表的数据会作为子数据集，如下：

SELECT TOP 5 CompanyShortName  Country, CompanyAddress,VendorContacts.Name
FROM VendorBaseInfo 
	INNER JOIN VendorContacts ON VendorBaseInfo.ID = VendorContacts.VendorID
FOR JSON AUTO

如上图所示，关联表的名称或别名，会作为子数据集的名称。使得子查询的效果也类似，如下：

SELECT TOP 5 CompanyShortName  Country, CompanyAddress,
	(
		SELECT Name 
		FROM VendorContacts 
		WHERE VendorContacts.VendorID = VendorBaseInfo.ID 
		FOR JSON AUTO
	) AS VendorContacts
FROM VendorBaseInfo 
FOR JSON AUTO

使用JSON PATH自定义返回的对象

在JSON AUTO中不会将以点分隔的属性转换为对象，而JSON PATH则可以自定义返回JSON对象，如下：

SELECT TOP 5 CompanyShortName  Country, CompanyAddress
, VendorContacts.Name AS 'VendorContacts.Name'
, VendorContacts.Position AS 'VendorContacts.Position'
FROM VendorBaseInfo 
	INNER JOIN VendorContacts ON VendorBaseInfo.ID = VendorContacts.VendorID
FOR JSON PATH

这里给VendorContacts的两个属性起了别名，返回的结果如下：

使用WITHOUT_ARRAY_WRAPPER删除方括号

上面的示例（除了使用ROOT()时）默认返回的是一个数组，如果不希望返回方括号时，可使用WITHOUT_ARRAY_WRAPPER，如下：

SELECT TOP 5 CompanyShortName  Country, CompanyAddress
, VendorContacts.Name AS 'VendorContacts.Name'
, VendorContacts.Position AS 'VendorContacts.Position'
FROM VendorBaseInfo 
	INNER JOIN VendorContacts ON VendorBaseInfo.ID = VendorContacts.VendorID
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

WITHOUT_ARRAY_WRAPPER

Dean's blog