在上一篇中,介绍了返回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返回。