JSON_ARRAYAGG() in SQL Server 2025: Aggregate Rows Into JSON Arrays

JSON_ARRAYAGG() is one of the new features introduced in SQL Server 2025. This is an aggregation function that allows you to combine multiple row values into a single JSON array directly within your SQL queries.

JSON_ARRAYAGG() simplifies the process of generating structured JSON output from relational data, which makes it easier to build APIs, export data, and integrate with modern applications that rely on JSON. Instead of manually constructing JSON with string operations or complex subqueries, JSON_ARRAYAGG() provides a clean, efficient way to transform sets of rows into well-formed JSON arrays as part of standard SQL aggregation.

In this article, we’ll explore how JSON_ARRAYAGG() works and see how it can streamline JSON generation in SQL Server 2025.

What It Does

JSON_ARRAYAGG() is an aggregate function that collects values from multiple rows and returns them as a single JSON array. Each row contributes one element to the array.

The syntax goes like this:

JSON_ARRAYAGG(value_expression [ORDER BY column_list] [NULL ON NULL | ABSENT ON NULL] [RETURNING JSON])

Only value_expression is required. Everything else is optional (which we’ll cover in the examples below).

Sample Data

All examples below use this table:

CREATE TABLE OrderItems (
    OrderID     INT,
    ProductName VARCHAR(100),
    Category    VARCHAR(50),
    Quantity    INT,
    UnitPrice   DECIMAL(10, 2)
);

INSERT INTO OrderItems VALUES
(1, 'Wireless Keyboard', 'Peripherals', 1,  49.99),
(1, 'USB-C Hub',         'Peripherals', 2,  34.99),
(1, 'Monitor Stand',     'Accessories', 1,  29.99),
(2, 'Mechanical Keyboard','Peripherals',1,  89.99),
(2, 'Mouse Pad XL',      'Accessories', 1,  19.99),
(2, 'Webcam HD',         'Peripherals', 1,  74.99),
(3, 'Laptop Sleeve',     'Accessories', 2,  24.99),
(3, 'HDMI Cable',        'Accessories', 3,   9.99),
(3, 'USB Hub',           'Peripherals', 1,  27.99),
(3, NULL,                'Accessories', 1,  14.99);  -- product name unknown

A Simple Example

Let’s start by getting a list of product names per order:

SELECT
    OrderID,
    JSON_ARRAYAGG(ProductName) AS Products
FROM OrderItems
GROUP BY OrderID;

Result:

OrderID  Products                                          
-------  --------------------------------------------------
1        ["Wireless Keyboard","USB-C Hub","Monitor Stand"] 
2        ["Mechanical Keyboard","Mouse Pad XL","Webcam HD"]
3        ["Laptop Sleeve","HDMI Cable","USB Hub"]

Notice that Order 3 has four rows in the table, but only three products show up. That’s because the fourth row has a NULL product name, and ABSENT ON NULL is the default. This means NULL values are silently dropped from the array.

Handling NULL Values

The default ABSENT ON NULL behavior drops nulls. If you want them included as JSON null values instead, use NULL ON NULL:

SELECT
    OrderID,
    JSON_ARRAYAGG(ProductName NULL ON NULL) AS Products
FROM OrderItems
GROUP BY OrderID;

Result:

OrderID  Products                                          
-------  --------------------------------------------------
1        ["Wireless Keyboard","USB-C Hub","Monitor Stand"] 
2        ["Mechanical Keyboard","Mouse Pad XL","Webcam HD"]
3        ["Laptop Sleeve","HDMI Cable","USB Hub",null]

The unknown product is now represented in the array rather than quietly omitted. Which behavior you want often depend on whether the consumer of that JSON cares about position or just content.

Controlling Element Order

Array element order isn’t guaranteed without an explicit ORDER BY inside the function. If order matters (and often it does) you can specify it directly:

SELECT
    OrderID,
    JSON_ARRAYAGG(ProductName ORDER BY ProductName) AS Products
FROM OrderItems
GROUP BY OrderID;

Result:

OrderID  Products                                          
-------  --------------------------------------------------
1        ["Monitor Stand","USB-C Hub","Wireless Keyboard"] 
2        ["Mechanical Keyboard","Mouse Pad XL","Webcam HD"]
3        ["HDMI Cable","Laptop Sleeve","USB Hub"]

You can order by any column available in the group, not just the aggregated value. Ordering by price descending, for example:

SELECT
    OrderID,
    JSON_ARRAYAGG(ProductName ORDER BY UnitPrice DESC) AS ProductsByPrice
FROM OrderItems
GROUP BY OrderID;

Output:

OrderID  ProductsByPrice                                   
-------  --------------------------------------------------
1        ["Wireless Keyboard","USB-C Hub","Monitor Stand"] 
2        ["Mechanical Keyboard","Webcam HD","Mouse Pad XL"]
3        ["USB Hub","Laptop Sleeve","HDMI Cable"]

This lets you control the output shape without needing application-side sorting after the fact.

Combining with Other Aggregates

JSON_ARRAYAGG() works alongside standard aggregates in the same query:

SELECT
    OrderID,
    COUNT(*)                                        AS ItemCount,
    SUM(Quantity * UnitPrice)                       AS OrderTotal,
    JSON_ARRAYAGG(ProductName ORDER BY ProductName) AS Products
FROM OrderItems
GROUP BY OrderID;

Result:

