Parse Nested JSON to Azure SQL table

Question

I've written a function that requests some data from an API and dumps it in a Storage Account. Then I'm accesing the data in Azure SQL DB using this method. Everything works as intended, but I'm left with the following looking table:

+-----+-----------------------------------------------------------------------------------------------------------------------------+--------+
| key |                                                             value                                                           |   type |
+-----+-----------------------------------------------------------------------------------------------------------------------------+--------+
|   0 |    {"Localnumber": "1200", "Name": "Hovednummer - IT", "Description": null, "Email": "", "PhoneNumbers": [], "Phones": ...} |      5 |
+-----+-----------------------------------------------------------------------------------------------------------------------------+--------+

Where the JSON value looks like this:

[
  {
    "Localnumber": "626",
    "Name": "name",
    "Description": null,
    "Email": "email",
    "PhoneNumbers": [
      {
        "Number": "88888888",
        "LineName": null,
        "BelongsTo": "626",
        "GotoLocalNumber": null
      },
      {
        "Number": "12345678",
        "LineName": null,
        "BelongsTo": "626",
        "GotoLocalNumber": null
      }
    ],
    "Phones": [
      {
        "LocalNumber": "200018",
        "Name": null,
        "Type": 21,
        "MAC": null,
        "BelongsTo": "626",
        "description": "Myfone"
      },
      {
        "LocalNumber": "200273",
        "Name": null,
        "Type": 8,
        "MAC": "40859694",
        "BelongsTo": "626",
        "description": "mobile"
      },
      {
        "LocalNumber": "200449",
        "Name": null,
        "Type": 23,
        "MAC": null,
        "BelongsTo": "626",
        "description": "test"
      }
    ],
    "Info": [
      {
        "Type": 0,
        "Label": null,
        "Value": null
      }
    ],
    "Department": {
      "Name": "name",
      "Street": "Street",
      "Zipcode": "1111",
      "City": "City",
      "Cvr": "123456798",
      "Contact": "Contact",
      "Email": "email",
      "Ean": null,
      "Iref": null
    }
  }
]

How can I insert this into a new table, so it will look something like this for each JSON value:

+-------------+---------+-------------+-------+---------------------+-----------------------+------------------------+------------------------------+--------------------+-------------+-------------+------------+------------------+--------------------+-----------+------------+------------+-----------------+-------------------+--------------------+-----------------+----------------+--------------------+-------------------+----------------+-----------------+
| Localnumber |  Name   | Description | Email | PhoneNumbers_Number | PhoneNumbers_LineName | PhoneNumbers_BelongsTo | PhoneNumbers_GotoLocalNumber | Phones_LocalNumber | Phones_Name | Phones_Type | Phones_MAC | Phones_BelongsTo | Phones_description | Info_Type | Info_Label | Info_Value | Department_Name | Department_Street | Department_Zipcode | Department_City | Department_Cvr | Department_Contact | Department_Email  | Department_Ean | Department_Iref |
+-------------+---------+-------------+-------+---------------------+-----------------------+------------------------+------------------------------+--------------------+-------------+-------------+------------+------------------+--------------------+-----------+------------+------------+-----------------+-------------------+--------------------+-----------------+----------------+--------------------+-------------------+----------------+-----------------+
|        1213 | Z3C (1) | NULL        | NULL  | 20813221            | NULL                  | 1213                   | NULL                         | NULL               | NULL        | NULL        | NULL       | NULL             | NULL               | NULL      | NULL       | NULL       | NULL            | NULL              | NULL               | NULL            | NULL           | NULL               | NULL              | NULL           | NULL            |
|        1213 | Z3C (1) | NULL        | NULL  | NULL                | NULL                  | NULL                   | NULL                         | 200013             | NULL        | 21          | NULL       | 1213             | Myfone             | NULL      | NULL       | NULL       | NULL            | NULL              | NULL               | NULL            | NULL           | NULL               | NULL              | NULL           | NULL            |
|        1213 | Z3C (1) | NULL        | NULL  | NULL                | NULL                  | NULL                   | NULL                         | 200270             | NULL        | 8           | 20813221   | 1213             | Mobil (20813221)   | NULL      | NULL       | NULL       | NULL            | NULL              | NULL               | NULL            | NULL           | NULL               | NULL              | NULL           | NULL            |
|        1213 | Z3C (1) | NULL        | NULL  | NULL                | NULL                  | NULL                   | NULL                         | NULL               | NULL        | NULL        | NULL       | NULL             | NULL               | 0         | NULL       | NULL       | NULL            | NULL              | NULL               | NULL            | NULL           | NULL               | NULL              | NULL           | NULL            |
|        1213 | Z3C (1) | NULL        | NULL  | NULL                | NULL                  | NULL                   | NULL                         | NULL               | NULL        | NULL        | NULL       | NULL             | NULL               | NULL      | NULL       | NULL       | Obton A/S       | Silkeborgvej 2    | 8000               | Aarhus          | 31596106       | Søren Thygesen     | [email protected] | NULL           | NULL            |
+-------------+---------+-------------+-------+---------------------+-----------------------+------------------------+------------------------------+--------------------+-------------+-------------+------------+------------------+--------------------+-----------+------------+------------+-----------------+-------------------+--------------------+-----------------+----------------+--------------------+-------------------+----------------+-----------------+

