Create relational view over JSON text

  • Article

SQL Server 2016 enables you to use JSON_VALUE, JSON_QUERY, and OPENJSON functions to fetch data from JSON text. However, if you want to to query JSON data using standard relational models you can create views that encapsulate these functions.

In AdventureWorks2016CTP3 database is added Sales.SalesOrder_json table with two columns:

  • Info that contains various information about the sales order
  • OrderItems that contains an array of sales order items formatted as JSON array

You would need to run de-normalization script (attached in this post) to create and populate these columns.

If you want to access information in Info column, you can create standard view that encapsulates JSON_VALUE functions that access values from JSON column.

 CREATE VIEW Sales.vwSalesOrderInfo_json AS
 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

 

In the relational model you would need to join a lot of tables to get the same information, i.e.:

 

 CREATE VIEW Sales.vwSalesOrderInfoRel_json AS
 SELECT SalesOrderNumber, OrderDate, ShipDate, Status, Sales.SalesOrderHeader.AccountNumber, TotalDue,
 shipprovince.Name as [Shipping Province], 
 shipmethod.Name as [Shipping Method], 
 shipmethod.ShipRate as ShipRate,
 billaddr.AddressLine1 + COALESCE ( ', ' + shipaddr.AddressLine2, '') as [Billing Address],
 sp.FirstName + ' ' + sp.LastName as [Sales Person],
 cust.FirstName + ' ' + cust.LastName as Customer 
 FROM Sales.SalesOrderHeader
 JOIN Person.Address shipaddr
 ON Sales.SalesOrderHeader.ShipToAddressID = shipaddr.AddressID
 LEFT JOIN Person.StateProvince shipprovince
 ON shipaddr.StateProvinceID = shipprovince.StateProvinceID
 JOIN Purchasing.ShipMethod shipmethod
 ON Sales.SalesOrderHeader.ShipMethodID = shipmethod.ShipMethodID
 JOIN Person.Address billaddr
 ON Sales.SalesOrderHeader.BillToAddressID = billaddr.AddressID
 LEFT JOIN Sales.SalesPerson
 ON Sales.SalesPerson.BusinessEntityID = Sales.SalesOrderHeader.SalesPersonID
 LEFT JOIN Person.Person AS sp
 ON Sales.SalesPerson.BusinessEntityID = sp.BusinessEntityID
 LEFT JOIN Sales.Customer
 ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID
 LEFT JOIN Person.Person AS cust
 ON Sales.Customer.CustomerID = cust.BusinessEntityID

 

In Sales.SalesOrder_json table we also have OrderItems column that contains content of related SalesOrderDetails table. You can transform this array to rowset using the following view:

 CREATE VIEW Sales.vwSalesOrderItems_json
 AS
 SELECT SalesOrderID, SalesOrderNumber, OrderDate,
 CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal, ProductNumber, Name
 FROM Sales.SalesOrder_json
 CROSS APPLY
 OPENJSON (OrderItems)
 WITH ( CarrierTrackingNumber NVARCHAR(20),
 OrderQty int '$.Item.Qty',
 UnitPrice float '$.Item.Price',
 UnitPriceDiscount float '$.Item.Discount',
 LineTotal float '$.Item.Total',
 ProductNumber NVARCHAR(20) '$.Product.Number',
 Name NVARCHAR(50) '$.Product.Name'
 )

 

This view returns the same information as original SalesOrderDetails table. 

 

 

 

 

1. de-normalization.sql