SQLServer 2017 JSON初探

在上一篇中，介绍了返回JSON数据的方法，如果希望从JSON中读取数据，可以使用OPENJSON返回数据集。如果需要使用OPENJSON，那么数据库的兼容性级别不低于130，否则会提示找不到对象。

OPENJSON的默认架构

DECLARE @json NVARCHAR(1000)
SET @json = '[
	{
		"CompanyShortName":"保辉建筑",
		"Country":"中国",
		"CompanyAddress":"人民路100号",
		"VendorContacts":[
			{"Name":"张锋"},
			{"Name":"张天"},
			{"Name":"张宇"},
			{"Name":"张璐璐"}
		]
	},{
		"CompanyShortName":"赛博威",
		"Country":"中国",
		"CompanyAddress":"广东广州萝岗科学城软件园F1201",
		"VendorContacts":[
			{"Name":"赵六"}
		]
	}
]'

SELECT * FROM OPENJSON(@json)

这里每个对象一行记录。默认情况下，OPENJSON一共返回三列，其中type对应的意义是：

类型列的值 JSON数据类型

0 null

1 string

2 ssNoverstion

3 true/false

4 array

5 对象(object)

使用JSON路径表达式返回指定的部分

如果希望只检索JSON的部分内容，可以同时使用JSON路径，加以处理，如下：

--@json对应上面的内容
SELECT * FROM OPENJSON(@json, '$[0]')

这里第二个参数对应的是JSON路径表达式，其中$对应的是根对象，$[0]对应的是数组第一个对象，如果是$[0].VendorContacts则引用的是第一个对象的VendorContacts数组。

显示架构返回JSON数据

上面的示例，只会返回一个JSON对象，如果希望返回对象的指定属性，则可以显示指定返回数据的架构，如下：

--@json对应上面的内容
SELECT * FROM OPENJSON(@json) WITH(
	CompanyName NVARCHAR(50) '$.CompanyShortName',
	CompanyAddress NVARCHAR(100),
	Country NVARCHAR(50),
	Contacts NVARCHAR(50) '$.VendorContacts[0].Name'
)

如上图结果所示，使用WITH即可指定返回的数据架构。默认情况下，JSON键与WITH的字段名同名，如上面的CompanyAddress和Country；另外，指定了JSON路径表达式后，即可对字段进行重名称，如上面的CompanyShortName以CompanyName返回，也可从子对象中返回所需要的数据，如将VendorContacts第一个对象作为Contacts返回。

Dean's blog