JSON_ARRAYAGG and JSON_OBJECTAGG: Building JSON From Rows in SQL Server 2025

A customer needs one JSON list, but the database gives you several order rows. SQL Server 2025 adds JSON_ARRAYAGG and JSON_OBJECTAGG to build arrays and objects directly from those rows.

Start With the Shape the Consumer Needs

An array preserves a sequence of values. An object associates property names with values. Choose between them from the consuming contract, not from whichever function name looks shorter. An order-number list and a settings dictionary solve different representation problems.

I ask to see an example of the expected JSON before writing the aggregate. A list of numbers differs from a list of objects containing numbers. Both are valid JSON, but an application expecting one will not automatically accept the other. Brackets are surprisingly strict about their job.

Run these examples on SQL Server 2025 in one SSMS session. They create temporary teaching data, including a null order number and a customer without orders. The numbers are invented inputs, not observed database results. Keep numeric identifiers numeric when the JSON contract expects JSON numbers.

CREATE TABLE #JsonCustomers (CustomerID int NOT NULL PRIMARY KEY);
CREATE TABLE #JsonOrders
(
    EntryID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderNumber int NULL
);
INSERT #JsonCustomers VALUES (10), (20), (30);
INSERT #JsonOrders VALUES
(1, 10, 102), (2, 10, 101), (3, 10, NULL), (4, 20, 201);

Build One JSON_ARRAYAGG Array for Each Customer

GROUP BY gives each customer one aggregate row. JSON_ARRAYAGG takes the order-number expression and builds the corresponding array. The function handles JSON quoting and type representation. Do not assemble the brackets and commas manually around concatenated values.

Put the element ORDER BY inside the aggregate. The outer ORDER BY controls which customer row appears first, not the sequence inside each array. If duplicates need a stable position, include a tie breaker or deduplicate deliberately according to the business rule.

The following query omits null order numbers explicitly. A duplicate order number remains a duplicate array element. JSON aggregation is not a hidden uniqueness check. Keep key constraints and duplicate validation in the relational data that supplies the output.

SELECT CustomerID,
    JSON_ARRAYAGG(OrderNumber ORDER BY OrderNumber, EntryID ABSENT ON NULL) AS OrderNumbers
FROM #JsonOrders
GROUP BY CustomerID
ORDER BY CustomerID;

How JSON_ARRAYAGG Treats Missing Values and JSON Null

JSON_ARRAYAGG defaults to ABSENT ON NULL. A null expression contributes no array element. NULL ON NULL keeps a JSON null element instead. Choose the rule based on whether a missing position has meaning for the consumer.

A SQL NULL and the text 'null' also differ. The text becomes a JSON string, while the SQL null policy decides omission or a JSON null value. Do not substitute the word null into a text column and expect the aggregate to recognize it as a missing value.

The next comparison orders by the entry identifier so the null input's position remains clear. Test these cases alongside ordinary data. If order numbers are required, reject missing numbers upstream rather than quietly accepting whichever null policy makes the document look neat.

SELECT CustomerID,
    JSON_ARRAYAGG(OrderNumber ORDER BY EntryID ABSENT ON NULL) AS OmittedNulls,
    JSON_ARRAYAGG(OrderNumber ORDER BY EntryID NULL ON NULL) AS RetainedNulls
FROM #JsonOrders
GROUP BY CustomerID
ORDER BY CustomerID;

Include Customers With No Orders

Grouping only the order table does not produce a row for a customer with no order rows. Start from the customer table when every customer must appear. The correlated aggregate below returns each customer's array and normalizes a missing aggregate value to an empty array.

That makes the empty-collection contract explicit. An empty array, SQL NULL, and an absent JSON property have different meanings. Keep the choice consistent across endpoints. What should your consumer receive when a customer has no orders at all? A left join over a synthetic null child also needs careful null handling so it does not accidentally create a one-element null array.

SELECT c.CustomerID, COALESCE(a.OrderNumbers, N'[]') AS OrderNumbers
FROM #JsonCustomers AS c
OUTER APPLY
(
    SELECT JSON_ARRAYAGG(o.OrderNumber ORDER BY o.OrderNumber, o.EntryID ABSENT ON NULL) AS OrderNumbers
    FROM #JsonOrders AS o
    WHERE o.CustomerID = c.CustomerID
) AS a
ORDER BY c.CustomerID;

Build an Object From Key-Value Rows

