Query JSON data

  • Article

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.