The following code example creates a sample table with a JSON column and populates it with values from the Application.People table:

USE WideWorldImporters; 
DROP TABLE IF EXISTS dbo.T1; 
CREATE TABLE dbo.T1( 
id INT NOT NULL, 
info NVARCHAR(2000) NOT NULL, 
CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED(id) 
); 
 
INSERT INTO dbo.T1(id, info) 
SELECT PersonID, info FROM Application.People t1 
CROSS APPLY( 
  SELECT ( 
    SELECT t2.FullName, t2.EmailAddress, t2.PhoneNumber,      t2.FaxNumber    FROM Application.People t2 WHERE t2.PersonID = t1.PersonID FOR      JSON AUTO, WITHOUT_ARRAY_WRAPPER 
  ) info 
 ) x  

Assume you want to return rows that have the Vilma Niva value for the FullName property. Since this is a scalar value, you can use the JSON_VALUE function. Before you execute the code, ensure that the actual execution plan will be displayed as well (on the Query menu, click on Include Actual Execution Plan, or click on the Include Actual Execution Plan toolbar button). Now execute the following code:

SELECT id, info 
FROM dbo.T1 
WHERE JSON_VALUE(info,'$.FullName') = 'Vilma Niva'; 

The execution plan for the query is shown in the following screenshot:

The plan shows that a Clustered Index Scan was performed; SQL Server was not able to search for full names within the JSON column in an efficient manner.

To improve the performance of the query, you can create a computed column by using the same expression as in its WHERE clause and then using a non-clustered index on it:

ALTER TABLE dbo.T1 ADD FullName AS  JSON_VALUE(info, '$.FullName'); 
CREATE INDEX IX1 ON dbo.T1(FullName); 

When you execute the same query again, the execution plan is changed:

SELECT id, info 
FROM dbo.T1 
WHERE JSON_VALUE(info,'$.FullName') = 'Vilma Niva'; 

A newly created index is used and the plan is more efficient, as shown in the following screenshot:

Of course, this will work only for a particular JSON path, in this case for the FullName property only. For the other properties, you would need to create additional computed columns and indexes on them. In the case of XML indexes, all nodes and values are covered; they are not related to particular values.

An important feature of JSON indexes is that they are collation-aware. The result of the JSON_VALUE function is a text value that inherits its collation from the input expression. Therefore, values in the index are ordered using the collation rules defined in the source columns.

By using indexes on computed columns, you can improve performance for frequently used queries.