Announcing JSON Enhancements in Azure SQL Database, Azure SQL Managed Instance and SQL Server 2022

Azure SQL Database, Azure SQL Managed Instance and SQL Server currently support JSON text processing in T-SQL batches. This functionality allows you to store and manipulate JSON documents in the database. The JSON functions - ISJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY allows you to validate, query and modify JSON documents. The FOR JSON operator allows you to transform SQL data into a JSON string. The OPENJSON operator allows you to transform JSON string into a relational dataset.

Today, we are announcing the public preview of JSON enhancements in Azure SQL Database, Azure SQL Managed Instance and SQL Server 2022 CTP 2.0. This preview contains an enhancement to ISJSON function and three new JSON functions – JSON_PATH_EXISTS, JSON_OBJECT and JSON_ARRAY. Currently, the ISJSON function allows you to test if a string value contains a valid JSON object or array. The new optional json_type_constraint parameter in ISJSON function can now be used to test conformance of JSON documents to the IETF RFC 8259 specification. This capability allows you to test for strings that contain a JSON value, scalar, object, or array. This functionality is like the IS JSON predicate in the ANSI SQL standard. The new JSON_PATH_EXISTS function allows you to test for the existence of a specific SQL/JSON path expression in a JSON document. This functionality is like the JSON_EXISTS predicate in the ANSI SQL standard. The new ANSI SQL compatible JSON value constructors - JSON_OBJECT and JSON_ARRAY functions allow you to construct JSON object or array from SQL data.

JSON functionality overview

Now, let us look at how the new JSON functionality can be used. The ISJSON function currently tests if a string is a valid JSON object or array. This function is typically used in a CHECK constraint on a column. Consider a SQL table that stores order details from an order processing system as a JSON string or document. The schema of the table is shown below:

CREATE TABLE new_orders (
 order_id int IDENTITY NOT NULL PRIMARY KEY,
 order_details nvarchar(4000) NOT NULL CHECK (ISJSON (order_details) = 1)
);

The ISJSON condition in the CHECK constraint ensures that the input string contains a valid JSON object or array. However, if the application developer wants to enforce the additional requirement that the JSON string should be a JSON array then it is not possible to use ISJSON directly since it tests for both JSON object and array. The new json_type_constraint parameter can now be used to ensure that the application only inserts the order details as a JSON array. The table definition can be modified as shown below:

CREATE TABLE new_orders (
 order_id int IDENTITY NOT NULL PRIMARY KEY,
 order_details nvarchar(4000) NOT NULL CHECK (ISJSON (order_details, ARRAY) = 1)
);

The new JSON_PATH_EXISTS function can be used to augment the CHECK constraint to test for existence of specific SQL/JSON paths in the JSON documents. The table definition can be modified as shown below to test for specific JSON path:

CREATE TABLE new_orders (
 order_id int IDENTITY NOT NULL PRIMARY KEY,
 order_details nvarchar(4000) NOT NULL
  CHECK (ISJSON (order_details, ARRAY) = 1 AND
         JSON_PATH_EXISTS(order_details, '$.OrderNumber') = 1)
);

Currently, the FOR JSON operator can be used to transform relational data into a JSON string. However, there are some limitations with the FOR JSON operator. For example, producing a nested JSON document from a query may require sub-queries or parameterizing the key name in a JSON object requires dynamic SQL or constructing JSON array with scalars is not possible. The ANSI SQL compatible JSON_OBJECT & JSON_ARRAY functions allow you to construct complex JSON documents using scalar functions in a query or procedural code. A sample query that constructs a JSON document with a JSON object containing a nested JSON array is shown below:

SELECT o.OrderNumber,
	JSON_OBJECT('Date':o.OrderTime, 'Price':o.Price, 'Quantity':o.Quantity, 
		    'AccountDetails':JSON_OBJECT('AccountNumber':o.AccountNumber, 
                        'PhoneNumbers':JSON_ARRAY(a.Phone1, a.Phone2, a.Phone3))) AS OrderDetails
  FROM Orders AS o
  JOIN Accounts AS a
    ON a.AccountNumber = o.AccountNumber;

This sample query generates a result set with 2 columns: one containing the value of the OrderNumber column and another column containing a string formatted as a JSON document. using the new JSON_OBJECT and JSON_ARRAY functions. The JSON document produced from a sample is shown below:

