SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

AdventureWorks2016CTP3 - JSON Sample scripts

By Mick Rust,

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.

Total article views: 331 | Views in the last 30 days: 10
 
Related Articles
FORUM

seperate city and state/province tables?

I was wondering if it would be better to create seperate tables for the city and province/state to r...

FORUM

Resource for States/Provinces/Regions by Country

Not sure if this is the right or best place to post this, but here is what I am looking for. Cu...

FORUM

Design database to store countries, provinces and cities

I am developing an application (online) which is about FindAProperty. This is intended to be used...

FORUM

Multiple level grouping in a SSRS report

Report needs to be grouped and drill down by province and city, however each level of grouping has m...

BLOG

Oracle-SQL Datatype conversion issues through linked server

Msg 9803,Level 16,State 1, Line 1 Invalid data fortype"numeric". Fix: Use TO_CHAR function. See bel...

 
Contribute