SQL Server 2025 JSON Index

Problem

We’re trying the new JSON data type in SQL Server for data stored as JSON in a table. When we query it using functions such as JSON_VALUE, we see a full table scan is performed for each query. Is there a way we can index the JSON to improve performance?

Solution

As JSON is becoming more and more prevalent in the data world, for example REST APIs typically return a dataset in JSON format, SQL Server is enhancing its support in the database engine itself. In early 2025, the native JSON data type was introduced in Azure SQL DB (and Azure SQL Managed Instance and Fabric SQL DB) to provide better support for JSON. Later, two new functions were added to T-SQL to handle JSON. In the SQL Server 2025 preview, even more new functions were added. At the time of writing not available in the cloud counterparts of the SQL Server engine).

Another addition is a new index type: the JSON index.

In this tip, we’ll introduce this new index and explain how to create and use in your queries. The tip was written using SQL Server 2025 Release Candidate 1 preview. If you want to try out the SQL Server 2025 preview, you can either use a pre-configured VM image with SQL Server CTP 2.1 in Azure, or if you want the latest version of the preview (Release Candidate 1 at the time of writing) you can create an Azure VM and install SQL Server 2025 yourself. Keep in mind that functionality displayed in the preview might change when the final version of SQL Server 2025 is released.

The new JSON index

To speed up query performance for a column containing JSON data, you can create a new type of index: the JSON index.

The syntax is as follows:

--MSSQLTips.com
CREATE JSON INDEX ix_json ON dbo.mytable(myjsoncolumn) FOR ('$.some.path', '$.another.path');

There are quite some limitations for a JSON index:

at the time of writing, it’s only available in SQL Server 2025 (on-premises database engine)
the table needs to have a clustered primary key
the index cannot be created on a view, table-valued variable or memory optimized table
the column on which the index is created needs to be of the JSON data type (which means you cannot use VARCHAR(MAX)) and it cannot be a computed column
you can create only one JSON index per column, but you can specify multiple paths for the same column. If you have multiple JSON columns in your table, you can create one index per JSON column up to 249 JSON indexes. This is a limit, not a goal…
for the moment, data compression options are ignored, and index hints as well

JSON Index Creation

Let’s illustrate with an example. On an AdventureWorks sample database, we can create the following table with a JSON column:

--MSSQLTips.com
DROP TABLE IF EXISTS dbo.JsonTest;
CREATE TABLE dbo.JsonTest
(
     Id             INT IDENTITY(1,1) PRIMARY KEY
    ,CustomerKey    INT
    ,Title          NVARCHAR(8)
    ,FirstName      NVARCHAR(50)
    ,MiddleName     NVARCHAR(50)
    ,LastName       NVARCHAR(50)
    ,Suffix         NVARCHAR(10)
    ,EmailAddress   NVARCHAR(50)
    ,Phone          NVARCHAR(20)
    ,JsonData       JSON);

Using the FOR JSON clause, we can take data from the customer dimension and convert it to JSON. The result is inserted into the table.

--MSSQLTips.com
INSERT INTO dbo.JsonTest(
     CustomerKey
    ,Title
    ,FirstName
    ,MiddleName  
    ,LastName
    ,Suffix
    ,EmailAddress
    ,Phone
    ,JsonData)
SELECT
     CustomerKey
    ,Title
    ,FirstName
    ,MiddleName
    ,LastName
    ,Suffix
    ,EmailAddress
    ,Phone
    ,(
    SELECT
         CustomerKey    AS [CustomerKey]
        ,Title          AS [Name.Title]
        ,FirstName      AS [Name.FirstName]
        ,MiddleName     AS [Name.MiddleName]
        ,LastName       AS [Name.LastName]
        ,Suffix         AS [Name.Suffix]
        ,EmailAddress   AS [ContactInfo.EmailAddress]
        ,Phone          AS [ContactInfo.Phone]
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) myjson
FROM AdventureWorksDW2022.dbo.DimCustomer;
GO 100

To have a bit more data in the table, the INSERT statement is executed 100 times using the GO statement, which only works in SSMS or sqlcmd. The same script is executed to create a table named dbo.JsonTest_Index. Having two identical tables will allow us to compare the performance with or without JSON index. On the second table, we can now create the following JSON index:

--MSSQLTips.com
DROP INDEX IF EXISTS ix_json ON dbo.JsonTest_Index;
CREATE JSON INDEX ix_json ON dbo.JsonTest_Index(JsonData) FOR ('$.Name.FirstName');

It would also be possible to create the following index if you want to support multiple paths:

--MSSQLTips.com
CREATE JSON INDEX ix_json ON dbo.JsonTest_Index(JsonData) FOR ('$.Name.FirstName', '$.ContactInfo.EmailAddress');

You could specify the following paths: '$.Name.FirstName', '$.Name.LastName', but since SQL Server recursively indexes the keys/values from a specified path it would make more sense to just specify '$.Name' (unless you explicitly want to exclude the middle name attribute). SQL Server doesn’t allow you to specify overlapping paths. The combination '$.Name.FirstName', '$.Name' would lead to the error “Invalid JSON paths in JSON index.”

When we query sys.objects, we can see the JSON index is stored as an internal table.

This means that if you want to calculate the size of your table using sys.dm_db_partition_stats, you need to include this internal table as well to get a correct total size.

Querying the JSON index

Let’s test our new JSON index with the new JSON_CONTAINS function.

--MSSQLTips.com
SET STATISTICS IO ON;
 
SELECT FirstName, LastName
FROM dbo.JsonTest
WHERE JSON_CONTAINS(JsonData,'David','$.Name.FirstName') = 1;
 
SELECT FirstName, LastName
FROM dbo.JsonTest_Index
WHERE JSON_CONTAINS(JsonData,'David','$.Name.FirstName') = 1;

We get the following execution plans:

execution plans for query without and with json index

The top query is the table without the JSON index and it needs to do a full clustered index scan. The bottom query can use the JSON index. However, since the index is actually another (internal) table, it needs to do a clustered index seek on that table and use the results in another clustered index seek on the actual table. This is similar in behavior as a non-covering index which needs to do a key lookup on the clustered index to fetch the additional rows.

This means that ideally the JSON index is used to fetch a couple of rows and not a large portion of the table. Looking at the data pages read, we can see that using the JSON index leads to a significant reduction in page reads:

The clustered index scan has over 101,000 reads, while the clustered index seek on the JSON index has 48 reads and the clustered index seek on the table itself has about 17,000 reads.

Let’s change our queries to use JSON_VALUE instead of JSON_CONTAINS.

--MSSQLTips.com
SELECT FirstName, LastName
FROM dbo.JsonTest_Index
WHERE JSON_VALUE(JsonData,'$.Name.FirstName') = 'David';

For reasons unknown, SQL Server does not use the JSON index, even though the documentation states that it is supported:

Summary

Not every operator is supported in the preview, but the comparison operator = should be supported. Microsoft MVP Daniel Hutmacher has an example in his blog post JSON indexes in SQL Server: First impressions where JSON_VALUE does use an index, so it is possible.

Next Steps

Other tips about JSON in SQL Server:
You can find all SQL Server 2025 tips in this overview.

Resources

Community

Subscribe

The New JSON Index in SQL Server 2025