Microsoft SQL Server Adding index on JSON path


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.