Microsoft SQL Server Storing JSON in SQL tables Adding index on JSON path

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

Queries that filter or sort data by some value in JSON column usually use full table scan.

SELECT * FROM ProductCollection
WHERE JSON_VALUE(Data, '$.Color') = 'Black'

To optimize these kind of queries, you can add non-persisted computed column that exposes JSON expression used in filter or sort (in this example JSON_VALUE(Data, '$.Color')), and create index on this column:

ALTER TABLE ProductCollection
ADD vColor as JSON_VALUE(Data, '$.Color')

CREATE INDEX idx_JsonColor
ON ProductCollection(vColor)

Queries will use the index instead of plain table scan.



Got any Microsoft SQL Server Question?