Is there any way to insert below nested json data into sql server

I am working on a nested JSON data which I need to load into SQL SERVER 2012. The nested JSON contains two roots i.e. one column and another rows. I need to put value from row into the column. Please see the structure as below:

{
    "tables": [
        {
            "name": "PrimaryResult",
            "columns": [
                {
                    "name": "timestamp",
                    "type": "datetime"
                },
                {
                    "name": "id",
                    "type": "string"
                },
                {
                    "name": "name",
                    "type": "string"
                },
                {
                    "name": "url",
                    "type": "string"
                },
                {
                    "name": "duration",
                    "type": "real"
                }
            ],
            "rows": [
                [
                    "2019-04-08T13:09:52.871Z",
                    "244",
                    "Internal",
                    "https://google.com",
                    1245,

                ]
            ]
        }
    ]
}

Result:

timestamp    id    name    url    duration
2019-04-08    244    Internal    https://google.com    1245

Here , in sql server it should take column names from columns and value for each column from rows

Answer

assuming that you store the json into a file named json.txt

import json

with open('json.txt') as f:
    data = json.load(f)
    tableName = data['tables'][0]["name"]
    sqlCreateTable = 'CREATE TABLE ' + tableName + ' (n'
    sqlInsertInto = 'INSERT INTO ' + tableName + ' ('
    for i in range(0,len(data['tables'][0]['columns'])):
        columnName = data['tables'][0]['columns'][i]['name']
        type = data['tables'][0]['columns'][i]['type']
        sqlCreateTable += columnName + " " + type + ',n'
        sqlInsertInto += columnName + ', '

    sqlCreateTable = sqlCreateTable[:-2] + 'n);'
    sqlInsertInto = sqlInsertInto[:-2] + ')nVALUES ('

    for value in data['tables'][0]['rows'][0]:
        sqlInsertInto += str(value) + ', '

    sqlInsertInto = sqlInsertInto[:-2] + ');'

    print(sqlCreateTable)
    print(sqlInsertInto)

Output for create table:

CREATE TABLE PrimaryResult (
timestamp datetime,
id string,
name string,
url string,
duration real
);

Output for insert into table:

INSERT INTO PrimaryResult (timestamp, id, name, url, duration)
VALUES (2019-04-08T13:09:52.871Z, 244, Internal, https://google.com, 1245);