SQL Server JSON Functions and Formatting Options

Quick Answer: Can SQL Server Format a JSON String?

Yes, but only up to a point. SQL Server can generate JSON from query results with FOR JSON, validate JSON with ISJSON, read it with OPENJSON, and update it with JSON_MODIFY.

What it does not provide is a general-purpose pretty-printer for arbitrary JSON text. Current Microsoft documentation describes FOR JSON as returning compact JSON text. If your goal is to make an existing JSON string readable, the usual workflow is:

• Use SQL Server to produce or validate the JSON.
• Use client tooling or a JSON formatter to indent and inspect it.
• Use JSON functions inside SQL Server only when you need to query, filter, or reshape the data.

Generating JSON with FOR JSON

SQL Server supports two FOR JSON modes:PATH and AUTO.PATH is usually the better default because it gives you explicit control over property names and nesting.

FOR JSON PATH

In PATH mode, use column aliases to shape the JSON. Nested objects are created with dot-separated aliases like [customer.email].

SELECT
    CustomerID AS id,
    Name AS [customer.name],
    Email AS [customer.email]
FROM Customers
FOR JSON PATH;

Example output:

[
  {
    "id": 1,
    "customer": {
      "name": "Alice",
      "email": "alice@example.com"
    }
  },
  {
    "id": 2,
    "customer": {
      "name": "Bob",
      "email": "bob@example.com"
    }
  }
]

FOR JSON AUTO

AUTO is faster to type when you just want JSON output that mirrors the tables and joins in your SELECT. The tradeoff is less control over property names and nesting.

SELECT
    CustomerID,
    Name,
    Email
FROM Customers
FOR JSON AUTO;

Formatting Options That Matter

• WITHOUT_ARRAY_WRAPPER returns a single object instead of wrapping one row in [].
• INCLUDE_NULL_VALUES keeps properties whose SQL value is NULL. By default, SQL Server omits them.
• ROOT('name') adds a wrapper object at the top level.

SELECT
    CustomerID AS id,
    Name AS name,
    Email AS email
FROM Customers
WHERE CustomerID = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES;

Example output:

{
  "id": 1,
  "name": "Alice",
  "email": null
}

Notice that the JSON SQL Server returns is still compact text under the hood. If you want human-friendly indentation, format the result in your editor or a JSON formatting tool after the query runs.

Reading JSON with OPENJSON, JSON_VALUE, and JSON_QUERY

Once JSON is stored in a column or variable, SQL Server gives you three main patterns:

• OPENJSON for turning JSON into rows and columns.
• JSON_VALUE for extracting one scalar value.
• JSON_QUERY for extracting an object or array.

OPENJSON with an Explicit Schema

Use a WITH clause when you know the expected shape. This is also where AS JSON belongs: it tells SQL Server to return a nested object or array as JSON text instead of coercing it to a scalar.

DECLARE @payload NVARCHAR(MAX) = N'{
  "orderId": 42,
  "status": "paid",
  "items": [
    { "sku": "A-1", "qty": 2 },
    { "sku": "B-9", "qty": 1 }
  ]
}';

SELECT
    orderId,
    status,
    items
FROM OPENJSON(@payload)
WITH (
    orderId INT '$.orderId',
    status NVARCHAR(20) '$.status',
    items NVARCHAR(MAX) '$.items' AS JSON
);

If you need to shred an array into rows, point OPENJSON at the array path directly:

SELECT sku, qty
FROM OPENJSON(@payload, '$.items')
WITH (
    sku NVARCHAR(50) '$.sku',
    qty INT '$.qty'
);

Important compatibility note: Microsoft documents OPENJSON as requiring database compatibility level 130 or higher.

JSON_VALUE vs JSON_QUERY

The difference is simple and important:

• JSON_VALUE returns a scalar, such as text, number, or boolean.
• JSON_QUERY returns a JSON object or JSON array.

SELECT
    JSON_VALUE(@payload, '$.status') AS OrderStatus,
    JSON_QUERY(@payload, '$.items') AS ItemsJson;

JSON paths use lax mode by default, which returns NULL for a missing path. Use strict when a missing property should raise an error instead of failing quietly.

Updating and Validating JSON

JSON_MODIFY

JSON_MODIFY updates one path at a time and returns the modified JSON string. It is useful for targeted changes without rebuilding the whole document by hand.

DECLARE @doc NVARCHAR(MAX) = N'{
  "status": "draft",
  "tags": ["internal"]
}';

SET @doc = JSON_MODIFY(@doc, '$.status', 'published');
SET @doc = JSON_MODIFY(@doc, 'append $.tags', 'public');

SELECT @doc AS ModifiedJson;

ISJSON

ISJSON is the fast first check before you trust a payload. On newer SQL Server releases, Microsoft also documents optional JSON type constraints such as OBJECT, ARRAY, and SCALAR.

SELECT
    ISJSON('{"name":"valid"}') AS IsValidJson,
    ISJSON('{"name":"valid"}', OBJECT) AS IsObject,
    ISJSON('["a","b"]', ARRAY) AS IsArray,
    ISJSON('"hello"', SCALAR) AS IsScalar;

Validation is especially useful before you pass JSON into OPENJSON or before you send a JSON string to a formatter for inspection.

Newer JSON Constructors and 2025 Version Notes

SQL Server JSON support is broader now than it was when JSON first arrived in SQL Server 2016. Depending on your version, you may also have SQL/JSON constructor functions such as JSON_OBJECT and JSON_ARRAY, which are a better choice than manual string concatenation.

SELECT JSON_OBJECT(
    'id': CustomerID,
    'name': Name
) AS CustomerJson
FROM Customers;

• Microsoft's current documentation lists JSON_ARRAYAGG and JSON_OBJECTAGG as GA in Azure SQL Database and Azure SQL Managed Instance when using SQL Server 2025 or the Always-up-to-date policy, and as preview features in SQL Server 2025.
• The native json data type is documented as GA in Azure SQL Database and Azure SQL Managed Instance in those 2025 environments, and preview in SQL Server 2025.
• Microsoft also documents CREATE JSON INDEX as a preview feature in SQL Server 2025. Before that, the standard pattern is still computed columns plus normal indexes.

Performance and Troubleshooting

• For frequent filters on one JSON property, expose that property as a computed column and index it.
• Cast indexed JSON values to an appropriate SQL type or length where possible. Microsoft warns that wide values can exceed the nonclustered index key length limit.
• Avoid repeatedly shredding large JSON documents in hot query paths if the same properties are needed all the time. Materialize the fields you filter or join on.
• FOR JSON returns NVARCHAR(MAX) text. Large results may be split across multiple rows in some clients, even though the payload is logically one JSON document.

ALTER TABLE Products
ADD ProductName AS CAST(JSON_VALUE(ProductJson, '$.name') AS NVARCHAR(200));

CREATE INDEX IX_Products_ProductName
ON Products(ProductName);

Which Function Should You Use?

• Use FOR JSON PATH when you need clean, predictable JSON output from relational data.
• Use FOR JSON AUTO when you want a quick JSON export that follows your existing SELECT shape.
• Use OPENJSON when you need rows and columns from a JSON document.
• Use JSON_VALUE for a single scalar value and JSON_QUERY for an object or array.
• Use JSON_MODIFY for a targeted update, and ISJSON before processing untrusted input.

For searchers asking how to "format a JSON string in SQL Server," the key distinction is this: SQL Server is excellent at creating and querying JSON, but readable whitespace formatting is still usually a job for your client or a dedicated formatter.