[Demo] Full Text Search in Transactional Database

Hey guys, yesterday I had posted a blog on combining a database and a full text search engine. Today let’s have a practical demo using MongoDb Atlas Search.

Pre requisites

  • Create an account on MongoDb Atlas for free.
  • Install MongoDb Compass for a better UI to build queries and aggregation pipeline. [Optional]

Agenda

  • Create an Atlas M0 Cluster for free and load sample data.
  • Create a Full Text Search index.
  • Create a native MongoDb compound index.
  • Query the same collection using both indexes.

Creating a free cluster

Login to your MongoDb Atlas account and build a new cluster. Preferably chose AWS (you can chose any you like, AWS is just my preference) as provider and chose your nearest location. Keep in mind, if you are using a free tier, your region should have free tier availability. Keep all other settings the same, just make sure you are having MongoDb version 4.2 or above.

Once, your cluster is created, let’s load some sample data for us. Fortunately, MongoDb Atlas has a sample dataset it provides, which is more than enough to query and see various different data types. Let’s load it. Click on the Ellipse button and select Load Sample Dataset. It’ll take a while to load, and we can move ahead once ready.

Load Sample Dataset

Creating a Full Text Search index

Now before we can have Full Text Search queries, we need to enable that by creating a FTS index. To do this, follow the steps below –

  • Click on collections button of your cluster to open the Collections view. Then select the sample_mflix database and chose the movies collection.

"Sample dataset"

  • Click on the tab titled SearchBeta under collections. Let’s create a Search index. Clicking it, it’ll open up a box as below defining the mapping of the FTS index you want on that collection. Going in details later, creating a dynamic mapping lets MongoDb recognize itself which field to index, by default mapping all the text fields (top level or nested).

Create Full Text Search Index

Creating a native MongoDb Compound Index

Now, as we have created a FTS index, let’s create a simple compound index on the same collection. To do that follow the steps –

  • Open MongoDb compass and connect to your atlas cluster (just click on connect button on your cluster homepage, and guide through compass connection).
  • Once connected, choose the sample_mflix database and the movies collection in it. Go to the indexes tab to create indexes.
  • Let’s assume our application now also wants to search the movies through genre and sort via imdb rating. So lets create an index with keys (in order) –
    • genres : 1 (asc)
    • imdb.rating : -1 (desc)
  • DO NOT FORGET TO SELECT THE “Build index in the background” option.

Now as we have created both our FTS index and our a native compound index in our same transactional collection, we have our setup ready and can see the results by querying our collection as below.

Querying – Native Index and Full Text Search

Let’s first try to query the native index. Follow the steps below –

  • On compass, go to the Explain Plan tab of the movies collection.
  • Let’s apply a filter – {“genres”: “Drama”, “imdb.rating”: {$gte: 7}}
  • This above filter will query for all documents having the genre as Drama and imdb rating greater than equal to 7.
  • Let’s also add a sort, click on options on the right of filters and add this to sort – {“imdb.rating”: -1}
  • This will give us the result in descending order of the imdb rating.
  • Let’s see the result below –

Screenshot 2020-04-02 at 2.03.24 PM

As we see, the result used the index {genres, imdb.rating} we had created earlier.

Now let’s query for a Full Text Search. Assuming we want to return all our documents which contain the words – Guns, we need to create an aggregation pipeline to use the $searchBeta stage to query the FTS index. Let’s do that –

  • Go to aggregations tab. For first stage, let’s select $searchBeta as the stage and add this as the parameters –

Screenshot 2020-04-02 at 2.08.54 PM

  • Then, to be able to see the match score, we need to add a $project stage to retrieve that information. Lets create another stage and add these parameters to it –

Screenshot 2020-04-02 at 2.09.56 PM

  • Now if you are getting the same answer, we know that we have the documents returned (in descending order of the match score, by default) in which we find guns in the fullPlot field. The highlights tell us where in the whole fullPlot text field, did we match our matching phrases.
  • In order to see this in a real application, you can visit this deployed link and search for guns, you would get the same result you had in your aggregation stage. The highlights have been marked as coloured as where in the string have you found your matching phrase.

Bonus

Now, you can try searching for the phrase biggit in your Compass aggregation pipeline, by just replacing guns with biggit in your $searchBeta stage. As you do that, you see there are no documents matching that condition. Let’s add a document having this, so that we can see how when an update is made, the document is indexed in FTS on a real time basis.

  • Go to Documents tab on compass and on the first document you see, click the “Copy Document” button. Now go to Add Data -> Insert Document and paste your copied document there.
  • Change the value of fullplot with the string – “hey there Mr. Biggit. How are you doing?” (Just a random string, containing “biggit” :p)
  • Remember to remove the _id field (as no two documents can have the same _id).
  • Insert the document and return to Aggregations tab.
  • Just refresh or press enter after the last closing bracket of $searchBeta stage, without changing any logic.
  • You’ll find that the new document inserted is automatically indexed in FTS index.

Conclusion

We see that we have built an FTS engine with our transactional database without integrating any other third technology or syncing data between two data stores. This makes operations easy and the cost less.

If you like the content, do like, share and follow the blog so that you’ll get email notifications of the new content..! 😀

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

%d bloggers like this: