Parse JSON Data From MS SQL Server table

Question

I have some DDL below, how can I parse out the JSON data?
This is for MS SQL Server 2016 - and I'd like to parse out each element of the JSON so that if the json id does not exist in dbo.proddata then I insert each of the json nodes into fields in the sql table dbo.proddata. (Each json node is a column in the sql table dbo.proddata).
Create Table JSONData
(
Id int IDENTITY(1,1) PRIMARY KEY
,jsondata varchar(max)
)

Insert Into JsonData Values
([
   {
      "Id":4002348,
      "ShipmentDetails":[
         {
            "Carrier":"FedEx: Ground",
            "TrackingNumber”:”111111111111”,
            "TrackingNumberUrl":"https://www.fedex.com/fedextrack/?trknbr= 111111111111",
            "ShippingCost":0.0,
            "OrderDisplayNumber”:”ABC-123”,
            "CarrierManifestCode":"FDX-GND"
         }
      ],
      "ShipmentLines":[
         {
            "Sku”:”XYZ-“123,
            "QuantityShipped":10,
            "OrderLineId”:”ABC-123-1”,
            "CustomIdentifier":""
         }
      ],
      "OrderNumber”:”ABC-123”,
      "ShipDate":"2022-05-12T13:20:16.443",
      "IsTransmitted":false,
   }
])

Answer 1

Try something like this:

select P.Id, P.OrderNumber, P.ShipDate, P.IsTransmitted, D.*, L.*
from JSONData
cross apply openjson(jsondata) with
(
    Id bigint,
    OrderNumber nvarchar(max),
    ShipDate datetime,
    IsTransmitted bit,
    ShipmentDetails nvarchar(max) as json,
    ShipmentLines nvarchar(max) as json
) P
outer apply openjson(P.ShipmentDetails) with
(
    Carrier nvarchar(max),
    TrackingNumber nvarchar(max),
    TrackingNumberUrl nvarchar(max),
    ShippingCost money,
    OrderDisplayNumber nvarchar(max),
    CarrierManifestCode nvarchar(max)
) D
outer apply openjson(P.ShipmentLines) with
(
    Sku nvarchar(max),
    QuantityShipped int,
    OrderLineId nvarchar(max),
    CustomIdentifier nvarchar(max)
) L

Adjust it to insert the new data to table. (Consider the MERGE or INSERT...SELECT...WHERE NOT EXIST statements).

But you should probably have more tables if you expect multiple shipment details and lines for a product.

Answer 2

Hi @Johnathan Simpson ,

Welcome to Microsoft T-SQL Q&A Forum!

Here , I even think you can not use the new table JSONData , after al l, our purpose is to pass the json value to dbo.proddata , you can retrieve the json value first and then use：

INSERT INTO target_table[(<column-list>)] SELECT ... FROM ...;  

And you can try STEP1:

 DECLARE @json NVARCHAR(max) =N'{  
       "Id":4002348,  
       "ShipmentDetails":[  
          {  
             "Carrier":"FedEx: Ground",  
             "TrackingNumber":"111111111111",  
             "TrackingNumberUrl":"https://www.fedex.com/fedextrack/?trknbr= 111111111111",  
             "ShippingCost" : 0.0,  
             "OrderDisplayNumber": "ABC-123",  
             "CarrierManifestCode" : "FDX-GND"  
          }  
       ],  
       "ShipmentLines":[  
          {  
             "Sku”:”XYZ-“123,  
             "QuantityShipped":10,  
             "OrderLineId”:"ABC-123-1",  
             "CustomIdentifier": ""  
          }  
       ],  
       "OrderNumber” : "ABC-123",  
       "ShipDate" : "2022-05-12T13:20:16.443",  
       "IsTransmitted" : false  
    }'  
select * into dbo.proddata from   
openjson(@json,'$.ShipmentDetails')  
with(  
 Details_Carrier nvarchar(20) '$.Carrier',  
 Details_TrackingNumber nvarchar(20) '$.TrackingNumber',  
 Details_Url   nvarchar(50) '$.TrackingNumberUrl',  
 Details_ShippingCost nvarchar(10) '$.ShippingCost',  
 Details_OrderDisplayNumber nvarchar(20) '$.OrderDisplayNumber',  
 Details_CarrierManifestCode nvarchar(20) '$.CarrierManifestCode'  
)  
--drop table  dbo.proddata  
select * from dbo.proddata  

Step2：

select * into dbo.proddata from   
openjson(@json) with  
(  
 Id int '$.Id',  
 OrderNumber nvarchar(50) '$.OrderNumber',  
 ShipDate datetime '$.ShipDate',  
 IsTransmitted bool  
)  
outer apply openjson(@json,'$.ShipmentDetails')  
with(  
 Details_Carrier nvarchar(20) '$.Carrier',  
 Details_TrackingNumber nvarchar(20) '$.TrackingNumber',  
 Details_Url   nvarchar(50) '$.TrackingNumberUrl',  
 Details_ShippingCost nvarchar(10) '$.ShippingCost',  
 Details_OrderDisplayNumber nvarchar(20) '$.OrderDisplayNumber',  
 Details_CarrierManifestCode nvarchar(20) '$.CarrierManifestCode'  
)  
outer apply openjson(@json,'$.ShipmentLines')  
with   
(  
 Ship_SKU nvarchar(20) '$.Sku',  
 Ship_QuantityShipped int '$.QuantityShipped',  
 Ship_OrderLineId nvarchar(20) '$.OrderLineId',  
 Ship_CustomIdentifier nvarchar(20) '$.CustomIdentifier'  
)  

By the way , I think you know the meaning of the status value of IsTransmitted , we use bit to represent 0, 1, null, and false to represent 0 .

JSON documents may have child elements and hierarchical data that cannot be directly mapped to standard relational columns . In this case , you can flatten the JSON hierarchy by concatenating the parent entity with the child array,So we used outer apply. which can found int this link.

If you want to know more about json incoming table , please click this link , which has very detailed instructions.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.