Processing JSON data in Azure SQL Managed Instance just got more performant thanks to the new way JSON data is stored and handled. Now in preview for Azure SQL Managed Instance with Always-up-to-date update policy configured, JSON data can be stored in a new binary data format with database column declared as a new JSON data type:
CREATE TABLE Orders (order_id int, order_details JSON NOT NULL);
All existing JSON functions support the new JSON data type seamlessly, with no code changes. There are also a couple of new aggregate functions:
1. Constructing a JSON object from an aggregation of SQL data or columns:
SELECT JSON_OBJECTAGG( c1:c2 )
FROM (
VALUES('key1', 'c'), ('key2', 'b'), ('key3','a')
) AS t(c1, c2);
2. Constructing a JSON array from an aggregation of SQL data or columns:
SELECT TOP(5) c.object_id, JSON_ARRAYAGG(c.name ORDER BY c.column_id) AS column_list
FROM sys.columns AS c
GROUP BY c.object_id;
For a quick introduction you can watch a short video explaining the very same functionality on Azure SQL Database:
Resources:
JSON data type (preview) - SQL Server | Microsoft Learn
JSON_OBJECTAGG (Transact-SQL) - SQL Server | Microsoft Learn
Microsoft
