Convert Complex JSON Data To SQL Table

In this article we learn about convert complex json data to sql table in easy steps

Below Query TO Convert Complex JSON data to SQL table,

Step 1

Create complex JSON & Convert complex JSON into SQL Table , for this we are using Openjson() for Converting JSON data into SQL,

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