Accessing JSON Array in SQL Server 2016 using JSON_VALUE
Solution 1
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');
Solution 2
You can use "CROSS APPLY" to get the phone numbers with firstName:
SELECT JSON_VALUE (jsonData, '$.firstName'),p.*
FROM JsonData
CROSS APPLY
OPENJSON (JsonData, '$.phoneNumbers') WITH(type varchar(10) '$.type', number varchar (30) '$.number') p
Solution 3
SQL Server 2016 supports JSON. It's very similar, almost identical. You'll make your own comparison.
You don't need to use a temp variable @Table and then make manipulations...
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:
https://msdn.microsoft.com/en-us/library/dn921897.aspx
https://msdn.microsoft.com/en-us/library/dn921898.aspx
UVData
Updated on October 27, 2020Comments
-
UVData over 3 years
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.
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?