SQL Server 2025 ships with a handful of genuinely useful additions, and JSON_OBJECTAGG() is one of the better ones. It lets you aggregate rows into a single JSON object (with key-value pairs pulled directly from your data) without any string hacking or FOR JSON PATH gymnastics.
Here’s what it does, how it works, and when you might actually use it.
What It Does
JSON_OBJECTAGG() is an aggregate function. It takes two expressions per row (a key and a value) and rolls them up into a single JSON object. One row becomes one key-value pair. All the rows in a group become a single {} object.
The syntax goes something like this:
JSON_OBJECTAGG ( key : value [ NULL ON NULL | ABSENT ON NULL ] [ RETURNING JSON ] )key becomes the property name and value becomes the property value. We’ll cover the various options in the examples below.
A Simple Example
Say you have a table of product attributes stored in a key-value pattern:
CREATE TABLE ProductAttributes (
ProductID INT,
AttrName VARCHAR(50),
AttrValue VARCHAR(100)
);
INSERT INTO ProductAttributes VALUES
(1, 'color', 'matte black'),
(1, 'weight', '1.2kg'),
(1, 'material', 'aluminum'),
(2, 'color', 'white'),
(2, 'weight', '0.9kg'),
(3, 'color', 'orange'),
(3, 'weight', null),
(4, 'color', null),
(4, 'weight', '1.9kg');You can use JSON_OBJECTAGG() to pivot these values into a JSON object, like this:
SELECT
ProductID,
JSON_OBJECTAGG(AttrName : AttrValue) AS Attributes
FROM ProductAttributes
GROUP BY ProductID;Result:
ProductID Attributes
--------- --------------------------------------------------------------
1 {"color":"matte black","weight":"1.2kg","material":"aluminum"}
2 {"color":"white","weight":"0.9kg"}
3 {"color":"orange","weight":null}
4 {"color":null,"weight":"1.9kg"}
Each group becomes its own tidy JSON object. No STUFF(), no FOR JSON PATH, and no subquery needed.
Handling NULL Values
By default, if value is NULL, the output returns NULL for the value. You can change that behavior with the ABSENT ON NULL / NULL ON NULL clause:
SELECT
ProductID,
JSON_OBJECTAGG(AttrName : AttrValue ABSENT ON NULL) AS "ABSENT ON NULL",
JSON_OBJECTAGG(AttrName : AttrValue NULL ON NULL) AS "NULL ON NULL"
FROM ProductAttributes
GROUP BY ProductID;Result:
ProductID ABSENT ON NULL NULL ON NULL
--------- -------------------------------------------------------------- --------------------------------------------------------------
1 {"color":"matte black","weight":"1.2kg","material":"aluminum"} {"color":"matte black","weight":"1.2kg","material":"aluminum"}
2 {"color":"white","weight":"0.9kg"} {"color":"white","weight":"0.9kg"}
3 {"color":"orange"} {"color":"orange","weight":null}
4 {"weight":"1.9kg"} {"color":null,"weight":"1.9kg"}
So when we use ABSENT ON NULL, any property with a NULL value is completely omitted from the output. But with NULL ON NULL (the default), the property is still included in the output, with the value set to null.
Combining with Other Aggregates
JSON_OBJECTAGG() plays well alongside regular aggregates. You can mix it with COUNT(), SUM(), or anything else in the same SELECT:
SELECT
ProductID,
COUNT(*) AS TotalAttributes,
JSON_OBJECTAGG(AttrName VALUE AttrValue) AS AttributeJSON
FROM ProductAttributes
GROUP BY ProductID;Output:
ProductID TotalAttributes AttributeJSON
--------- --------------- --------------------------------------------------------------
1 3 {"color":"matte black","weight":"1.2kg","material":"aluminum"}
2 2 {"color":"white","weight":"0.9kg"}
3 2 {"color":"orange","weight":null}
4 2 {"color":null,"weight":"1.9kg"}
This gives you both the count and the JSON object in one pass. No subqueries needed.
Nesting JSON
We can use JSON_OBJECTAGG() with a common table expression (CTE) to return nested JSON:
WITH ProductJSON AS (
SELECT
ProductID,
JSON_OBJECTAGG(AttrName : AttrValue) AS Attrs
FROM ProductAttributes
GROUP BY ProductID
)
SELECT JSON_OBJECTAGG(ProductID : Attrs) AS AllProducts
FROM ProductJSON;Here’s a formatted view of the output:
{
"1": {
"color": "matte black",
"weight": "1.2kg",
"material": "aluminum"
},
"2": {
"color": "white",
"weight": "0.9kg"
},
"3": {
"color": "orange",
"weight": null
},
"4": {
"color": null,
"weight": "1.9kg"
}
}
Here, the outer object uses ProductID as the key, and the value is each product’s attributes pre-aggregated into its own JSON object.
The CTE handles the per-product aggregation, and the outer query rolls all of those objects into one.
Type Handling
JSON_OBJECTAGG() returns nvarchar(max) by default. If you’re on SQL Server 2025+ and want the native json type instead, add RETURNING JSON:
JSON_OBJECTAGG(AttrName : AttrValue) RETURNING JSONThe native json type is validated at storage time and can be more efficient to work with downstream, but nvarchar(max) is the default if you leave the clause off.
As for the values inside the object, SQL Server maps your SQL types to JSON types automatically:
INT,BIGINT,DECIMAL,FLOATbecome JSON numbers (no quotes)VARCHAR,NVARCHAR,CHARbecome JSON strings (quoted)BITbecometrue/falseNULLdepends on yourNULL ON NULL/ABSENT ON NULLsetting
If the automatic mapping isn’t what you need, cast the value explicitly before passing it in. For example, if you have a numeric column stored as varchar and you want it to appear as a JSON number rather than a quoted string:
JSON_OBJECTAGG(MetricName : CAST(MetricValue AS FLOAT))When to Use It
JSON_OBJECTAGG() is a good option when:
- You’re storing attributes in a key-value table and want to return them as JSON per entity
- You’re building an API response layer inside SQL and want to shape the JSON without application-side transformation
- You’re exporting data to a downstream system that expects JSON and you want to do the shaping close to the data
It’s less useful when your JSON structure is static and known at query-write time. In that case, FOR JSON PATH or JSON_OBJECT() (the scalar, non-aggregate version) is simpler and more readable.