Building JSON From Query Results With FOR JSON in SQL Server

Getting rows out of SQL Server as JSON is a one-clause job: add FOR JSON to the end of a query. The clause has two modes, a few options and one trap that catches most people the first time they nest child rows. This article goes through each, from the simplest query to a nested order document, and explains how to keep the output predictable. It complements reading JSON with OPENJSON, which goes the other way.

FOR JSON PATH

PATH is the mode to use. The result is a JSON array with one object per row, and column names become keys:

SELECT id, name, active
FROM customers
FOR JSON PATH;

The shape of the result is an array such as [{"id":1,"name":"Ada","active":true},{"id":2,"name":"Grace","active":false}]. Per the documentation, a bit column is written as true or false, numbers as numbers, strings are quoted and escaped (quotes, backslashes and control characters get the usual JSON escapes), and date and time types are written as ISO 8601 strings.

Dots in an alias create nesting. This is the feature that makes PATH the better mode:

SELECT o.id,
       o.placed,
       c.id   AS [customer.id],
       c.name AS [customer.name]
FROM orders o
JOIN customers c ON c.id = o.customer_id
FOR JSON PATH;

Each row becomes {"id":..,"placed":..,"customer":{"id":..,"name":..}}. Aliases with square brackets are needed because of the dot. Two columns that map to conflicting paths (one a scalar a and another a.b) are an error. You choose the key order by the order of the select list, and the key names by the aliases; a column with no alias gets its column name.

FOR JSON AUTO

AUTO builds the structure from the tables in the FROM clause: a join of two tables yields rows from the first table with an array of the second nested inside. It is quick to write and gives you almost no control over key names and nesting, and the output changes if you change the join. It suits quick exports; use PATH for anything an application depends on.

Options

• **ROOT('orders')** wraps the array in an object: {"orders":[...]}. It is how you produce a document with a named top-level key.

• **WITHOUT_ARRAY_WRAPPER** removes the outer [ and ], so a single-row query returns an object rather than an array of one. Without it, a query for one customer returns [{"id":1,...}], and the application unwraps it.

• **INCLUDE_NULL_VALUES** keeps null columns as "key":null. By default a NULL column is omitted from the object, so two rows can have different key sets. If the consumer expects every key in every row, set the option; if it treats missing and null alike, the default makes smaller output.

Combine as needed: FOR JSON PATH, ROOT('customer'), WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES.

Nesting child rows

A document with an array of children is built with a correlated subquery that itself ends in FOR JSON PATH:

SELECT o.id,
       o.placed,
       (SELECT i.sku, i.qty, i.price
        FROM order_items i
        WHERE i.order_id = o.id
        ORDER BY i.id
        FOR JSON PATH) AS items
FROM orders o
FOR JSON PATH;

The documentation says the inner FOR JSON result is treated as JSON and embedded as is, so items is an array of objects inside each order. The ORDER BY inside the subquery decides the order of the array, which is the only reliable way to control it. An order with no items gets items as NULL, and the key is omitted by default (or null with INCLUDE_NULL_VALUES); there is no automatic empty array. To get [], wrap the subquery with ISNULL(( ... FOR JSON PATH), '[]'), which brings us to the next section, because that wrapped value is no longer a plain subquery.

The double-encoding trap

FOR JSON recognizes the output of a nested FOR JSON subquery as JSON, and that recognition is lost when the JSON text arrives some other way: from a column that holds JSON text, from a scalar function, from ISNULL, CONVERT or a variable. Then the engine treats it as an ordinary string and escapes it. The nested array shows up as "items":"[{\"sku\":\"A1\"...}]", a string full of backslashes, instead of an array. This is the same class of bug as SQLite's lost subtype and it has the same fix: tell the engine it is JSON. In SQL Server that is JSON_QUERY:

SELECT o.id,
       JSON_QUERY(o.metadata) AS metadata,
       JSON_QUERY(ISNULL((SELECT i.sku, i.qty FROM order_items i
                          WHERE i.order_id = o.id FOR JSON PATH), '[]')) AS items
FROM orders o
FOR JSON PATH;

JSON_QUERY(o.metadata) embeds a stored JSON document as an object, not a string, and the wrapped ISNULL makes an empty array for orders without items. Whenever a nested value in the output looks like a string full of escaped quotes, a JSON_QUERY is missing. Because JSON_QUERY returns NULL for a value that is not an object or an array, make sure the column really holds one.

Getting the result out

Two practical points about the output. First, it is an nvarchar(max) value, and when you run a FOR JSON query directly in a client tool without assigning it to a variable, the documentation notes that the result is returned as multiple rows of about 2 KB each, which a client must concatenate; SSMS shows them as several rows, and an application reading a result set has to read them all and join the pieces. Assigning to a variable (SET @json = (SELECT ... FOR JSON PATH)) gives a single value. Second, there is no pretty printing: the output is compact. Use a formatter on the other side; the JSON formatter in the browser does it without sending the data anywhere.

The 2022 constructors

SQL Server 2022 added JSON_OBJECT and JSON_ARRAY, which build a value from expressions rather than from a result set, with NULL ON NULL and ABSENT ON NULL clauses:

SELECT JSON_OBJECT('id': o.id, 'total': o.total ABSENT ON NULL) FROM orders o;

They suit building one object per row inside a larger query and avoid FOR JSON's result-set orientation. For aggregating many rows into an array they do not help; FOR JSON is still the way, and newer releases with a native json type add aggregate functions for the same job. Check which your version has. In the open-source engines, the aggregate form is jsonb_agg (aggregating rows into JSON), JSON_ARRAYAGG (MySQL) and json_group_array.

Performance and size

A FOR JSON query is still just a query: it reads the rows and builds the text. Correlated subqueries per parent row cost one lookup per parent, as in the other databases, so index the foreign key (order_items(order_id)) before blaming the clause. A document of several megabytes is built in memory and should be paged. If a consumer needs millions of rows, stream them as JSON Lines from the application rather than building one value (JSON Lines for data pipelines and log files).

When FOR JSON is not the right way to build the document

The shape depends on the client. Application serializers are easier to version and test than SQL strings.

You need an exact key order or number formats. FOR JSON follows column order and SQL formatting; money columns come out as plain numbers, and a decimal has the precision of its type.

You need an empty array for no children everywhere. You will write the ISNULL and JSON_QUERY wrapper repeatedly, which suggests a view or application code.

The output is large. One nvarchar(max) value is a lot of memory on both sides.

You must use a version without it. FOR JSON needs SQL Server 2016 and compatibility level 130.

The reference is Microsoft's documentation on FOR JSON and on JSON_QUERY, and the overview in the comparison of JSON in the four engines.