Parsing JSON in SQL Server

Question

Referring to the example provided by Microsoft:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob')

when the JSON data has an array inside, for example:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, 
         "Phones": ["123","345","678"] // like here
        }   
]'

Is there any way to join the array on the parsed data to get something like this:

 Id     First Name      Last Name    Phone
 2      John            Smith        123
 2      John            Smith        345
 2      John            Smith        678

Jeroen Mostert · Accepted Answer · 2017-08-21 18:03:42Z

4

OUTER APPLY is your friend here. Let's mix up both kinds of rows for good measure.

DECLARE @json NVARCHAR(MAX)
SET @json = N'
[
  {
    "id": 2,
    "info": {
      "name": "John",
      "surname": "Smith"
    },
    "age": 25,
    "Phones": [
      "123",
      "345",
      "678"
    ]
  },
  {
    "id": 5,
    "info": {
      "name": "Jane",
      "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
  }
]'  

SELECT id, [name], [surname], age, dateOfBirth, number
FROM (
    SELECT * 
    FROM OPENJSON(@json)
    WITH (
        id          INT             'strict $.id',
        [name]      NVARCHAR(50)    '$.info.name',
        [surname]   NVARCHAR(50)    '$.info.surname',
        age         INT,
        dateOfBirth DATETIME2       '$.dob',
        Phones      NVARCHAR(MAX)   AS JSON
    )
) AS people
OUTER APPLY OPENJSON(Phones)
WITH (
    number NVARCHAR(50) '$'
)

Result:

+----+------+---------+------+-----------------------------+--------+
| id | name | surname | age  |         dateOfBirth         | number |
+----+------+---------+------+-----------------------------+--------+
|  2 | John | Smith   | 25   | NULL                        | 123    |
|  2 | John | Smith   | 25   | NULL                        | 345    |
|  2 | John | Smith   | 25   | NULL                        | 678    |
|  5 | Jane | Smith   | NULL | 2005-11-04 12:00:00.0000000 | NULL   |
+----+------+---------+------+-----------------------------+--------+

answered Aug 21, 2017 at 18:03

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

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

3 Comments

TheGameiswar Over a year ago

great solution,i was stuck at this Phones NVARCHAR(MAX) AS JSON so implemented it like values clause

Jeroen Mostert Over a year ago

@TheGameiswar: If the number of values is strictly limited (and/or known) explicitly plucking them out of the array may well be faster than a second OPENJSON. But I have zero experience timing JSON parsing, so no need to optimize just yet. :-)

TheGameiswar Over a year ago

yes you are right, just saw execution plan,original Json was costed at 0,json below outer apply was costed at 20

Niels Berglund · Accepted Answer · 2017-08-21 18:15:23Z

3

Well, you can always do something like this:

SELECT ID,  FirstName, LastName, value 
FROM OPENJSON(@json)
WITH(ID int '$.id', 
     FirstName nvarchar(50) '$.info.name',
     LastName nvarchar(50) '$.info.surname',
     Phones nvarchar(max) '$.Phones' AS Json)
CROSS APPLY OPENJSON(Phones)

Hope this helps.

answered Aug 21, 2017 at 18:15

Niels Berglund

1,70810 silver badges6 bronze badges

Comments

nejcs · Accepted Answer · 2017-08-21 18:11:00Z

You will have to do it in two passes:

Extract for each person data and leaving array of phone numbers as JSON
Use CROSS/OUTER APPLY to parse array of phone numbers for each person and join result together

Something like this:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
    { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "Phones": ["123","345","678"] },
    { "id" : 3,"info": { "name": "Jane", "surname": "Smith" }, "Phones": ["321","543"] }
]';

WITH CTE AS (
    SELECT id, firstName, lastName, phones
    FROM OPENJSON(@json)
    WITH (
        id INT 'strict $.id',  
        firstName NVARCHAR(50) '$.info.name', 
        lastName NVARCHAR(50) '$.info.surname',
        phones NVARCHAR(MAX) '$.Phones' AS JSON
    )
)
SELECT c.id, c.firstName, c.lastName, p.value as phone
FROM CTE c
    CROSS APPLY OPENJSON(c.phones) p

Srinivasa Rao · Accepted Answer · 2018-07-09 19:54:32Z

-2

You can use C# library

https://www.dotnet4techies.com/2018/07/convert-json-to-sql-format-using-csharp.html

It is a powerful tool and it maintains sql relationships from your Json if it has nested levels.

answered Jul 9, 2018 at 19:54

Srinivasa Rao

848 bronze badges

Collectives™ on Stack Overflow

Parsing JSON in SQL Server

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related