JSON Indexes in SQL Server 2025

by

Before SQL Server 2025, indexing JSON data meant creating a computed column to extract the value you cared about, then indexing that column. It works, and it’s still a solid approach, but it requires you to know upfront which paths you’ll query, maintain separate columns for each one, and keep those columns in sync with any schema changes. SQL Server 2025 introduces JSON indexes as a native alternative. It’s a single index structure that covers the JSON column directly, without the computed column overhead.

JSON indexes are currently in preview and only available in SQL Server 2025 on-premises. They aren’t available yet on Azure SQL Database, Managed Instance, or Fabric. That said, things could be different by the time you read this article.

Requirements

Before creating a JSON index there are a few hard requirements.

  • The target column must be the native JSON data type.
  • You can’t create a JSON index on a VARCHAR(MAX) or NVARCHAR(MAX) column.
  • The table must also have a clustered primary key, and the clustering key is limited to 31 columns with a maximum key size of 128 bytes.
  • JSON indexes can’t be created on indexed views, table-valued variables, computed JSON columns, or memory-optimized tables.

Syntax

The basic syntax goes something like this:

CREATE JSON INDEX name ON table_name (json_column_name)
  [ FOR ( sql_json_path [ , ...n ] ) ]
  [ WITH ( <json_index_option> [ , ...n ] ) ]
  [ ON { filegroup_name | "default" } ]
[ ; ]

See the Microsoft documentation for the full syntax.

The FOR clause is optional. Without it, the index covers all paths in the JSON document recursively from the root. With it, you can narrow the index to specific paths, which can be useful when your documents are large and you only query a handful of properties.

Basic Example

Suppose you create the following table:

CREATE TABLE support_tickets (
    ticket_id   INT     NOT NULL PRIMARY KEY,
    details     JSON    NOT NULL
);

You can either index the entire JSON document:

CREATE JSON INDEX ix_tickets_details
    ON support_tickets (details);

Or target only the paths you actually query:

CREATE JSON INDEX ix_tickets_details
    ON support_tickets (details)
    FOR ('$.status', '$.priority', '$.customer_id');

Only one JSON index is allowed per column so you’ll need to choose which is the best option for your situation. You can include as many paths as needed within it, and up to 249 JSON indexes are allowed per table, although you’ll only ever have one per JSON column.

Path Rules

Paths in the FOR clause can’t overlap. $.a and $.a.b can’t be specified together because $.a already recursively covers everything beneath it, and SQL Server raises an error if you try. Up to 128 levels of nesting are supported in the path.

If you need to change which paths are indexed, there’s no ALTER JSON INDEX so you’ll need to drop and recreate it. Keep this in mind if your JSON schema is still evolving.

The following CREATE JSON INDEX statement raises an error due to overlapping paths:

CREATE JSON INDEX ix_bad
    ON support_tickets (details)
    FOR ('$.customer', '$.customer.id');

Output:

Msg 13683, Level 16, State 1, Line 1
Invalid JSON paths in JSON index.

The following statement is fine, as there are no overlapping siblings:

CREATE JSON INDEX ix_good
    ON support_tickets (details)
    FOR ('$.customer.id', '$.customer.name');

Output:

Commands completed successfully.

Array Search Optimization

By default, JSON indexes are optimized for object property lookups. If your queries frequently search inside JSON arrays, you can enable OPTIMIZE_FOR_ARRAY_SEARCH to improve performance for those patterns:

CREATE TABLE event_log (
    event_id    INT     NOT NULL PRIMARY KEY,
    payload     JSON    NOT NULL
);

CREATE JSON INDEX ix_event_payload
    ON event_log (payload)
    WITH (OPTIMIZE_FOR_ARRAY_SEARCH = ON);

This pairs naturally with JSON_CONTAINS() queries that use [*] wildcards in the path expression. If your use case is primarily object lookups with no array searching, leave it at the default OFF.

How the Index is Stored

Microsoft’s official docs don’t describe the internal storage mechanism, but Daniel Hutmacher and MSSQLTips have both found that JSON indexes aren’t stored as a traditional B-tree index structure on the table. Instead, SQL Server maintains a hidden internal table (visible in sys.objects with type IT and named in the pattern sys.json_index_{object_id}_{index_id}) and keeps it updated atomically as the base table changes. It shows up in sys.indexes like any other index, but the actual data lives in this separate structure. This is the same pattern used by XML indexes and spatial indexes.

SQL Server also appears to create a non-clustered index on the internal table itself. This is visible as json_index_posting_col_nci in sys.indexes. So the JSON index you define is really two objects under the hood: the internal table that stores the extracted key/value pairs, and a non-clustered index on that table that makes seeks possible. This compounds the storage cost because you’re paying for both structures, not just one index’s worth of space.

