Introduction
Modern enterprise applications frequently store semi-structured data in JSON format. SQL Server, PostgreSQL, MySQL, and other relational databases now offer native JSON support, allowing flexible storage models without moving to a full NoSQL stack.
However, storing JSON is not enough. Real performance comes from correctly indexing JSON paths so that the database does not have to parse and scan JSON on every query.
This article is a production-ready guide on how to effectively index JSON columns for high-performance semi-structured queries. The focus is SQL Server, but applicable techniques are also discussed for PostgreSQL and MySQL.
Why Index JSON Data?
JSON is popular for use cases such as:
Audit trails
Dynamic attributes of products or users
API payload storage
Event logs and telemetry
Search filters that vary by tenant or module
Without proper indexes, JSON queries degrade into full table scans and repeated parsing.
JSON Storage Patterns
1. Entity + JSON Column Pattern
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
CustomerId INT,
Payload NVARCHAR(MAX) -- contains JSON
);
Simple but requires indexing paths manually.
2. Hybrid Model (Relational + JSON Attributes)
Store core fields in relational columns, additional flexible fields in JSON.
3. Full JSON Document Table
Typical for logs and events.
Query Patterns on JSON Data
1. Accessing a Single JSON Property
SELECT * FROM Orders
WHERE JSON_VALUE(Payload, '$.status') = 'Shipped';
2. Range Filters on JSON Values
SELECT * FROM Orders
WHERE CAST(JSON_VALUE(Payload, '$.totalAmount') AS DECIMAL(18,2)) > 5000;
3. Searching Inside JSON Arrays
SELECT * FROM Orders
WHERE JSON_QUERY(Payload, '$.tags') LIKE '%urgent%';
4. Using JSON Fields for Joins
SELECT o.*
FROM Orders o
JOIN Customers c ON c.CustomerCode = JSON_VALUE(o.Payload, '$.customerCode');
All these query patterns benefit significantly from indexing.
Indexing JSON Data in SQL Server
SQL Server does not automatically index JSON fields. But you can index computed columns.
Step 1: Create Computed Columns for JSON Paths
ALTER TABLE Orders
ADD Status AS JSON_VALUE(Payload, '$.status');
Step 2: Add Indexes on the Computed Columns
For exact match queries:
CREATE INDEX IX_Orders_Status ON Orders(Status);
For numeric filters:
ALTER TABLE Orders
ADD TotalAmount AS TRY_CAST(JSON_VALUE(Payload, '$.totalAmount') AS DECIMAL(18,2));
CREATE INDEX IX_Orders_TotalAmount ON Orders(TotalAmount);
Benefits
SQL Server indexes the computed column, not the entire JSON.
Removes the need to parse JSON at query time.
Drastically reduces CPU usage.
Persistent vs Non-Persistent Computed Columns
SQL Server indexes require computed columns to be deterministic.
ALTER TABLE Orders
ADD Status AS JSON_VALUE(Payload, '$.status') PERSISTED;
Persisted columns speed up indexing and reduce runtime cost.
Indexing JSON Arrays
JSON arrays are harder to index. SQL Server offers OPENJSON, which can be used with CROSS APPLY.
SELECT o.OrderId
FROM Orders o
CROSS APPLY OPENJSON(o.Payload, '$.tags') AS tag
WHERE tag.value = 'urgent';
Best practice: normalize arrays into a child table
Instead of indexing arrays, store them relationally:

Join the conversation! Your thoughts help the community grow.