Building an API response shouldn't require stitching quoted strings together. JSON_OBJECT creates a structured object in SQL Server 2022. Pair it with JSON_ARRAY for nested values and choose the null behavior explicitly.
Build a JSON_OBJECT From Typed Values
JSON construction functions handle escaping and representation for supported SQL values. The object function takes key-value pairs with a colon between them. That syntax differs from a SELECT alias.
Keys are expressions yielding character values. Keep property names stable under the API contract. A database column rename doesn't require changing the external property's name when the mapping is explicitly written in the constructor.
I use these constructors when the result is one object for each row. They make that grain visible. The examples require SQL Server 2022 or later, except the separately identified aggregate example.
Sample customers are invented input. The code produces JSON text without claiming observed response size or timing. Validate the output against the consumer's contract before replacing an existing serialization path.
CREATE TABLE #JsonCustomers(CustomerId int PRIMARY KEY,CustomerName nvarchar(60),Email nvarchar(100));
INSERT #JsonCustomers VALUES (1,N'Sample Customer',NULL),(2,N'Second Customer',N'test@example.invalid');
SELECT CustomerId,
JSON_OBJECT('customerId':CustomerId,'name':CustomerName,'email':Email) AS CustomerJson
FROM #JsonCustomers;Customer 1 came back with “email”:null in my run, because JSON_OBJECT keeps null properties by default.
Choose Missing or Explicit Null in JSON_OBJECT
NULL ON NULL includes a property whose value is JSON null. ABSENT ON NULL omits the property instead. The object constructor defaults to including nulls, while the array constructor defaults to omitting them.
Spell the choice out when the consumer depends on it. A missing key and an explicit null can drive different updates. Don't choose the response contract by its shortest syntax.
The comparison below keeps the source row unchanged and varies only serialization. Inspect the output text to see which key is present. Don't treat the two objects as equivalent unless the consumer's contract does.
I keep both cases in the response tests. An endpoint can use null to clear a value and absence to preserve it. Those are different instructions.
SELECT JSON_OBJECT('customerId':CustomerId,'email':Email NULL ON NULL) AS ExplicitNull,
JSON_OBJECT('customerId':CustomerId,'email':Email ABSENT ON NULL) AS MissingNull
FROM #JsonCustomers;
SELECT JSON_ARRAY(1,NULL,3 NULL ON NULL) AS ArrayWithNull,
JSON_ARRAY(1,NULL,3 ABSENT ON NULL) AS ArrayWithoutNull;The ABSENT ON NULL column dropped the email key for customer 1 and kept it for customer 2. The two arrays came back as [1,null,3] and [1,3].
Nest an Array Without Manual Quoting
JSON_ARRAY accepts individual expressions and produces an array. Nest it inside the object constructor for a fixed set of values. The constructors preserve JSON structure for nested constructor results rather than forcing you to concatenate brackets and quotes.
Use JSON_QUERY where needed for stored JSON text. It identifies a structured fragment instead of an ordinary string requiring escaping.
Keep strings, numbers, and booleans under the consumer's expected types. A formatted numeric string isn't the same JSON value as a number. Formatting for display belongs at a different boundary.
The sample uses two fixed labels to demonstrate nesting. A variable-length child collection needs rowset serialization or an aggregate. Don't keep extending a fixed constructor's argument list.
SELECT JSON_OBJECT('customerId':CustomerId,
'name':CustomerName,
'labels':JSON_ARRAY(N'active',N'example')) AS CustomerJson
FROM #JsonCustomers;Each object carried “labels”:[“active”,”example”] as a real nested array, not as a quoted string.
Compare Rowset Serialization
FOR JSON PATH serializes a selected rowset, usually as an array of objects. JSON_OBJECT returns one object value per selected row. Those outputs serve different consumers.
Use FOR JSON PATH when the endpoint needs a collection. Use the constructor when an object expression belongs within another query or response. Neither form eliminates the need to define result grain and ordering for the overall payload.
INCLUDE_NULL_VALUES asks FOR JSON to keep null properties, matching an explicit object-constructor policy. ORDER BY controls collection order when it matters. The returned text isn't a guarantee that property order should become part of the business identity.
Consumers should address object properties by name. An array's item order is a separate contract, so preserve it deliberately when changing the serialization query.
SELECT CustomerId AS customerId,CustomerName AS [name],Email AS email
FROM #JsonCustomers
ORDER BY CustomerId
FOR JSON PATH,INCLUDE_NULL_VALUES;This returned one array holding both customers, with “email”:null kept for the first one.
Use Aggregate Constructors on SQL Server 2025
SQL Server 2025 adds JSON_ARRAYAGG and JSON_OBJECTAGG. These functions construct JSON from rows rather than a fixed argument list. The next block requires that version.
Keep it separate from the SQL Server 2022 examples. An array aggregate assembles the customer's variable-length items. The outer object constructor includes that collection through JSON_QUERY.
Order the array within its aggregate when the consumer needs a repeatable item sequence. For an object aggregate, keys need a defined uniqueness rule. Don't let duplicate key names emerge from an accidental join and assume every parser will interpret them identically.
The SQL aggregate collects the rows supplied. The source query still decides whether those rows represent one authoritative value for each property.
-- SQL Server 2025.
SELECT CustomerId,
JSON_OBJECT('customerId':CustomerId,
'items':JSON_QUERY(JSON_ARRAYAGG(ItemName ORDER BY ItemName))) AS CustomerJson
FROM (VALUES (1,N'Wheel'),(1,N'Frame'),(2,N'Brake')) AS x(CustomerId,ItemName)
GROUP BY CustomerId;
SELECT JSON_OBJECTAGG(CONVERT(nvarchar(20),CustomerId):CustomerName) AS CustomerNames
FROM #JsonCustomers;On SQL Server 2025, customer 1 got “items”:[“Frame”,”Wheel”] in name order. The object aggregate returned one object keyed by customer number.
Validate the Contract at the Boundary
ISJSON checks generated or stored text when you need an independent syntax validation. Typed extraction and path checks establish specific value rules. Constructors relieve you of escaping work, but they don't invent required values or validate a business identity.
Check the source's nullability and type conversions. A valid object can still contain the wrong customer. Check required properties and the value types expected by the API.
Which distinction matters to this consumer, missing or null? Test that alongside quotes, backslashes, Unicode, and empty arrays. Keep rejected source data visible before constructing an outward response.
A serializer is polite about escaping. It has no opinion about whether the content belongs in the response. The query and authorization path must establish that before the JSON leaves the database.
Keep JSON_OBJECT Output Separate From Presentation
Return structured values under a stable property contract. Avoid formatting amounts and dates into arbitrary display strings unless the API requires that exact representation. Compare the old and new payload semantics before deployment, including array order and null handling.
A shorter query is useful when it preserves those behaviors. It is risky when the serialization change quietly becomes an unapproved API change.
Use JSON_OBJECT for row-level object expressions and the appropriate array or rowset mechanism for collections. Keep the version boundary explicit for aggregates. Save a small fixture covering each null and nesting rule.
The finished response should explain its structure without a string-concatenation puzzle. The database can build valid JSON. Your design decides whether it is the correct JSON for the request.
Keep collection size within an agreed response limit. A valid array can still be too large for the endpoint's consumers. Use paging or a separate detail request when the contract requires it.
Constructors solve representation. The surrounding query still needs to return an authorized, bounded amount of data for that request.
Related reading on this blog: JSON_ARRAYAGG and JSON_OBJECTAGG: Building JSON From Rows in SQL Server 2025 and Querying Nested JSON Arrays With OPENJSON.
JSON construction is not string assembly, it is typed values under a response contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
