MongoDB - Advanced Indexing

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.

MongoDB - Advanced Indexing

As shown below, we inserted the following document into the collection of named users.

db.users.insert(
	{
		"address": {
			"city": "Delhi",
			"state": "Uttar pradesh",
			"pincode": "099123"
		},
		"tags": [
			"music",
			"cricket",
			"blogs"
		],
		"name": "Raj"
	}
)

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

Indexing Array Fields

Suppose we want user documents to be checked based on user tags. We'll create an index for the tag array in the collection for this purpose.

As a result, each of your fields will have a unique index entry created when you build an array index. As a result, in our example, when we establish an index on the tag array, distinct indexes will be formed for the values of music, cricket, and blogs.

Use the following code to build an index on the tag array –

>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" : "099123"
	},
	"tags" : [
		"music",
		"cricket",
		"blogs"
	],
	"name" : "Raj"
}
>

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 we want to search for documents based on fields like city, state, and pincode. Since all these fields are part of the sub-document address field, we will produce an index for all the sub-document fields.

To build an index on all three sub-document fields, 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 as follows for any of the sub-document fields using this index.

> db.users.find({"address.city":"Los Angeles"}).pretty()
{
	"_id" : ObjectId("5dd7c927f1dd4583e7103fdf"),
	"address" : {
		"city" : "Delhi",
		"state" : "uttar pradesh",
		"pincode" : "099123"
	},
	"tags" : [
		"music",
		"cricket",
		"blogs"
	],
	"name" : "Raj"
}  

Note that the expression of the query must follow the order of the specified index. So the above index will support the following queries –

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