EDIT

So the following query helped me to achieve what I was searching for.

SELECT GO.LocalNumber
    ,GO.Name
    ,GO.Description
    ,GO.Email
    ,json.Number
    ,json.LineName
    ,json.Belongsto
    ,json.GoToLocalNumber
    ,go2.LocalNumber
    ,go2.Name
    ,go2.Type
    ,go2.MAC
    ,go2.BelongsTo
    ,go2.description 'description_phones'
    ,go3.Type 'Info_Type'
    ,go3.Label
    ,go3.Value
    ,go4.Name
    ,go4.Street
    ,go4.Zipcode
    ,go4.City
    ,go4.Cvr
    ,go4.Contact
    ,go4.Email
    ,go4.Ean
    ,go4.Iref
FROM openrowset(BULK 'flexfoneoutputblob/data', DATA_SOURCE = 'FlexfoneBlobStorage', SINGLE_CLOB) AS AzureBlob
CROSS APPLY OPENJSON(BulkColumn) WITH (
        Localnumber INT
        ,Name NVARCHAR(250)
        ,Description NVARCHAR(250)
        ,Email NVARCHAR(250)
        ,PhoneNumbers NVARCHAR(MAX) AS json
        ,Phones NVARCHAR(MAX) AS json
        ,Info NVARCHAR(MAX) AS json
        ,Department NVARCHAR(MAX) AS json
        ) AS GO
CROSS APPLY OPENJSON(PhoneNumbers) WITH (
        Number NVARCHAR(20)
        ,LineName NVARCHAR(20)
        ,BelongsTo NVARCHAR(10)
        ,GotoLocalNumber NVARCHAR(20)
        ) AS json
CROSS APPLY OPENJSON(Phones) WITH (
        LocalNumber NVARCHAR(20)
        ,Name NVARCHAR(50)
        ,Type INT
        ,MAC NVARCHAR(20)
        ,BelongsTo NVARCHAR(20)
        ,description NVARCHAR(30)
        ) AS go2
CROSS APPLY OPENJSON(Info) WITH (
        Type INT
        ,Label NVARCHAR(20)
        ,Value NVARCHAR(20)
        ) AS go3
CROSS APPLY OPENJSON(Department) WITH (
        Name NVARCHAR(20)
        ,Street NVARCHAR(20)
        ,Zipcode NVARCHAR(20)
        ,City NVARCHAR(20)
        ,Cvr NVARCHAR(20)
        ,Contact NVARCHAR(20)
        ,Email NVARCHAR(20)
        ,Ean NVARCHAR(20)
        ,Iref NVARCHAR(20)
        ) AS go4

