question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked BertZhoumsft-7490 answered

Parse JSON Data From MS SQL Server table

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,
    }
 ])
sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
Viorel-1 avatar image
2 Votes"
Viorel-1 answered BertZhoumsft-7490 edited

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered Viorel-1 commented

Hi @InigoMontoya-1790 ,

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

203471-image.png

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'
 )

203551-image.png
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.



image.png (7.4 KiB)
image.png (9.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.