AdventureWorks2016CTP3 - JSON Sample scripts

,

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

Rate

1 (1)

Share

Share

Rate

1 (1)