Accessing JSON Array in SQL Server 2016 using JSON_VALUE

Question

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?

This is off-topic but might be useful to know.You can use DROP TABLE IF EXISTS JsonData instead of if (select) drop table.... — Jovan MSFT
– Jovan MSFT, Commented Jul 19, 2016 at 7:36

Igor Micev · Accepted Answer · 2016-07-12 09:47:14Z

35

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

answered Jul 12, 2016 at 9:47

Igor Micev

1,7401 gold badge19 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

UVData Over a year ago

Thanks again! Looks like its the closest I can get. There will be a lot of tweaks required if I do not know exact structure of JSON provided but your suggestions have helped me in right direction. I wanted to create generic solution. I wish if future updates can support proper JSON traversing.

Bochen Lin · Accepted Answer · 2017-08-14 05:09:34Z

25

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

answered Aug 14, 2017 at 5:09

Bochen Lin

4435 silver badges14 bronze badges

Comments

Igor Micev · Accepted Answer · 2016-07-10 16:34:02Z

9

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

answered Jul 10, 2016 at 16:34

Igor Micev

1,7401 gold badge19 silver badges24 bronze badges

5 Comments

UVData Over a year ago

Hi, Thanks for help! I needed @table variable as my original solution needs a parameter in which app can pass any JSON path. Data against that passed path should be returned from a (MUCH) complex JSON. Its a simplified example and I basically need to return all elements if array is provided as argument. For example, $.phoneNumbers[:].number should be returning all numbers in that array.

Igor Micev Over a year ago

Aham, then you're very close to the solution. I'd use the above queries (or paths) in a dynamic sql query as you're trying in your query. Know that $.phoneNumbers[:] in T-SQL JSON is actually $.phoneNumbers[*]

UVData Over a year ago

Thanks again! I got following error when I tried to use * - Msg 13607, Level 16, State 4, Line 4 JSON path is not properly formatted. Unexpected character '*' is found at position 15. If you could please point me to any link explaining "TSQL JSON" that'd be really helpful. I couldn't find any such reference

Igor Micev Over a year ago

The '[*]' is then a wrong syntax. I didn't test it (assuming that [:] works, and expected [*] to work too. See my post below for how to get all rows. You have to use OPENJSON then.

ListenSoftware Louise Ai Agent Over a year ago

thanks, JSON_VALUE(c.value,'$.stock_asset_rents[0].sequence_num') as [stock_asset_rents_sequence_num], worked to access the json array

Charles · Accepted Answer · 2018-10-26 12:53:28Z

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;

Jeremy Caney · Accepted Answer · 2024-11-22 03:33:57Z

The question has already been answered. However, the below helped me, if anyone else is trying to query JSON using T-SQL where you have an array inside another other one.

    DECLARE @rtalla nvarchar(max)=
    '{
      "firstName": "Robert",
      "lastName" : "Talla",
      "age"      : 30,
      "address"  : {
        "streetAddress": "Raavan street",
        "city"         : "LankaPally",
        "postalCode"   : "00099-0011"
      },
      "phoneNumbers": [
        {
          "phtype"  : "iPhone",
          "number": "0123-4567-8888",
          "users" : [
             {
               "relation":"wife",
               "allow":"YES"
             },
             {
               "relation":"daughter",
               "allow":"NO"
             }
           ]
        },
        {
          "phtype"  : "home",
          "number": "0123-4567-8910",
          "users" : [
             {
               "relation":"wife",
               "allow":"YES"
             },
             {
               "relation":"daughter",
               "allow":"YES"
             }
           ]
        },
        {
          "phtype"  : "work",
          "number": "0123-4567-9991",
          "users" : [
             {
               "relation":"wife",
               "allow":"NO"
             },
             {
               "relation":"daughter",
               "allow":"NO"
             }
           ]
        }
      ]
    }'


SELECT
     ADDRS.firstName
    ,ADDRS.lastName
    ,ADDRS.age
    ,ADDRS.streetAddress
    ,ADDRS.city

    ,PHTYP.phtype
    ,PHTYP.number

    ,USR.relation
    ,USR.allow
FROM

    OPENJSON(@rtalla)
        WITH
        (
            firstName NVARCHAR(25) '$.firstName'
           ,lastName NVARCHAR(25) '$.lastName'
           ,age INT '$.age'
           ,streetAddress NVARCHAR(25) '$.address.streetAddress'
           ,city NVARCHAR(25) '$.address.city'
           ,phoneNumbers NVARCHAR(MAX) AS JSON 
        ) AS ADDRS

        CROSS APPLY
        OPENJSON(phoneNumbers)     
        WITH (
                phtype NVARCHAR(25) '$.phtype'
               ,number NVARCHAR(25) '$.number'
               ,users NVARCHAR(MAX) AS JSON 
             ) AS PHTYP

            CROSS APPLY 
            OPENJSON(users)
            WITH (
                    relation NVARCHAR(25) '$.relation'
                   ,allow NVARCHAR(25) '$.allow'
                ) AS USR

WHERE USR.allow= N'NO'

Collectives™ on Stack Overflow

Accessing JSON Array in SQL Server 2016 using JSON_VALUE

5 Answers 5

1 Comment

Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related