Rows from a query need deliberate shaping before a consuming page receives a document. FOR JSON PATH gives SQL Server explicit control over object names, nested properties, and child arrays. Shape the result deliberately, then confirm the client receives one complete JSON value rather than a fragmented display.
Name the Contract Before Writing the Serializer
FOR JSON PATH uses selected column names and aliases to shape JSON properties. Dotted aliases create nested objects. That gives you control over the consumer-facing document without asking application code to rebuild every object from a flat result set. The feature is available in SQL Server 2016 and later.
I start with the expected document shape. Property names, arrays, missing values, and numeric types are part of the contract. A valid JSON document can still be wrong for the consumer if an array becomes a string or a property disappears unexpectedly.
The sample uses temporary parent and child tables in one SSMS session. Its values are synthetic. Keep decimal values as numeric columns when the consumer expects numbers, and dates as dates or deliberately formatted strings according to the contract. Avoid formatting every value into display text before serialization. JSON already has enough punctuation without receiving numbers dressed as prose.
CREATE TABLE #JSONOrders(OrderID int PRIMARY KEY,CustomerName nvarchar(40),NoteText nvarchar(80));
CREATE TABLE #JSONLines(OrderID int,LineID int,Amount decimal(12,2));
INSERT #JSONOrders VALUES(1,N'Alpha',NULL),(2,N'Beta',N'Ready');
INSERT #JSONLines VALUES(1,1,10.00),(1,2,15.00),(2,1,20.00);
SELECT OrderID AS [order.id],CustomerName AS [customer.name]
FROM #JSONOrders ORDER BY OrderID
FOR JSON PATH;Nest Child Rows as an Array With FOR JSON PATH
A correlated subquery can return the child rows for each parent with its own FOR JSON PATH. Wrap that result with JSON_QUERY to make the JSON intent explicit and avoid treating the array as ordinary escaped text. Keep the correlation predicate tied to the parent's stable key.
The next query orders parent rows and child rows independently. If the consumer relies on array order, state the order in the relevant query. Relational rows have no guaranteed presentation order without ORDER BY. A parent ordering does not automatically order its child subquery.
I check the document with a parent that has no children as well as one with several children. The expected empty-array or missing-property behavior needs to be deliberate. Also confirm that the child query does not accidentally include another parent's rows. A perfectly formatted array containing the wrong records is a data bug, not a serialization success.
SELECT o.OrderID AS [order.id],o.CustomerName AS [customer.name],
JSON_QUERY((SELECT l.LineID,l.Amount FROM #JSONLines AS l
WHERE l.OrderID=o.OrderID ORDER BY l.LineID FOR JSON PATH)) AS lines
FROM #JSONOrders AS o
ORDER BY o.OrderID
FOR JSON PATH,ROOT('orders');Decide Whether NULL Properties Should Appear
By default, FOR JSON omits properties whose SQL value is NULL. INCLUDE_NULL_VALUES preserves them as JSON null. That difference matters when the consumer distinguishes absent information from an explicitly empty value. Choose the behavior rather than allowing it to become an accidental API rule.
ROOT wraps the array inside a named top-level property. It can make a response easier to extend later with other fields, but the consuming contract still determines whether that wrapper belongs there. Changing a root name is a document-shape change even when the underlying rows stay identical.
WITHOUT_ARRAY_WRAPPER removes the surrounding array for a result known to contain one object. It is not a general shortcut for an arbitrary multirow result. Multiple rows without the wrapper do not form one valid JSON object. Use a unique predicate and validate the cardinality when the contract expects one object. Zero-row behavior also needs an explicit response policy.
SELECT OrderID,CustomerName,NoteText
FROM #JSONOrders WHERE OrderID=1
FOR JSON PATH,INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER;
Return One Value Instead of Relying on Display Rows
Some clients expose long FOR JSON output in multiple result rows or display fragments. Assigning the generated document to an nvarchar(max) variable and selecting that variable returns one scalar value. The client still needs to read the full value without its own truncation limit.
The following block stores the complete generated array, then checks its validity and character length through queries. Those are measurements from the reader's execution, not invented observed results. LEN is useful for this document inspection, while DATALENGTH provides its byte representation size when needed.
What does your client do with a long result? Test its retrieval path rather than judging the document from a clipped SSMS grid cell. A truncated display does not prove SQL Server generated invalid JSON. Copy the full value through a supported client method and validate the actual received document. Keep transport and rendering issues separate from the serializer's output.
DECLARE @Document nvarchar(max)=
(SELECT OrderID,CustomerName,NoteText FROM #JSONOrders ORDER BY OrderID
FOR JSON PATH,INCLUDE_NULL_VALUES);
SELECT @Document AS JSONDocument,ISJSON(@Document) AS IsValidJSON,
LEN(@Document) AS DocumentCharacters;Consider Constructors for Focused Documents
On SQL Server 2025, JSON_OBJECT and JSON_ARRAYAGG provide clear alternatives for some object and aggregate-array shapes. JSON_OBJECT builds a named object directly, while JSON_ARRAYAGG gathers values into an array. They complement FOR JSON rather than replacing every nested relational serialization.
The next example aggregates order identifiers with an explicit order. Keep its SQL Server 2025 requirement separate from the SQL Server 2016 FOR JSON examples. Choose the expression that makes the intended shape easiest to review on the actual deployment version.
Check NULL handling and returned type for the chosen function. Different constructors have their own options and semantics. Do not assume they produce the same document as a FOR JSON query merely because both results contain brackets. Compare the parsed structure, property presence, and value types. The consumer cares about those properties much more than which serializer looked shorter in the query editor.
SELECT JSON_OBJECT('orderCount':COUNT_BIG(*)) AS SummaryDocument,
JSON_ARRAYAGG(OrderID ORDER BY OrderID) AS OrderIDs
FROM #JSONOrders;Validate FOR JSON PATH Output and Query Performance
Test special characters, NULL values, no parents, no children, and multiple child rows. SQL Server handles JSON escaping, so avoid manually replacing quotation marks as a competing serializer. Validate the generated document and the consumer's parsed structure with representative values.
Inspect the plan for correlated child lookups on the real tables. An index supporting the child relationship can matter when many parents are serialized. A convenient document shape does not remove the cost of locating its rows. Keep the selected observation set bounded according to the response requirement.
Use FOR JSON PATH to express the contract clearly and return a complete scalar document when that suits the client. Preserve the distinction between a valid JSON string, the correct document shape, and a successful client read. Those checks together make the serialization useful beyond a tidy result in SSMS.
Related reading on this blog: JSON_OBJECT and JSON_ARRAY: Building JSON in SQL Server 2022 and Querying Nested JSON Arrays With OPENJSON.
A JSON result is not just a different display, it is a document contract for the consumer.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
