Geek Answers Handbook

Access JSON array in SQL Server 2016 using JSON_VALUE

I am stuck while accessing an array inside json using the newly introduced JSON_VALUE function. Please consider the 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 a specific phone number, then the usual syntax for accessing this node does not work. I get the following error in this case

 JSON path is not properly formatted. Unexpected character ':' is found at position 15.

Although, when I checked http://jsonpath.com , I can get the value. Does SQL Server 2016 use a different syntax to access JSON values?

+6
source share
3 answers

To get everything 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');
+6
source

You can use "CROSS APPLY" to get 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
+4
source

SQL Server 2016 supports JSON. It is very similar, almost identical. You will make your comparison.

You do not need to use the temporary variable @Table, and then do the manipulations ...

Just do 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 Microsoft links for JSON support for more details:

https://msdn.microsoft.com/en-us/library/dn921897.aspx

https://msdn.microsoft.com/en-us/library/dn921898.aspx

+2
source

More articles:


All Articles