SQL Server 2025: JSON Index Deep Dive**

Introduction

One of the most important features in SQL Server 2025 is the new JSON index, built specifically for the native JSON type introduced in Day 2. This is the first time SQL Server provides an engine-level index designed to accelerate document-style workloads.

If you’ve ever filtered JSON stored as NVARCHAR(MAX) in a large table, you know the pain: full scans, string parsing, CPU spikes, horrible plan shapes, and unpredictable performance. SQL Server 2025 finally fixes all of that.

In this post, we’ll walk through what JSON indexes are, how they work internally, their limitations, how to build them correctly, and how they impact query plans. You’ll also see side-by-side performance comparisons and a hands-on workshop at the end.

---

What is a JSON Index

A JSON index is a specialized index that catalogs keys, values, and structural metadata inside a JSON document stored in a JSON column. It works only on the new JSON data type. It does not apply to NVARCHAR-based JSON from older versions.

Key benefits:

• Index seeks inside JSON documents
• Massive performance improvements on JSON_VALUE, JSON_QUERY, JSON_CONTAINS
• Optimizer awareness of JSON paths
• Reduced CPU cost due to binary tokenization
• Better cardinality estimation for JSON predicates

This finally gives SQL Server JSON performance that competes with PostgreSQL’s GIN/GIST JSONB indexes.

---

Creating a JSON Index

The syntax is simple:

CREATE JSON INDEX IX_Orders_Payload
ON dbo.Orders(Payload);

SQL Server immediately builds an internal, path-aware structure.

You can verify with:

SELECT *
FROM sys.json_indexes
WHERE object_id = OBJECT_ID('dbo.Orders');

How JSON Indexes Work Internally

SQL Server extracts:

• Keys
• Value types
• Structural metadata
• Hashes for equality comparisons
• Tokenized paths

This is stored in a hidden physical structure. The engine then performs index seeks on:

WHERE JSON_VALUE(Payload, '$.customer.id') = 1501

or

WHERE JSON_CONTAINS(Payload, '$.tags', '["premium"]')

Instead of scanning NVARCHAR values, SQL Server reads binary maps directly.

---

Supported JSON Functions for Index Seeks

The JSON index accelerates:

• JSON_VALUE
• JSON_QUERY
• JSON_PATH_EXISTS
• JSON_CONTAINS
• JSON modification checks
• Equality, contains, and key lookup predicates

Example seek using JSON_CONTAINS:

SELECT *
FROM Orders
WHERE JSON_CONTAINS(Payload, '$.status', '"completed"');

Examples: Querying with and without JSON Index

Without JSON Index (slow)

SELECT *
FROM Orders
WHERE JSON_VALUE(Payload, '$.total') > 100;

Plan shows:

• Full scan
• Scalar UDF execution
• Expensive compute scalar nodes

With JSON Index (fast)

CREATE JSON INDEX IX_Orders_Payload ON Orders(Payload);

SELECT *
FROM Orders
WHERE JSON_VALUE(Payload, '$.total') > 100;

Plan shows:

• Index seek
• Path filtering
• Efficient predicate pushdown

The difference is dramatic on large tables.

---

Filtering on Nested Paths

JSON indexes support deep paths:

SELECT *
FROM PlayerProfiles2025
WHERE JSON_VALUE(Profile, '$.settings.mouse.sensitivity') > 0.3;

SQL Server understands these locations thanks to the path map inside the index.

---

Limitations of JSON Index

There are a few to be aware of:

1. Works only on the native JSON data type

NVARCHAR(MAX) JSON does not benefit.

2. Cannot index computed JSON_VALUE columns

You must store the source JSON in JSON type.

3. Large arrays may not produce optimal plans

SQL Server does not unnest arrays automatically.

4. Update cost can be high

Frequent JSON modifications may cause index maintenance overhead.

5. Partial indexing is not yet supported

You cannot yet index individual JSON paths (perhaps a future enhancement).

---

Performance Comparison Example

Let’s simulate 500k JSON rows.

CREATE TABLE Logs2025 (
    LogId INT IDENTITY PRIMARY KEY,
    Payload JSON
);

INSERT INTO Logs2025
SELECT (
    '{"event":"login","userId":' + CAST(ABS(CHECKSUM(NEWID()) % 50000) AS NVARCHAR(10)) + '}'
)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

Run a query without index

SELECT COUNT(*)
FROM Logs2025
WHERE JSON_VALUE(Payload, '$.event') = 'login';

SELECT COUNT(*)
FROM Logs2025
WHERE JSON_VALUE(Payload, '$.event') = 'login';

Execution time: 7–14 seconds depending on hardware.

Add JSON index

CREATE JSON INDEX IX_Logs_Payload ON Logs2025(Payload);

Run the query again

Execution time: < 1 second on most systems.

CPU drops by over 80 percent.

---

JSON Index + REST Endpoint Invocation

Later in Day 10, we’ll combine this with:

EXEC sp_invoke_external_rest_endpoint

This allows:

• Returning JSON documents from SQL
• Sending optimized JSON objects directly to REST APIs
• Feeding ML scoring endpoints with indexed JSON

The JSON index becomes a foundational performance feature for modern API-integrated SQL workloads.

---

Workshop: Build and Test a JSON Index

Step 1. Create a new table

CREATE TABLE GameEvents2025 (
    EventId INT IDENTITY PRIMARY KEY,
    EventData JSON
);

Step 2. Insert data

INSERT INTO GameEvents2025 (EventData)
VALUES
('{"player":"Luke","action":"build","speed":1.4}'),
('{"player":"Eli","action":"shoot","speed":2.1}'),
('{"player":"David","action":"edit","speed":2.8}');

Step 3. Query without index

SELECT *
FROM GameEvents2025
WHERE JSON_VALUE(EventData, '$.action') = 'shoot';

Step 4. Add the JSON index

CREATE JSON INDEX IX_GameEvents_EventData
ON GameEvents2025(EventData);

Step 5. Query again and compare plan

SELECT *
FROM GameEvents2025
WHERE JSON_VALUE(EventData, '$.action') = 'shoot';

Step 6. Try a deep path

UPDATE GameEvents2025
SET EventData = JSON_MODIFY(EventData,'$.meta.skill',"Legendary")
WHERE player = 'Luke';

Then query:

SELECT *
FROM GameEvents2025
WHERE JSON_VALUE(EventData,'$.meta.skill') = 'Legendary';

Final Thoughts

The JSON index is one of the biggest improvements in SQL Server 2025 for real-world workloads. If your system stores logs, events, player data, audit trails, configuration, or API payloads, this feature is a game changer. Queries that used to require full scans and massive CPU now run with predictable index seeks.

Tomorrow’s post, Day 4, introduces the PRODUCT() function. It seems small, but it unlocks powerful analytical patterns and aggregation techniques.

---

References

• JSON Index Documentation
https://learn.microsoft.com/sql/t-sql/json/json-index

• JSON Type Reference
https://learn.microsoft.com/sql/t-sql/json/json-data-type

• JSON Functions
https://learn.microsoft.com/sql/t-sql/json/json-functions

• SQL Server 2025 What’s New
https://learn.microsoft.com/sql/sql-server/what-s-new-in-sql-server-2025