JSON_OBJECTAGG uses a property-name expression followed by a colon and a value expression. The example enforces a non-null unique setting key before aggregation. That keeps duplicate-property ambiguity out of the generated object.

The default here is NULL ON NULL, unlike the array aggregate's default. A null setting therefore remains a property with a JSON null value. ABSENT ON NULL removes that entire property. An object has no application-level ordering contract, so access values by property name rather than property position.

Settings values are text in this sample. A textual 'true' remains a string, not a JSON boolean. Define the input types and output schema deliberately when a settings document mixes numbers, booleans, and strings. Do not convert every value to text merely to fit one generic storage column.

CREATE TABLE #JsonSettings
(
    SettingKey nvarchar(40) NOT NULL PRIMARY KEY,
    SettingValue nvarchar(100) NULL
);
INSERT #JsonSettings VALUES
(N'Theme', N'Dark'), (N'TimeZone', N'UTC'),
(N'DisplayName', N'Avery "Quinn"'), (N'OptionalNote', NULL);
SELECT JSON_OBJECTAGG(SettingKey:SettingValue NULL ON NULL) AS AllSettings,
    JSON_OBJECTAGG(SettingKey:SettingValue ABSENT ON NULL) AS PresentSettings
FROM #JsonSettings;

Compare the Existing Document Shape

FOR JSON PATH remains useful for complete structured documents. The older nested-query pattern below returns each customer with an array of order objects. JSON_QUERY tells the outer formatter that the nested value is JSON rather than ordinary text to quote. Without the COALESCE, a customer with no orders loses the Orders property completely.

This is an object array, not the primitive number array built earlier. Preserve that distinction when replacing existing output. A migration to shorter SQL should not silently change a consuming application's contract. The next section produces the same object-array structure with the new aggregate.

SELECT c.CustomerID,
    JSON_QUERY
    (
        COALESCE
        (
            (SELECT o.OrderNumber
             FROM #JsonOrders AS o
             WHERE o.CustomerID = c.CustomerID AND o.OrderNumber IS NOT NULL
             ORDER BY o.OrderNumber, o.EntryID
             FOR JSON PATH),
            N'[]'
        )
    ) AS Orders
FROM #JsonCustomers AS c
ORDER BY c.CustomerID
FOR JSON PATH;

Match That Structure With JSON_ARRAYAGG

Construct one JSON object per order, then aggregate those objects. The explicit cast to the SQL Server 2025 json type preserves their JSON structure as elements. Feeding ordinary unmarked text containing braces would risk producing strings rather than the intended nested objects.

The CTE groups those objects by customer. The outer query retains every customer and supplies an empty collection where needed. JSON_QUERY again embeds the aggregate text into the final FOR JSON document. Compare parsed structures rather than expecting identical whitespace in the generated text.

;WITH OrderArrays AS
(
    SELECT CustomerID,
        JSON_ARRAYAGG
        (
            CAST(JSON_OBJECT('OrderNumber': OrderNumber) AS json)
            ORDER BY OrderNumber, EntryID ABSENT ON NULL
        ) AS OrderObjects
    FROM #JsonOrders
    WHERE OrderNumber IS NOT NULL
    GROUP BY CustomerID
)
SELECT c.CustomerID,
    JSON_QUERY(COALESCE(a.OrderObjects, N'[]')) AS Orders
FROM #JsonCustomers AS c
LEFT JOIN OrderArrays AS a ON a.CustomerID = c.CustomerID
ORDER BY c.CustomerID
FOR JSON PATH;

Validate the Contract and Measure the Query

I test quoted text, nulls, empty groups, duplicate candidates, and ordering before replacing a JSON query. The functions handle escaping, but they cannot decide your business contract. A document can be syntactically valid while omitting a required customer or changing a number into text.

Keep filtering and joins correct before serialization. Inspect the actual plan for the relational work and measure representative data on your own server. Shorter JSON construction does not prove lower CPU, fewer reads, or smaller memory requirements without that comparison.

Use JSON_ARRAYAGG for a true array aggregation and JSON_OBJECTAGG for a reviewed key-value object. Keep FOR JSON PATH where its document shaping fits. Combining them gives each expression a clear job and preserves the output your consumer already understands.

Related reading on this blog: Storing JSON in SQL Server and STRING_AGG Limits: The 8,000 Byte Error, Ordering and Duplicates.

A JSON aggregate is not a substitute for a schema, it is a concise way to build the shape you chose.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.