Query JSON data

[JovanPop]

First published on MSDN on Nov 28, 2015

New JSON support in SQL server enables you to create SQL queries that use both relational and semi-structured data formatted as JSON text. In this post we will see some query examples.

Problem

In my database I have information stored both as scalars in standard relation columns and as properties in JSON columns. I need to create queries that combine information both from JSON text and standard columns.

Solution

New functions for JSON text processing in SQL server enable us to query both relational and non-structured data formatted as JSON.

Data model

In this post I will use the Sales.SalesOrder_json table published in AdventureWorks2016CTP3 database. Sales.SalesOrder_json table is a copy of Sales.SalesOrderHeader table from AdventureWorks2016 database with additional JSON columns:

• OrderItems that contains an array of SalesOrderDetails rows belonging to sales order header


• SalesReasons that contains array of sales reasons associated to sales order


• Info that contains various information about the sales order such as customer name, sales person

Use Case 1

In the first example we will create a stored procedure that reads both columns and JSON values from sales order table:

CREATE PROCEDURE
Sales.SalesOrderInfo_json(@SalesOrderID int)
AS BEGIN
SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, AccountNumber, TotalDue,
JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
JSON_VALUE(Info, '$.ShippingInfo.City') City,
JSON_VALUE(Info, '$.Customer.Name') Customer,
JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE SalesOrderID = @SalesOrderID
END
GO


In this stored procedure you can read both standard relational columns from table and extract objects (e.g. shipping/billing information) and values (sales person, city from shipping address, and customer name) from Info JSON column. If you want to use relational model instead of JSON functions you would need to create complex query with several table joins:

CREATE PROCEDURE
Sales.SalesOrderInfoRel_json(@SalesOrderID int)
as begin
SELECT SalesOrderNumber, OrderDate, ShipDate, Status, Sales.SalesOrder_json.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.SalesOrdereHeader.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
WHERE Sales.SalesOrderHeader.SalesOrderID = @SalesOrderID
end
GO


As you can see, some basic information required in the report require several table joins. Besides simplicity of query, you might get some performance improvements. First stored procedure uses just one table seek operation to read a single row; however, relational query must seek into several table and join results.

Use case 2: Aggregate results using columns and values from JSON text

You can use both standard relational columns and properties from JSON fields in any part of query. In the following stored procedure are filtered and aggregated results using values from JSON text:

CREATE PROCEDURE
Sales.SalesOrdersPerCustomerAndStatusReport_json(@city nvarchar(50), @territoryid int)
AS BEGIN
SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer, Status, SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'), Status
HAVING SUM(SubTotal) > 1000
END


Use Case 3: Aggregate results using collections of JSON objects

In the previous examples we have used just single values from JSON text. OPENJSON function enables us to transform JSON arrays to bales and apply any aggregation function. In the following stored procedure we will take sales reasons stored as arrays of JSON string values in SalesReasons column and group sales orders by the values in this array and return count of sales order by each sales reason:

CREATE PROCEDURE
Sales.SalesOrdersBySalesReasonReport_json(@city nvarchar(50))
AS BEGIN
SELECT value, COUNT(SalesOrderNumber) AS NumberOfOrders
FROM Sales.SalesOrder_json
CROSS APPLY OPENJSON (SalesReasons)
WHERE JSON_VALUE(Info, '$.ShippingInfo.City') = @city
GROUP BY value
END
GO


Conclusion

Function for JSON text processing in SQL Server enable you to combine JSON data with standard columns in any SQL query. With JSON text function you can use full SQL language for querying and analyzing JSON data. Also, you can combine both standard relational data with JSON values in any query.