Microsoft SQL Server Storing JSON in SQL tables Adding index on JSON path
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.
