Microsoft SQL Server JSON in Sql Server Index on JSON properties by using computed columns
Example
When storing JSON documents in SQL Server, We need to be able to efficiently filter and sort query results on properties of the JSON documents.
CREATE TABLE JsonTable
(
id int identity primary key,
jsonInfo nvarchar(max),
CONSTRAINT [Content should be formatted as JSON]
CHECK (ISJSON(jsonInfo)>0)
)
INSERT INTO JsonTable
VALUES(N'{"Name":"John","Age":23}'),
(N'{"Name":"Jane","Age":31}'),
(N'{"Name":"Bob","Age":37}'),
(N'{"Name":"Adam","Age":65}')
GO
Given the above table If we want to find the row with the name = 'Adam', we would execute the following query.
SELECT *
FROM JsonTable Where
JSON_VALUE(jsonInfo, '$.Name') = 'Adam'
However this will require SQL server to perform a full table which on a large table is not efficent.
To speed this up we would like to add an index, however we cannot directly reference properties in the JSON document. The solution is to add a computed column on the JSON path $.Name, then add an index on the computed column.
ALTER TABLE JsonTable
ADD vName as JSON_VALUE(jsonInfo, '$.Name')
CREATE INDEX idx_name
ON JsonTable(vName)
Now when we execute the same query, instead of a full table scan SQL server uses an index to seek into the non-clustered index and find the rows that satisfy the specified conditions.
Note: For SQL server to use the index, you must create the computed column with the same expression that you plan to use in your queries - in this example JSON_VALUE(jsonInfo, '$.Name'), however you can also use the name of computed column vName
