Native JSON support now in preview in Azure SQL Managed Instance

Processing JSON data in Azure SQL Managed Instance gets more performant thanks to a new way JSON data is stored and processed. Now in preview with Azure SQL Managed Instance with Always-up-to-date. Policy Updates The composed JSON data can be stored in a new binary data format where the database column is declared as a new JSON data type.

CREATE TABLE Orders (order_id int, order_details JSON NOT NULL);

All existing JSON Functions New JSON data support You can input smoothly without changing the code. There are also some new aggregate functions.

1. Constructing a JSON object from an aggregate of SQL data or columns:

SELECT JSON_OBJECTAGG( c1:c2 )
FROM (
    VALUES('key1', 'c'), ('key2', 'b'), ('key3','a')
) AS t(c1, c2);

2. Constructs a JSON array from SQL data or aggregates of 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 same functionality in Azure SQL Database.

Watch 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