Native JSON type & JSON aggregates are now in private preview for Azure SQL Database

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

We are excited to announce the private preview of native JSON type and JSON_OBJECTAGG & JSON_ARRAYAGG aggregates in Azure SQL Database. The JSON type will allow you to store JSON documents in a native binary format that is optimized for storage and query performance. The ANSI SQL compatible JSON aggregates - JSON_OBJECTAGG & JSON_ARRAYAG will allow you to aggregate relational data and transform the data into JSON documents in a query.


Before we look at the functionality available in the private preview, Azure SQL Database and SQL Server currently supports the following JSON functionality:


  • ISJSON function to test if a character string contains valid JSON including checking for specific JSON type(s) like object or array or scalar.
  • ANSI SQL compatible functions JSON_VALUE & JSON_QUERY to query JSON documents using SQL/JSON path expressions.
  • ANSI SQL compatible constructors JSON_OBJECT & JSON_ARRAY to construct JSON object or array from relation data.
  • JSON_MODIFY function to modify a property or item at a specific SQL/JSON path in JSON documents.
  • OPENJSON function to transform JSON documents into relational data.
  • FOR JSON operator to transform results of a SELECT statement into a JSON document.

Now, as part of the private preview you will be able to test the following new functionality:

  • Create table(s) with columns of JSON type.
  • Query and manage JSON documents using SELECT, INSERT, UPDATE, DELETE statements.
  • Use JSON functions with the new JSON type.
  • Use JSON_ARRAYAGG & JSON_OBJECTAGG to aggregate data in a query and produce JSON documents.

First, let us look at how to use the new JSON type in a table to store JSON documents. The example below shows a table "Orders" with an order_id column of INT data type & an order_info column of JSON data type. JSON documents are inserted into the table using INSERT statement and the JSON documents are provided as a string. Queries containing columns of the new JSON type will return the data as varchar(max) type.


CREATE TABLE dbo.Orders (
    order_id int NOT NULL IDENTITY,
    order_info JSON NOT NULL
INSERT INTO dbo.Orders (order_info)
    "OrderNumber": "S043659",
}'), ('
    "OrderNumber": "S043661",


Now, the JSON functions can be used to extract properties of the JSON documents using SQL/JSON path expressions. The examples below shows how to extract the AccountNumber property from the JSON document:


SELECT o.order_id, JSON_VALUE(o.order_info, '$.AccountNumber') AS account_number
  FROM dbo.Orders as o;


The JSON type can also be used to declare variables and use it in T-SQL batches & stored procedures:


        "OrderNumber": "S043659",
        "OrderNumber": "S043661",
SELECT @json, JSON_PATH_EXISTS(@json, '$[0].OrderNumber') AS OrderNumberExists
    , JSON_QUERY(@json, '$[0]') as FirstOrder, JSON_Value(@json, '$[0].AccountNumber') as Account#;


Next, let us take a look at how the new JSON aggregates can be used to aggregate data in a query and return JSON documents. We will setup an Accounts and Orders table with sample data first:

	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);

	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
INSERT INTO Orders (OrderNumber, OrderTime, AccountNumber, Price, Quantity)
VALUES ('S043659',

Now, if you want to return the data from these tables as a single JSON document, then you can use the new JSON_OBJECTAGG and JSON_ARRAYAGG aggregates to aggregate the relational data and produce a single JSON document:

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

The JSON document produced from the SELECT statement is shown below:


Hope this gave you a good overview of the new JSON type and JSON_OBJECTAGG & JSON_ARRAYAGG aggregates. We are eager to have you try out the new features & give feedback. You can sign-up for the private preview here.


json private preview sign-upjson private preview sign-up


Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.