JSON Support for SQL Server

Documentation › Indexing

Indexing

Guide to indexing values from JSON.

In SQL Server, to index the result of a function call, you first need to create a computed column storing the result of the function call. You then create an index on that computed column.

Once an index is created in this fashion, SQL Server will use the index whether you reference the column name or the expression used to create the column.

Important Note:

If you wish to index a string, make sure you use the dbo.JsonNVarChar450() or JsonDataType.QueryNVarChar450() functions, rather than the NVarCharMax equivalents, as SQL Server indexes are limited to 900 byte entries (nvarchar data is 2 bytes per character). If you use NVarCharMax, inserts will fail if any of your indexed data is more than 450 characters long.

Example

create table Person ( Id int identity(1, 1), Data nvarchar(max), Name as dbo.JsonNVarChar450(Data, 'Name') persisted ) go create index IX_Person_Name on Person(Name) go insert into Person (Data) values ('{"Name":"Charles","Age":39}') insert into Person (Data) values ('{"Name":"Matthew","Age":12}') select * from Person where Name like 'Ch%' --uses index select * from Person where dbo.JsonNVarChar450(Data, 'Name') = 'Matthew' --also uses index go create table Mountain ( Id int identity(1, 1), Data json, Height as Data.QueryInt('Dimensions.Height') persisted ) go create index IX_Mountain_Height on Mountain(Height) go insert into Mountain (Data) values ('{"Name":"Mt. Fuji","Dimensions":{"Height":3776}}') insert into Mountain (Data) values ('{"Name":"Mt. Everest","Dimensions":{"Height":8848}}') select * from Mountain where Height > 8000 --uses index select * from Mountain where Data.QueryInt('Dimensions.Height') < 4000 --also uses index