SQL Server JSON_OBJECTAGG(): Aggregate Rows into a JSON Object

JSON_OBJECTAGG(key:value) aggregates key/value pairs from multiple rows into one JSON object. Use it to pivot rows into properties, with one object per result group.

Availability: Microsoft’s current function reference lists JSON_OBJECTAGG for SQL Server 2025 (17.x), Azure SQL Database, Azure SQL Managed Instance, SQL analytics endpoint and Warehouse in Microsoft Fabric, and SQL database in Microsoft Fabric. The function also appears in SQL Server 2025’s What’s New list. The release notes say that What’s New features not in the preview table follow the SQL Server 2025 release status; JSON_OBJECTAGG is not listed in that table. Together, those pages imply that the function follows SQL Server 2025’s general availability status, although its function page doesn’t explicitly label the status. Check the function page’s Applies to list and the target service’s update policy before use.

Syntax

JSON_OBJECTAGG(key_expression:value_expression
               [NULL ON NULL | ABSENT ON NULL]
               [RETURNING json])

key_expression supplies the JSON property name; value_expression supplies its value. The result is JSON text of type nvarchar(max) by default. On platforms with the native json type, RETURNING JSON returns that type.

Aggregate key/value rows

Use GROUP BY to produce one JSON object for each group:

WITH Settings AS (
    SELECT *
    FROM (VALUES
        ('reader', 'theme', 'dark'),
        ('reader', 'pageSize', '25'),
        ('editor', 'theme', 'light')
    ) AS v(user_name, setting_name, setting_value)
)
SELECT
    user_name,
    JSON_OBJECTAGG(setting_name:setting_value) AS preferences
FROM Settings
GROUP BY user_name
ORDER BY user_name;

user_name | preferences
----------|-----------------------------------
editor    | {"theme":"light"}
reader    | {"theme":"dark","pageSize":"25"}

The returned JSON object contains one property for each key/value row in its group. Use an outer ORDER BY to sort result rows; the aggregate doesn’t provide an element-order clause for object properties. The property order in the sample output is illustrative and isn’t guaranteed or significant to JSON object meaning.

Choose how SQL NULL values are handled

By default, NULL ON NULL keeps the property and writes its value as JSON null. Use ABSENT ON NULL to omit properties whose value expression is SQL NULL:

WITH Settings AS (
    SELECT *
    FROM (VALUES ('theme', N'dark'), ('timezone', CAST(NULL AS nvarchar(20)))) AS v(setting_name, setting_value)
)
SELECT
    JSON_OBJECTAGG(setting_name:setting_value) AS keep_json_null,
    JSON_OBJECTAGG(setting_name:setting_value ABSENT ON NULL) AS omit_null_property
FROM Settings;

keep_json_null                        | omit_null_property
--------------------------------------|---------------------
{"theme":"dark","timezone":null} | {"theme":"dark"}

To construct one JSON object from expressions in a single row instead of aggregating rows, use JSON_OBJECT(). For arrays, see JSON_ARRAYAGG(). Browse the SQL Server JSON function guide for the full task-based list.