Menu

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 currently lists JSON_OBJECTAGG as a preview feature in SQL Server 2025 (17.x). It is generally available in Azure SQL Database, Azure SQL Managed Instance when using the SQL Server 2025 or Always-up-to-date update policy, SQL database in Microsoft Fabric, and Fabric Data Warehouse. Check the current Microsoft function reference for platform and update-policy details before relying on it in production.

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.

Advertisement