How To Convert Complex JSON Data To SQL Table

To convert complex JSON data to a SQL table, use the query below.

Step 1

In order to create complicated JSON and convert complex JSON into a SQL table, we need the Openjson() function.

declare @Jsonstring nvarchar(max)=
'{
"BankID":"00001",
"BankName":"Sbank7",
"BankType":"National",
"BankAccount":"021356458777",
"BankContacts":"0213258777",
"BankAccounts":{

                  "Account":
          [
          {"Acid":"1001","Name":"Sai pathrikar"},
          {"Acid":"1002","Name":"Swaraj pathrikar"},
          {"Acid":"1003","Name":"Sharayu pathrikar"},
          {"Acid":"1004","Name":"Kartik Ingle"}
          ]},
"BankTransaction":
[
{"Trid":"5001","TrAmount":1500,"TrTypr":"CR","Acid":"1001","TrDate":"2022-01-01"},
{"Trid":"5002","TrAmount":2000,"TrTypr":"DR","Acid":"1002","TrDate":"2022-01-01"},
{"Trid":"5003","TrAmount":2500,"TrTypr":"CR","Acid":"1003","TrDate":"2022-01-01"},
{"Trid":"5004","TrAmount":3000,"TrTypr":"DR","Acid":"1004","TrDate":"2022-01-01"}
]
}'

select Tbl_Bank.BankId,Tbl_Bank.BankName,Tbl_Bank.BankType,Tbl_Bank.BankAccount,
Tbl_Bank.BankContacts,Tbl_Account.Acid,Tbl_Account.Name,
Tbl_Tr.Trid ,Format(Tbl_Tr.TrAmount,'C','en-in') Tramount, Case when TrTypr='CR' then 'Credited' else 'Debited'
end TranType ,Format(Tbl_Tr.TrDate,'dd-MMM-yyyy') TrDate
from openJson(@Jsonstring)
with
(
BankID int , BankName varchar(max),BankType varchar(max),BankAccount varchar(max),
BankContacts varchar(max), BankAccounts nvarchar(max) as json,BankTransaction nvarchar(max) as json
)AS Tbl_Bank
Cross apply openjson (Tbl_Bank.BankAccounts)
with(
Account nvarchar(max) as json
) AS Tbl_BankAccounts cross apply openjson (Tbl_BankAccounts.Account)
with
(
Acid int , Name nvarchar(max)
) Tbl_Account
cross apply openjson(Tbl_Bank.BankTransaction)
with(
Trid int,TrAmount decimal(18,2),TrTypr varchar(max),Acid int,TrDate date
) Tbl_Tr where Tbl_Tr.Acid=Tbl_Account.Acid