JSON aggregates in Fabric Datawarehouse (Preview)
The preview for JSON aggregate functions in Fabric Datawarehouse has begun. You can now take advantage of two powerful JSON aggregate functions during this preview period.
- JSON_ARRAYAGG: Constructs a JSON array from an aggregation of SQL data or columns. It can be used to create a JSON array from a set of values.
- JSON_OBJECTAGG: Constructs a JSON object from an aggregation of SQL data or columns. It allows you to create key-value pairs from your data.
The preview for these two aggregate functions is already live in Azure SQL Database, and we are excited to announce that it is now also available in Fabric Data Warehouse.
JSON_ARRAYAGG
JSON_ARRAYAGG is an aggregate function that formats a set of cells within a group into a JSON array. For example, if we select country, state, and county from the geography table, we can observe different values of county within each (country, state) group.
If we group rows by country and state, we can concatenate all county cells within each group and output them as a single JSON array per group.
The JSON_ARRAYAGG function is especially useful when you need to represent a set of values as an array, especially when you need to join parent/child rows and return a list of child values for each parent row.
JSON_OBJECTAGG
JSON_OBJECTAGG is an aggregate function that takes two columns within a group of rows and formats their cells as key-value pairs in a single JSON object for each group.
For example, imagine we have a T-SQL query that returns average temperatures in Fahrenheit for cities defined by zip codes during the period from October 14, 2010, to October 21, 2020.
We can observe that within each group of rows, only DateID and AvgTemperatureFahrenheit differ.
By grouping the rows within the same city/county partition, we can obtain the (DateID, AvgTemperatureFahrenheit) pairs and format them as a JSON object. The JSON_OBJECTAGG function allows you to pivot multiple rows and represent pairs of columns as key-value pairs in the aggregated JSON object.
The JSON_OBJECTAGG function is incredibly useful when you need to pivot values from multiple rows within a group into the properties of a single JSON object associated with that group.
Conclusion
JSON_ARRAYAGG and JSON_OBJECTAGG are significant enhancements in T-SQL language that simplify the complex formatting of JSON objects in queries. Tasks that previously required inline FOR JSON expressions or a combination of STRING_AGG and STRING_ESCAPE functions can now be accomplished more easily.
These two aggregate functions are currently in preview in both Azure SQL Database and Fabric Data Warehouse, and they will become generally available simultaneously.
