How to index JSON data in SQL Server

      Json does not have a built-in data type, it does not have custom indexes either. You need to use standard indexes to optimize json queries. You don't want SQL Server to perform a full table scan to run any of your queries, that's why you have indexes. Let's say you save the json objects in the following format in a table named JsonTable and your query need to search by ProductId.

{
  "Products":[
    {"Product":{"ProductId":1,"Name":"60W Light Bulb","Price":"10.00", 
     "Alike": { "AlikeProducts" : ["2"]}}},
    {"Product":{"ProductId":2,"Name":"100W Light Bulb","Price":"7.00"}},      
    {"Product":{"ProductId":3,"Name":"HDMI Cable","Price":"7.00"}},
    {"Product":{"ProductId":4,"Name":"USB Cable","Price":"2.50"}},  
    {"Product":{"ProductId":5,"Name":"Power Cable","Price":"5.00"}},  
    {"Product":{"ProductId":6,"Name":"Monitor","Price":"160.00"}},  
    {"Product":{"ProductId":7,"Name":"Keyboard","Price":"27.00"}}  ,
"{\"Product\":{\"ProductId\":8,\"Name\":\"LED 60W Light Buld\",\"Price\":\"16.00\"}}"]  } 

     To make a search by ProductId, I used JSON_VALUE function in the following example. This  returns the data but SQL Server has to perform a full table scan to accomplish this.

  SELECT Name,Price FROM JsonTable
WHERE JSON_VALUE(value,'$.ProductId')=1

     You can not create an index for ProductId when it's part of a JSON object. We need to take the ProductId out of JSON object in some way so we can create an index on it. Virtual Columns (Computed Columns) can help us in this situation. We create a computed column which can evaluate the ProductId for each row and then we can create an index on that computed column.

ALTER Table JsonTable 

ADD virtualProductId AS JSON_VALUE(value, '$.ProductId') 

CREATE INDEX ix_jsontable_productid

ON JsonTable(virtualProductId)

     I have created a new computed column named virtualProductId in the JsonTable, It's not PERSISTED which makes this column does not occupy additional space in the table. Its value will be computed only when the index needs to be rebuilt. Now important part to make this index work is, you need to use the same expression (JSON_VALUE(value, '$.ProductId')) in your queries. If expression matches, SQL Server will use the index we have created and use it to find the Products in JSON.
 
     But wait, we are not done yet. As you can see in my select statement, I select Name and Price values. I can include Name and Price columns in my index so SQL Server will not do any additional lookups after it finds the Products. To do that, I am going to drop my index and create a new one with INCLUDE option.

DROP INDEX ix_jsontable_productid ON JsonTable

CREATE INDEX i_jsontable_productid

ON JsonTable(virtualProductId) INCLUDE(Name,Price)

     Now when SQL Server finds the Product it will have the Name and Price values too.