Accessing JSON Array in SQL Server 2016 using JSON_VALUE

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');

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

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 JsonDataWHERE ISJSON( JsonData ) > 0;--iPhone 0123-4567-8888SELECT  JSON_VALUE( JsonData, '$.phoneNumbers[1].type' ) AS [PhoneType],         JSON_VALUE( JsonData, '$.phoneNumbers[1].number' ) AS [PhoneNumber]FROM JsonDataWHERE 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