Tutorial by Examples

JSON_VALUE function enables you to take a data from JSON text on the path specified as the second argument, and use this value in any part of the select query: select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type from Product where JSON_VALUE(Data, '$.Type') = 'part' ...

Once JSON values are extracted from JSON text, you can use them ina any part of the query. You can create some kind of report on JSON data with grouping aggregations, etc: select JSON_VALUE(Data, '$.Type') as type, AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost from...

If some JSON text might not be properly formatted, you can remove those entries from query using ISJSON function. select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type from Product where JSON_VALUE(Data, '$.Type') = 'part' and ISJSON(Data) > 0

JSON_MODIFY function can be used to update value on some path. You can use this function to modify original value of JSON cell in UPDATE statement: update Product set Data = JSON_MODIFY(Data, '$.Price', 24.99) where ProductID = 17; JSON_MODIFY function will update or create Price key (if it do...

JSON_MODIFY function can be used to append new value to some array inside JSON: update Product set Data = JSON_MODIFY(Data, 'append $.tags', "sales") where ProductID = 17; New value will be appended at the end of the array, or a new array with value ["sales"] will be create...

If you have a "child table" formatted as JSON collection and stored in-row as JSON column, you can unpack this collection, transform it to table and join it with parent row. Instead of the standard JOIN operator, you should use CROSS APPLY. In this example, product parts are formatted as ...

In this example, Tags array may contain various keywords like ["promo", "sales"], so we can open this array and filter values: select ProductID, Name, Color, Size, Price, Quantity from Product CROSS APPLY OPENJSON(Data, '$.Tags') where value = 'sales' OPENJSON will op...