JSON Support for SQL Server

Example

Data

HatOrders Table
Id OrderData
1
{ "Name": "Bobby Hatman", "Address": { "Line1": "123 Hatlover's Lane", "Line2": null, "Suburb": "Melbourne", "State": "VIC", "Postcode": "3000" }, "OrderLines": [ { "ProductId": 72, "Description": "Purple Fedora", "Quantity": 2, "UnitPrice": 2.34, "LineTotal": 4.68 }, { "ProductId": 23, "Description": "Red Cap", "Quantity": 1, "UnitPrice": 2.57, "LineTotal": 2.57 } ], "OrderTotal": 7.25 }
2
{ "Name": "Hat Industries Pty Ltd", "Address": { "Line1": "456 Hat Rd", "Line2": null, "Suburb": "Sydney", "State": "NSW", "Postcode": "2000" }, "OrderLines": [ { "ProductId": 5, "Description": "Luxurious Leather Legionnaire Hat", "Quantity": 20, "UnitPrice": 54.00, "LineTotal": 1080 } ], "OrderTotal": 1080 }

Query

select Id, dbo.JsonNVarCharMax(OrderData, 'Name') as Name, dbo.JsonNVarCharMax(OrderData, 'Address.Suburb') as Suburb, dbo.JsonDecimal(OrderData, 'OrderTotal') as Total from HatOrders

Results

Id Name Suburb Total
1 Bobby Hatman Melbourne 7.25
2 Hat Industries Pty Ltd Sydney 1080