Indexing JSON data (Part II) - Full text search

In the previous post I described how you can index JSON fields using standard B-Tree indexes. In this post we will see how to use Full-text search indexes on JSON arrays.

Table structure

Full-text indexes can be used if JSON in your column is formatted as a simple array of scalar values. In the ideal case as array of numbers, but array of strings will also work in most of the cases. In this example I will use Sales.SalesOrder_json table that can be found in official AdventureWorks2016CTP3 database. We can assume that Sales.SalesOrder_json table has the following data:

SalesOrderID	SalesOrderNumber	.........	SalesReasons
1	SO44484	........	["Manufacturer","Quality"]
2	SO44352	........	["Price"]
3	SO44367	..........	["Manufacturer","Quality"]
4	SO44464	.........	["Price","Quality"]

Instead of related tables SalesOrderHeaderSalesReasons and SalesReasons I have placed sales reasons as an array of JSON strings.

If we create Full-text index on SalesReasons column, it will split tokens in this text using separators " and ,. This can be used to improve your queries that search for sales orders by some sales reason values in this array.

Full text index

First we will create full text search catalog and index:

-- Create full text catalog for JSON data
 CREATE FULLTEXT CATALOG jsonFullTextCatalog;
 GO
 
-- Create full text index on SalesReason column.
 CREATE FULLTEXT INDEX ON Sales.SalesOrder_json(SalesReasons)
 KEY INDEX PK_SalesOrder__json_SalesOrderID
 ON jsonFullTextCatalog;
 GO

With the full text index we can easily find all rows where SalesReasons JSON array contains some value (e.g. Price):

SELECT SalesOrderNumber, OrderDate, SalesReasons
 FROM Sales.SalesOrder_json
 WHERE CONTAINS(SalesReasons, 'Price')

We can even use more complex expression with AND, OR, NOT operators:

SELECT SalesOrderNumber, OrderDate, SalesReasons
FROM Sales.SalesOrder_json
WHERE CONTAINS(SalesReasons, 'Price OR Quality')

As you can see, although FTS is not specialized for JSON text, you can use it to improve performance of your queries where you need to filter results by some value in JSON array.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/09/indexing-json-arrays-using-full-text-search-indexes.aspx