For each the column data of value, get the json string, set the json string as parameter, then insert these data into the table. Example: sqlshack.com/how-to-parse-json-in-sql-server — Leon Yue
– Leon Yue, Commented May 26, 2021 at 7:30
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K
– Dale K, Commented May 26, 2021 at 8:01
Would you not want Phones and Department in separate tables? — Charlieface
– Charlieface, Commented May 26, 2021 at 9:45
@Charlieface Yes, I do. I will probably try to create several tables from this. Just not sure how to create proper keys for this, so I'll with one large table. — Artem
– Artem, Commented May 26, 2021 at 10:01
@Charlieface realized I can just use one of the uniqeu columns in both tables. — Artem
– Artem, Commented May 26, 2021 at 10:59

Charlieface · Accepted Answer · 2021-05-27 14:31:39Z

2

If you wanted to put it into separate tables, you would use SCOPE_IDENTITY to get the previous insert's ID.

Because the root object is in an array, you need to change the path to $[0]

DECLARE @json nvarchar(max) = (
    SELECT BulkColumn
    FROM openrowset(BULK 'flexfoneoutputblob/data', DATA_SOURCE = 'FlexfoneBlobStorage', SINGLE_CLOB)
);

SELECT LocalNumber
    ,Name
    ,Description
    ,Email
FROM OPENJSON(@json, '$[0]') WITH (
        Localnumber INT
        ,Name NVARCHAR(250)
        ,Description NVARCHAR(250)
        ,Email NVARCHAR(250)
        ) AS G;

DECLARE @id int = SCOPE_IDENTITY();

INSERT PhoneNumber
    (GId, Number, LineName, BelongsTo, GotoLocalNumber)
SELECT @id, Number, LineName, BelongsTo, GotoLocalNumber
FROM OPENJSON(@json, '$[0].PhoneNumbers') WITH (
        Number NVARCHAR(20)
        ,LineName NVARCHAR(20)
        ,BelongsTo NVARCHAR(10)
        ,GotoLocalNumber NVARCHAR(20)
        ) AS json;

INSERT Phone
    (GId, LocalNumber, Name, Type, MAC, BelongsTo, description)
SELECT @id, LocalNumber, Name, Type, MAC, BelongsTo, description
FROM OPENJSON(@json, '$[0].Phones') WITH (
        LocalNumber NVARCHAR(20)
        ,Name NVARCHAR(50)
        ,Type INT
        ,MAC NVARCHAR(20)
        ,BelongsTo NVARCHAR(20)
        ,description NVARCHAR(30)
        ) AS go2;

INSERT Info
    (GId, Type, Label, Value)
SELECT @id, Type, Label, Value
FROM OPENJSON(@json, '$[0].Info') WITH (
        Type INT
        ,Label NVARCHAR(20)
        ,Value NVARCHAR(20)
        ) AS go3

INSERT Department
    (GId, Name, Street, Zipcode, City, Cvr, Contact, Email, Ean, Iref)
SELECT @id, Name, Street, Zipcode, City, Cvr, Contact, Email, Ean, Iref
FROM OPENJSON(@json, '$[0].Department') WITH (
        Name NVARCHAR(20)
        ,Street NVARCHAR(20)
        ,Zipcode NVARCHAR(20)
        ,City NVARCHAR(20)
        ,Cvr NVARCHAR(20)
        ,Contact NVARCHAR(20)
        ,Email NVARCHAR(20)
        ,Ean NVARCHAR(20)
        ,Iref NVARCHAR(20)
        ) AS go4

In the case of the root object and Department you could also use JSON_VALUE because there is only one object

edited May 27, 2021 at 14:31

answered May 26, 2021 at 12:45

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

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

10 Comments

Artem Over a year ago

This is really good, but I think there's a typo around FROM AS AzureBlob.

Artem Over a year ago

And why is it only INSERT and not INSERT INTO?

Charlieface Over a year ago

Sorry fixed. INTO is optional. Column names in INSERT are optional also, but best to always include them

Artem Over a year ago

For some reason, the INSERT's doens't insert anything. Tables are still empty.

Charlieface Over a year ago

What do you get from just a SELECT, it should be the same? How similar is your actual JSON to the above, you may need to adjust the path (second parameter to OPENJSON)

|

Collectives™ on Stack Overflow

Parse Nested JSON to Azure SQL table

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related