Parse JSON in Microsoft SQL

Question

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

@Kane provided a solid answer. May I suggest that you show your appreciation for his time and talent by ACCEPTING the answer. — John Cappelletti
– John Cappelletti, Commented Jun 5, 2020 at 20:19

Kane · Accepted Answer · 2020-06-05 20:06:44Z

3

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

edited Jun 5, 2020 at 20:06

answered Jun 5, 2020 at 20:00

Kane

16.8k11 gold badges67 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Cappelletti Over a year ago

Just one small tweak SELECT SalesDate,StoreId,ProductId,SalesVolume,SalesQuantity Either way +1

John Cappelletti Over a year ago

Actually, it may be helpful for the OP to visualize how this works by keeping SELECT *

Dylan Smith · Accepted Answer · 2020-06-05 19:53:17Z

0

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)

answered Jun 5, 2020 at 19:53

Dylan Smith

5215 silver badges11 bronze badges

Collectives™ on Stack Overflow

Parse JSON in Microsoft SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related