How To Convert Complex JSON Data to SQL Table

Here, used OpenJson() to convert complex JSON Data to your sql Table.

declare @Jsonstring nvarchar(max)=
'{
"ProductID":"00001",
"ProductName":"Sbank7",
"ProductDescription":"Furnichar",
"ProductCategory":{

                  "Category":
          [
          {"cid":"1001","Name":"Chair"},
          {"cid":"1002","Name":"Table"},
          {"cid":"1003","Name":"Cupboard"}
          ]},
"ProductPurchase":
[
{"Pid":"5001","TrAmount":1500,"cid":"1001","TrDate":"2022-01-01"},
{"Pid":"5002","TrAmount":2000,"cid":"1002","TrDate":"2022-01-01"},
{"Pid":"5003","TrAmount":2500,"cid":"1003","TrDate":"2022-01-01"}
]
}'

select Tbl_Product.ProductID,Tbl_Product.ProductName,
Tbl_Product.ProductDescription,Tbl_Category.cid,Tbl_Category.Name,
Tbl_Tr.Pid ,Format(Tbl_Tr.TrAmount,'C','en-in') Tramount, Format(Tbl_Tr.TrDate,'dd-MMM-yyyy') TrDate
from openJson(@Jsonstring)
with
(
ProductID int , ProductName varchar(max),
ProductDescription varchar(max), ProductCategory nvarchar(max) as json,ProductPurchase nvarchar(max) as json
)AS Tbl_Product
Cross apply openjson (Tbl_Product.ProductCategory)
with(
Category nvarchar(max) as json
) AS Tbl_ProductCategory cross apply openjson (Tbl_ProductCategory.Category)
with
(
cid int , Name nvarchar(max)
) Tbl_Category
cross apply openjson(Tbl_Product.ProductPurchase)
with(
Pid int,TrAmount decimal(18,2),cid int,TrDate date
) Tbl_Tr where Tbl_Tr.cid=Tbl_Category.cid

see Output,