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.
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (
order_id int NOT NULL IDENTITY,
order_info JSON NOT NULL
);
INSERT INTO dbo.Orders (order_info)
VALUES ('
{
"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
}');
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:
DECLARE @json JSON = 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
}
]';
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:
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
);
INSERT INTO Orders (OrderNumber, OrderTime, AccountNumber, Price, Quantity)
VALUES ('S043659',
'2022-05-24T08:01:00',
'AW29825',
59.99,
1),
('S043661',
'2022-05-20T12:20:00',
'AW73565',
24.99,
3);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,
'AccountDetails':JSON_OBJECT('AccountNumber':o.AccountNumber,
'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:
{"S043659":{"Date":"2022-05-24T08:01:00","Price":59.99,"Quantity":1,"AccountDetails":{"AccountNumber":"AW29825","PhoneNumbers":["(123)456-7890","(123)567-8901"]}},"S043661":{"Date":"2022-05-20T12:20:00","Price":24.99,"Quantity":3,"AccountDetails":{"AccountNumber":"AW73565","PhoneNumbers":["(234)0987-654"]}}}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.