So if you calculate table size using sys.dm_db_partition_stats, the JSON index data won’t be included in the result. You’ll need to account for the internal table separately to get an accurate total. The size query in the Index Size section below looks at this.

Which Functions Can Use JSON Indexes

The Microsoft documentation lists JSON_VALUE(), JSON_PATH_EXISTS(), and JSON_CONTAINS() as the functions that can take advantage of a JSON index.

JSON_VALUE() supports equality searches, range queries, and IN lists. You can also use the RETURNING clause to specify the expected type:

-- Equality
SELECT * FROM support_tickets
WHERE JSON_VALUE(details, '$.status') = 'open';

-- Range with explicit type
SELECT * FROM support_tickets
WHERE JSON_VALUE(details, '$.priority' RETURNING INT) BETWEEN 1 AND 3;

JSON_PATH_EXISTS() supports equality with = 1 or = 0:

SELECT COUNT(*) FROM support_tickets
WHERE JSON_PATH_EXISTS(details, '$.escalation_notes') = 1;

JSON_CONTAINS(), which was introduced in 2025, searches for a SQL value in a path in a JSON document:

SELECT ticket_id FROM support_tickets
WHERE JSON_CONTAINS(details, 'billing', '$.tags[*]') = 1;

Real-World Performance

The documentation says all three functions can benefit from a JSON index, and for JSON_CONTAINS() that largely holds up in practice. However, for JSON_VALUE(), Brent Ozar’s testing found that query plans consistently join back to the clustered index even when the JSON index should be sufficient – a behaviour Daniel Hutmacher observed as well.

This will be a major gotcha if you’re evaluating whether to move away from computed columns. For queries that already use JSON_VALUE(), the computed column pattern is probably more predictable – at least for now. JSON indexes are the clearer win for JSON_CONTAINS() queries, especially against arrays.

With that being said, things could be different by the time you read this, so do your own testing to be sure.

Index Size

JSON indexes can be significantly larger than the data they index. In the same article mentioned above, Brent Ozar reported a 1GB table producing roughly a 12GB JSON index. The internal structure indexes all key/value pairs recursively, which compounds quickly on documents with many properties. You’ll need to factor this into capacity planning before deploying, and check index size after creation.

Here’s a query to check the index size:

-- Get the object_id of the internal table backing the JSON index
DECLARE @base_object_id INT = OBJECT_ID('support_tickets');

SELECT
    o.name              AS object_name,
    o.type_desc         AS object_type,
    i.name              AS index_name,
    SUM(a.total_pages) * 8 / 1024.0 AS size_mb
FROM sys.objects o
JOIN sys.indexes i
    ON i.object_id = o.object_id
JOIN sys.partitions p
    ON p.object_id = o.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a
    ON a.container_id = p.partition_id
WHERE o.object_id = @base_object_id          -- the base table itself
   OR o.parent_object_id = @base_object_id   -- internal tables owned by it
GROUP BY o.name, o.type_desc, i.name
ORDER BY o.type_desc, i.name;

This also returns the aforementioned json_index_posting_col_nci non-clustered index.

Online Builds Aren’t Supported

Creating, rebuilding, or dropping a JSON index acquires a Schema Modification (Sch-M) lock on the table for the full duration of the operation, blocking all reads and writes. ONLINE = ON is not supported and raises an error, at least in the current version (SQL Server 2025 (17.x)). I’ve found that even ONLINE = OFF causes an error (omitting the clause altogether is the only solution). In any case, you’ll need to plan index creation and rebuilds during maintenance windows accordingly.

Two related quirks from the official docs worth knowing:

  • MAXDOP is syntactically accepted but CREATE JSON INDEX currently always uses a single processor regardless of what you specify.
  • Data compression syntax is also accepted but isn’t actually supported in the current build (SQL Server 2025 (17.x)). Specifying it won’t cause an error, but it won’t do anything either.

Index Hints Aren’t Supported

You can’t force a JSON index with a query hint. If the optimizer doesn’t choose to use it, there’s currently no way to override that decision.

When to Use a JSON Index

As of SQL Server 2025 (17.x), JSON indexes are most worth using when all of the following are true:

  • you’re on SQL Server 2025
  • your column is the native JSON type, and
  • your queries use JSON_CONTAINS(), particularly against arrays.

That’s the combination that delivers reliable index seeks, at least for now.

If your queries primarily use JSON_VALUE(), the computed column approach is still more predictable. The two aren’t mutually exclusive either. You can have a JSON index on a JSON column and still maintain computed columns for specific high-frequency paths that need to be fastest.

JSON indexes have clear long-term potential. The rough edges around JSON_VALUE() seek behaviour, index size, and the lack of online builds are reasonable expectations for a preview feature, and all worth revisiting as SQL Server evolves.