Native JSON support now in preview in Azure SQL Managed Instance

  • Thread starter Thread starter Mladen_Andzic
  • Start date Start date
M

Mladen_Andzic

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:



Code: 
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:



Code: 
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:



Watch on Data Exposed



Resources:

JSON data type (preview) - SQL Server | Microsoft Learn

JSON_OBJECTAGG (Transact-SQL) - SQL Server | Microsoft Learn

JSON_ARRAYAGG (Transact-SQL) - SQL Server | Microsoft Learn

Continue reading...
 
Back
Top