Skip to main content

MongoDB - Advanced Indexing

We have inserted the following document in the collection named users as shown below −

db.users.insert(
	{
		"address": {
			"city": "Los Angeles",
			"state": "California",
			"pincode": "123"
		},
		"tags": [
			"music",
			"cricket",
			"blogs"
		],
		"name": "Tom Benzamin"
	}
)

The above document contains an address sub-document and a tags array.

Indexing Array Fields

Suppose we want to search user documents based on the user’s tags. For this, we will create an index on tags array in the collection.

Creating an index on array in turn creates separate index entries for each of its fields. So in our case when we create an index on tags array, separate indexes will be created for its values music, cricket and blogs.

To create an index on tags array, use the following code −

>db.users.createIndex({"tags":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
>

After creating the index, we can search on the tags field of the collection like this −

> db.users.find({tags:"cricket"}).pretty()
{
	"_id" : ObjectId("5dd7c927f1dd4583e7103fdf"),
	"address" : {
		"city" : "Los Angeles",
		"state" : "California",
		"pincode" : "123"
	},
	"tags" : [
		"music",
		"cricket",
		"blogs"
	],
	"name" : "Tom Benzamin"
}
>

To verify that proper indexing is used, use the following explain command −

>db.users.find({tags:"cricket"}).explain()

This gives you the following result −

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "mydb.users",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"tags" : {
				"$eq" : "cricket"
			}
		},
		"queryHash" : "9D3B61A7",
		"planCacheKey" : "04C9997B",
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"tags" : 1
				},
				"indexName" : "tags_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"tags" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"tags" : [
						"[\"cricket\", \"cricket\"]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Krishna",
		"port" : 27017,
		"version" : "4.2.1",
		"gitVersion" : "edf6d45851c0b9ee15548f0f847df141764a317e"
	},
	"ok" : 1
}
>

The above command resulted in "cursor" : "BtreeCursor tags_1" which confirms that proper indexing is used.

Indexing Sub-Document Fields

Suppose that we want to search documents based on city, state and pincode fields. Since all these fields are part of address sub-document field, we will create an index on all the fields of the sub-document.

For creating an index on all the three fields of the sub-document, use the following code −

>db.users.createIndex({"address.city":1,"address.state":1,"address.pincode":1})
{
	"numIndexesBefore" : 4,
	"numIndexesAfter" : 4,
	"note" : "all indexes already exist",
	"ok" : 1
}
>

Once the index is created, we can search for any of the sub-document fields utilizing this index as follows −

> db.users.find({"address.city":"Los Angeles"}).pretty()
{
	"_id" : ObjectId("5dd7c927f1dd4583e7103fdf"),
	"address" : {
		"city" : "Los Angeles",
		"state" : "California",
		"pincode" : "123"
	},
	"tags" : [
		"music",
		"cricket",
		"blogs"
	],
	"name" : "Tom Benzamin"
}

Remember that the query expression has to follow the order of the index specified. So the index created above would support the following queries −

>db.users.find({"address.city":"Los Angeles","address.state":"California"}).pretty()
{
	"_id" : ObjectId("5dd7c927f1dd4583e7103fdf"),
	"address" : {
		"city" : "Los Angeles",
		"state" : "California",
		"pincode" : "123"
	},
	"tags" : [
		"music",
		"cricket",
		"blogs"
	],
	"name" : "Tom Benzamin"
}
>

Comments

Post a Comment

Popular posts from this blog

C++ How to use Date and Time

The C++ standard library does not provide a proper date type. C++ inherits the structs and functions for date and time manipulation from C. To access date and time related functions and structures, you would need to include <ctime> header file in your C++ program. There are four time-related types: clock_t, time_t, size_t , and tm . The types clock_t, size_t and time_t are capable of representing the system time and date as some sort of integer. The structure type tm holds the date and time in the form of a C structure having the following elements: struct tm { int tm_sec ; // seconds of minutes from 0 to 61 int tm_min ; // minutes of hour from 0 to 59 int tm_hour ; // hours of day from 0 to 24 int tm_mday ; // day of month from 1 to 31 int tm_mon ; // month of year from 0 to 11 int tm_year ; // year since 1900 int tm_wday ; // days since sunday int tm_yday ; // days since January 1st int tm_isdst ; // hours of daylight savin...
Post a Comment
Read more

Lambda Function with Amazon DynamoDB

DynamoDB can trigger AWS Lambda when the data in added to the tables, updated or deleted. In this chapter, we will work on a simple example that will add items to the DynamoDB table and AWS Lambda which will read the data and send mail with the data added. Requisites To use Amazon DB and AWS Lambda, we need to follow the steps as shown below − Create a table in DynamoDB with primary key Create a role which will have permission to work with DynamoDBand AWS Lambda. Create function in AWS Lambda AWS Lambda Trigger to send mail Add data in DynamoDB Let us discuss each of this step in detail. Example We are going to work out on following example which shows the basic interaction between DynamoDB and AWS Lambda. This example will help you to understand the following operations − Creating a table called customer in Dynamodb table and how to enter data in that table. Triggering AWS Lambda function once the data is entered and sending mail using Amazon SES service. The basic block diagram that ...
Post a Comment
Read more

PERL Some good framework

1. Catalyst is the most popular agile Perl MVC web framework that encourages rapid development and clean design without getting in your way. Catalyst | Perl MVC web application framework 2. Mojolicious is a next generation web framework for the Perl programming language. Back in the early days of the web, many people learned Perl because of a wonderful Perl   ... Mojolicious - Perl real-time web framework 3. Documents for Perl  The Perl Archive Network, the gateway to all things Perl. The canonical location for Perl code and modules. The Comprehensive Perl Archive Network - www. cpan .org
Post a Comment
Read more