sqlsql-serverjsonsql-server-2016

Accessing JSON Array in SQL Server 2016 using JSON_VALUE


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?


Solution

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