使用 JSON_VALUE 访问 SQL Server 2016 中的 JSON 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38285223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Accessing JSON Array in SQL Server 2016 using JSON_VALUE
提问by UVData
I am stuck while accessing array inside json using newly introduced JSON_VALUE function. Please consider following code -
我在使用新引入的 JSON_VALUE 函数访问 json 中的数组时卡住了。请考虑以下代码 -
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.
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?
虽然当我检查http://jsonpath.com 时，我能够检索到值。SQL Server 2016 是否使用一些不同的语法来访问 JSON 值？
回答by Igor Micev
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');
回答by Bochen Lin
You can use "CROSS APPLY" to get the phone numbers with firstName:
您可以使用“CROSS APPLY”获取带有 firstName 的电话号码：
SELECT JSON_VALUE (jsonData, '$.firstName'),p.*
FROM JsonData
CROSS APPLY
OPENJSON (JsonData, '$.phoneNumbers') WITH(type varchar(10) '$.type', number varchar (30) '$.number') p
回答by Igor Micev
SQL Server 2016 supports JSON. It's very similar, almost identical. You'll make your own comparison.
SQL Server 2016 支持 JSON。它非常相似，几乎完全相同。你会自己做比较。
You don't need to use a temp variable @Table and then make manipulations...
您不需要使用临时变量 @Table 然后进行操作...
Simply run the following queries
只需运行以下查询
SELECT JSON_VALUE( JsonData, '$.phoneNumbers[0].type' ) AS [PhoneType],
JSON_VALUE( JsonData, '$.phoneNumbers[0].number' ) AS [PhoneNumber]
FROM JsonData
WHERE ISJSON( JsonData ) > 0;
--iPhone 0123-4567-8888
SELECT JSON_VALUE( JsonData, '$.phoneNumbers[1].type' ) AS [PhoneType],
JSON_VALUE( JsonData, '$.phoneNumbers[1].number' ) AS [PhoneNumber]
FROM JsonData
WHERE ISJSON( JsonData ) > 0;
--home 0123-4567-8910
Check out these official links from Microsoft, about JSON support for more details:
查看 Microsoft 的这些官方链接，了解有关 JSON 支持的更多详细信息：
https://msdn.microsoft.com/en-us/library/dn921897.aspx
https://msdn.microsoft.com/en-us/library/dn921897.aspx
回答by Charles
All together.
全部一起。
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
Core.*
,ARRAY.[Type]
,ARRAY.[Number]
FROM
OPENJSON(@json)
WITH
(
FirstName NVARCHAR(25) '$.firstName'
,LastName NVARCHAR(25) '$.lastName'
,Age INT '$.age'
,streetAddress NVARCHAR(25) '$.address.streetAddress'
,city NVARCHAR(25) '$.address.city'
) AS Core
CROSS APPLY
OPENJSON(@json, '$.phoneNumbers')
WITH
(
[Type] NVARCHAR(25) '$.type'
,[Number] NVARCHAR(25) '$.number'
) AS ARRAY;
