CODEWITHSUNDEEP
jsonsql-serverparsing
Patricio Lobos
Patricio Lobos

Reputation: 136

Parse JSON in Microsoft SQL

I have the following JSON, And I have no clue as to how to parse "Value" (When you run it in SQL you get 3 columns key, value and type)

Any ideas?

       DECLARE @json NVARCHAR(MAX);
SET @json = N'[
  {"salesDate":"2020-03-02","storeSales":[
{"storeId":"104","sales":[
{"productId":"20002","salesVolume":0.700,"salesQuantity":2,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"74301","salesVolume":0.750,"salesQuantity":1,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"642401","salesVolume":0.750,"salesQuantity":1,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"784001","salesVolume":2.100,"salesQuantity":3,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"1013801","salesVolume":1.500,"salesQuantity":2,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"1202801","salesVolume":0.750,"salesQuantity":1,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"1209901","salesVolume":0.700,"salesQuantity":1,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"1282201","salesVolume":0.750,"salesQuantity":1,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"3317301","salesVolume":1.500,"salesQuantity":2,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"4801301","salesVolume":0.700,"salesQuantity":1,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"5780106","salesVolume":6.000,"salesQuantity":2,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"7964902","salesVolume":0.375,"salesQuantity":1,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"10785001","salesVolume":0.750,"salesQuantity":1,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}},
{"productId":"11037501","salesVolume":1.500,"salesQuantity":2,"lastChanged":{"date":"2020-03-03","time":"07:28:06"}}]}]}]';

SELECT *
FROM OPENJSON(@json)

SQL Image

Upvotes: 0

Views: 234

Answers (2)

Kane
Kane

Reputation: 16812

Assuming you wanted to get the sales data try this (although I am not 100% sure if this query offers the best performance)

SELECT [SalesDate], [StoreId], [ProductId], [SalesVolume], [SalesQuantity]
FROM OPENJSON(@json) WITH (
        [SalesDate] DATETIME '$.salesDate',
        [StoreSales] NVARCHAR(MAX) '$.storeSales' AS JSON
    )
    OUTER APPLY OPENJSON(StoreSales) WITH (
        [StoreId] INT '$.storeId',
        [Sales] NVARCHAR(MAX) '$.sales' AS JSON
    )
    OUTER APPLY OPENJSON(Sales) WITH (
        [ProductId] INT '$.productId',
        [SalesVolume] DECIMAL(18, 4) '$.salesVolume',
        [SalesQuantity] INT '$.salesQuantity'
    )

Upvotes: 3

Dylan Smith
Dylan Smith

Reputation: 521

Value is the first object in your JSON array, which is itself another JSON object. SO you will have to read it using other SQL Server JSON utilities. For example:

SELECT JSON_VALUE(Value, '$.salesDate')
FROM OPENJSON(@json)

Upvotes: 0

Related Questions