Create relational view over JSON text
Published Mar 23 2019 02:08 PM 1,743 Views
First published on MSDN on Dec 09, 2015

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
SELECT SalesOrderID, SalesOrderNumber, OrderDate,
CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal, ProductNumber, Name
FROM Sales.SalesOrder_json
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

Version history
Last update:
‎Mar 23 2019 02:08 PM
Updated by: