使用JSON_VALUE访问SQL Server 2016中的JSON数组 [英] Accessing JSON Array in SQL Server 2016 using JSON_VALUE
本文介绍了使用JSON_VALUE访问SQL Server 2016中的JSON数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在使用新引入的JSON_VALUE函数访问json内的数组时,我陷入了困境.请考虑以下代码-
I am stuck while accessing array inside json using newly introduced JSON_VALUE function. Please consider following code -
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='JsonData')
DROP TABLE JsonData;
go
CREATE TABLE JsonData(JsonData nvarchar(max));
DECLARE @SQL nvarchar(max);
DECLARE @Table AS TABLE(JsonPath VARCHAR(256));
INSERT INTO JsonData(JsonData)
VALUES(
'{
"firstName": "John",
"lastName" : "doe",
"age" : 26,
"address" : {
"streetAddress": "naist street",
"city" : "Nara",
"postalCode" : "630-0192"
},
"phoneNumbers": [
{
"type" : "iPhone",
"number": "0123-4567-8888"
},
{
"type" : "home",
"number": "0123-4567-8910"
}
]
}')
INSERT INTO @Table
SELECT VALUE FROM OPENJSON('{
"Path1":"$.firstName","Path2":"$.phoneNumbers[:1].number"
}') ;
SELECT @SQL=(SELECT 'UNION SELECT '''+ CAST(JsonPath AS VARCHAR(256)) +''',JSON_VALUE(JsonData,'''+a.JsonPath+''')
FROM JsonData a'
FROM @Table a
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)')
FROM @Table t;
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)
PRINT @SQL
EXEC SP_EXECUTESQL @SQL;
在这里,如果我要访问特定的电话号码,则访问该节点的常规语法不起作用.在这种情况下,我遇到以下错误
Here If I want to access specific phone number then usual syntax of accessing this node is not working. I am getting following error in this case
JSON path is not properly formatted. Unexpected character ':' is found at position 15.
尽管当我在 http://jsonpath.com 进行检查时,我仍能够检索到值. SQL Server 2016是否使用某些不同的语法来访问JSON值?
Though when I checked at http://jsonpath.com , I am able to retrieve value. Does SQL server 2016 uses some different syntax for accessing JSON values?
推荐答案
要从phoneNumbers中获取全部信息:
To get all from phoneNumbers:
DECLARE @json nvarchar(max)=
'{
"firstName": "John",
"lastName" : "doe",
"age" : 26,
"address" : {
"streetAddress": "naist street",
"city" : "Nara",
"postalCode" : "630-0192"
},
"phoneNumbers": [
{
"type" : "iPhone",
"number": "0123-4567-8888"
},
{
"type" : "home",
"number": "0123-4567-8910"
}
]
}'
SELECT [Type], [Number]
FROM OPENJSON( @json, '$.phoneNumbers' )
WITH ([Type] NVARCHAR(25) '$.type', [Number] NVARCHAR(25) '$.number');
这篇关于使用JSON_VALUE访问SQL Server 2016中的JSON数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文