OrderID  ItemCount  OrderTotal  Products                                          
-------  ---------  ----------  --------------------------------------------------
1        3          149.96      ["Monitor Stand","USB-C Hub","Wireless Keyboard"] 
2        3          184.97      ["Mechanical Keyboard","Mouse Pad XL","Webcam HD"]
3        4          122.93      ["HDMI Cable","Laptop Sleeve","USB Hub"]

Here we get all the summary data we might need for an order confirmation payload, all in one pass.

Using with `GROUP BY GROUPING SETS`

JSON_ARRAYAGG() supports GROUP BY GROUPING SETS, which lets you produce multiple levels of aggregation in a single query. This is useful when you want arrays at different grouping levels (e.g. per order, per category, and overall) without running separate queries:

SELECT
    OrderID,
    Category,
    SUM(Quantity * UnitPrice)       AS Subtotal,
    JSON_ARRAYAGG(ProductName)      AS Products
FROM OrderItems
GROUP BY GROUPING SETS((OrderID), (Category), (OrderID, Category), ());

Result:

OrderID  Category     Subtotal  Products                                                                                                                                 
-------  -----------  --------  -----------------------------------------------------------------------------------------------------------------------------------------
1        Accessories  29.99     ["Monitor Stand"]                                                                                                                        
2        Accessories  19.99     ["Mouse Pad XL"]                                                                                                                         
3        Accessories  94.94     ["Laptop Sleeve","HDMI Cable"]                                                                                                           
null     Accessories  144.92    ["Monitor Stand","Mouse Pad XL","Laptop Sleeve","HDMI Cable"]                                                                            
1        Peripherals  119.97    ["Wireless Keyboard","USB-C Hub"]                                                                                                        
2        Peripherals  164.98    ["Mechanical Keyboard","Webcam HD"]                                                                                                      
3        Peripherals  27.99     ["USB Hub"]                                                                                                                              
null     Peripherals  312.94    ["Wireless Keyboard","USB-C Hub","Mechanical Keyboard","Webcam HD","USB Hub"]                                                            
null     null         457.86    ["Monitor Stand","Mouse Pad XL","Laptop Sleeve","HDMI Cable","Wireless Keyboard","USB-C Hub","Mechanical Keyboard","Webcam HD","USB Hub"]
1        null         149.96    ["Wireless Keyboard","USB-C Hub","Monitor Stand"]                                                                                        
2        null         184.97    ["Mechanical Keyboard","Mouse Pad XL","Webcam HD"]                                                                                       
3        null         122.93    ["Laptop Sleeve","HDMI Cable","USB Hub"]

When OrderID is NULL in the result, that row represents the aggregation across all orders for that Category. When both are NULL, it’s the grand total row. The Products array follows the same grouping, so each row’s array reflects exactly the items in that group.

Note that ORDER BY inside JSON_ARRAYAGG() is not supported when used with GROUPING SETS, CUBE, or ROLLUP (SQL Server will raise an error if you try). If element order matters, you’ll need to sort the results after the fact in your application layer.

Nesting with `JSON_OBJECTAGG()`

Here’s an example that uses JSON_ARRAYAGG() in a common table expression (CTE), along with JSON_OBJECTAGG(), which is another aggregate function introduced in SQL Server 2025:

WITH CategoryArrays AS (
    SELECT
        OrderID,
        Category,
        JSON_ARRAYAGG(ProductName ORDER BY ProductName) AS Products
    FROM OrderItems
    GROUP BY OrderID, Category
)
SELECT
    OrderID,
    JSON_OBJECTAGG(Category : Products) AS ByCategory
FROM CategoryArrays
GROUP BY OrderID;

Result:

OrderID  ByCategory                                                                        
-------  ----------------------------------------------------------------------------------
1        {"Accessories":["Monitor Stand"],"Peripherals":["USB-C Hub","Wireless Keyboard"]} 
2        {"Accessories":["Mouse Pad XL"],"Peripherals":["Mechanical Keyboard","Webcam HD"]}
3        {"Accessories":["HDMI Cable","Laptop Sleeve"],"Peripherals":["USB Hub"]}

The CTE builds the per-category arrays, then JSON_OBJECTAGG() wraps them into a single object keyed by category. Clean and readable, even when the output structure gets complex.

Return Type

JSON_ARRAYAGG() returns nvarchar(max) by default. If you want the native json type instead, add RETURNING JSON:

JSON_ARRAYAGG(ProductName ORDER BY ProductName RETURNING JSON)

The native json type is validated at storage time. If you’re storing the result in a column or passing it to something that expects typed JSON, RETURNING JSON is worth adding. Otherwise the default nvarchar(max) is fine.

When to Use It

JSON_ARRAYAGG() can be a very handy tool when:

You’re returning a list of related values per group and want them as a JSON array rather than separate rows
You’re building API response payloads inside SQL and need array-shaped data
You’re pairing it with JSON_OBJECTAGG() to build structured nested JSON without multiple round-trips

It’s less useful when you need arrays with mixed types or complex structures that can’t be expressed as a simple aggregation over a single column.

What It Does

Sample Data

A Simple Example

Handling NULL Values

Controlling Element Order

Combining with Other Aggregates

Using with GROUP BY GROUPING SETS

Nesting with JSON_OBJECTAGG()

Return Type

When to Use It

Using with `GROUP BY GROUPING SETS`

Nesting with `JSON_OBJECTAGG()`