{"Date":"2022-05-24T08:01:00","Price":59.99,"Quantity":1,"AccountDetails":{"AccountNumber":"AW29825","PhoneNumbers":["(123)456-7890","(123)567-8901"]}}

The script below contains an end-to-end sample of the new JSON functionality:

USE tempdb;
-- Sample schema:
DROP TABLE IF EXISTS Accounts;
CREATE TABLE Accounts (
	AccountNumber varchar(10) NOT NULL PRIMARY KEY,
	Phone1 varchar(20) NULL,
	Phone2 varchar(20) NULL,
	Phone3 varchar(20) NULL
);
INSERT INTO Accounts (AccountNumber, Phone1, Phone2, Phone3)
VALUES('AW29825', '(123)456-7890', '(123)567-8901', NULL),
	('AW73565', '(234)0987-654', NULL, NULL);
DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
	OrderNumber varchar(10) NOT NULL PRIMARY KEY,
	OrderTime datetime2 NOT NULL,
	AccountNumber varchar(10) NOT NULL,
	Price decimal(10, 2) NOT NULL,
	Quantity int NOT NULL
);

-- Input JSON document example:
DECLARE @json nvarchar(1000) = N'
[
    {
        "OrderNumber": "S043659",
        "Date":"2022-05-24T08:01:00",
        "AccountNumber":"AW29825",
        "Price":59.99,
        "Quantity":1
    },
    {
        "OrderNumber": "S043661",
        "Date":"2022-05-20T12:20:00",
        "AccountNumber":"AW73565",
        "Price":24.99,
        "Quantity":3
    }
]';
-- Test for valid JSON array and a specific SQL/JSON path:
SELECT ISJSON(@json, ARRAY) AS IsValidJSONArray, JSON_PATH_EXISTS(@json, '$[0].OrderNumber') AS OrderNumberExists;

-- Transform JSON string into relational data using OPENJSON operator:
INSERT INTO Orders (OrderNumber, OrderTime, AccountNumber, Price, Quantity)
  SELECT T.OrderNumber, T.OrderTime, T.AccountNumber, T.Price, T.Quantity
  FROM OPENJSON(@json)
		WITH(
			OrderNumber varchar(10) '$.OrderNumber',
			OrderTime datetime2 '$.Date',
			AccountNumber varchar(10) '$.AccountNumber',
			Price decimal(10, 2) '$.Price',
			Quantity int '$.Quantity'
			) AS T;

SELECT * FROM Orders;

-- Transform relational data into a JSON string using JSON_OBJECT function:
SELECT o.OrderNumber, JSON_OBJECT('Date':o.OrderTime, 'AccountNumber':o.AccountNumber, 'Price':o.Price, 'Quantity':o.Quantity) AS OrderDetails
  FROM Orders AS o;

-- Transform relational data into a JSON string:
-- Approach using the new JSON_OBJECT & JSON_ARRAY functions:
SELECT o.OrderNumber,
		JSON_OBJECT('Date':o.OrderTime, 'Price':o.Price, 'Quantity':o.Quantity, 
			'AccountDetails':JSON_OBJECT('AccountNumber':o.AccountNumber, 'PhoneNumbers':JSON_ARRAY(a.Phone1, a.Phone2, a.Phone3))) AS OrderDetails
  FROM Orders AS o
  JOIN Accounts AS a
    ON a.AccountNumber = o.AccountNumber;

-- One approach with the existing FOR JSON operator:
SELECT o.OrderNumber,
	(SELECT o.OrderTime as Date, o.Price, o.Quantity,
		(SELECT a.AccountNumber, JSON_QUERY(CONCAT('[',
			CONCAT_WS(',', QUOTENAME(a.Phone1, '"'),			
				QUOTENAME(a.Phone2, '"'),
				QUOTENAME(a.Phone3, '"')), ']')) AS PhoneNumbers
		    FOR JSON PATH) AS AccountDetails
	    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS OrderDetails
  FROM Orders AS o
  JOIN Accounts AS a
    ON a.AccountNumber = o.AccountNumber;

In summary, you have seen how to use the ISJSON enhancement and the new JSON functions JSON_PATH_EXISTS, JSON_OBJECT and JSON_ARRAY.  You can try these enhancements in SQL Server 2022 CTP 2.0. These enhancements will come soon to Azure SQL Database and Azure SQL Managed Instance. Let us know what you think about these JSON enhancements and other JSON capabilities you would like to see in the database engine.