How to Convert JSON Data to SQL Table

Convert JSON data to SQL table with ease, using the OPENJSON() function in SQL Server. Learn about convert complex JSON data to SQL table in easy steps.

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

#sql #json