Converting JSON API returned data into 2016 SQL table rows

To start the setup you must have the 2016 version of SQL Server(or higher version)

Create the table beforehand along w/ desired columns(sample columns added)

Fetch API data w/ POSTMAN chrome extension tool or simply use your API fetching custom app to retrieve the data(browser will do too)

In SQL Server 2016 create new query to your db > table
Copy > Paste the following script DECLARE @json NVARCHAR(MAX)

SET @json =

N'[<json>]'

INSERT INTO <table>

SELECT * FROM

OPENJSON ( @json )

WITH (

<fields>

)
where <json> is the returned data from POSTMAN/browser, <table> is the SQL table to which you want the data to be added, <fields> would be the matching fields we add from JSON to SQL column
an example for this with exact sample values would be:
DECLARE @json NVARCHAR(MAX)

SET @json =

N'[

{

"Order": {

"Number":"SO43659",

"Date":"2011-05-31T00:00:00"

},

"AccountNumber":"AW29825",

"Item": {

"Price":2024.9940,

"Quantity":1

}

},

{

"Order": {

"Number":"SO43661",

"Date":"2011-06-01T00:00:00"

},

"AccountNumber":"AW73565",

"Item": {

"Price":2024.9940,

"Quantity":3

}

}

]'

INSERT INTO zSample

SELECT * FROM

OPENJSON ( @json )

WITH (

Number varchar(200) '$.Order.Number' ,

Date datetime '$.Order.Date',

Customer varchar(200) '$.AccountNumber',

Quantity int '$.Item.Quantity'

)

In my POSTMAN JSON API I've fetched the data from https://api.github.com/users/hadley/repos
then you can copy the table columns I've made for the table in step 1

Execute script in SQL 2016 then data rows will be added, you can map all fields just make sure you map the nested data too

As you can see it can even convert nested data from Order and Item values(or owner data in my screenshots); run the script on your own model to test it, enjoy! ;) **You can automate everything with a custom C# app if you feel lazy copy pasting and editing data every time you wish to convert it