With the JSON sample scripts supplied with AdventureWorks2016CTP3 (https://www.microsoft.com/en-us/download/details.aspx?id=49502) the code:
SELECT SalesOrderNumber, OrderDate, ShipDate, Status, AccountNumber, TotalDue,
JSON_VALUE(Info, '$.ShippingInfo.Province') as [Shipping Province],
JSON_VALUE(Info, '$.ShippingInfo.Method') as [Shipping Method],
JSON_VALUE(Info, '$.ShippingInfo.ShipRate') as ShipRate,
JSON_VALUE(Info, '$.BillingInfo.Address') as [Billing Address],
JSON_VALUE(Info, '$.SalesPerson.Name') as [Sales Person],
JSON_VALUE(Info, '$.Customer.Name') as Customer
FROM Sales.SalesOrder_json
--WHERE JSON_VALUE(Info, '$.Customer.Name') = 'Edwin Shen'
Only returns NULL
If you set the JSON to be strict
JSON_VALUE(Info, 'strict$.ShippingInfo.Province') as [Shipping Province]
Msg 13608, Level 16, State 5, Line 77
Property cannot be found on the specified JSON path.
The coloum sales.SalesOrder_json.Info is generated from the code in de-normalization.sql which when generating the table Info sets it as an array an array so to access it you need to add [0] to retrieve the values.
SELECT info,SalesOrderNumber, OrderDate, ShipDate, Status, AccountNumber, TotalDue, JSON_VALUE(Info, '$[0].ShippingInfo.Province') as [Shipping Province], JSON_VALUE(Info, '$[0].ShippingInfo.Method') as [Shipping Method], JSON_VALUE(Info, '$[0].ShippingInfo.ShipRate') as ShipRate, JSON_VALUE(Info, '$[0].BillingInfo.Address') as [Billing Address], JSON_VALUE(Info, '$[0].SalesPerson.Name') as [Sales Person]--, -- JSON_VALUE(Info, '$[0].Customer.Name') as Customer, * FROM Sales.SalesOrder_json WHERE JSON_VALUE(Info, '$[0].SalesPerson.Name') = 'David Campbell'
Hope this helps someone.
