MongoDB Advanced Indexing

We inserted the following documents into 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 tag array.

Index array field

Suppose we want to search for user documents based on user tags. To do this, we will create an index on the tags array in the collection.

When creating an index on an array, we create a separate index item for each field of the array. Therefore, in our example, when we create an index on the tags array, we will create separate indexes for the values music, cricket, and blogs.

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

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

After creating an index, we can search on the tags field of the collection, as follows-

>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 whether the correct index is being used, please use the following explain command-

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

This gives you the following results-

{
	"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 produced "cursor": "BtreeCursor tags_1"

Index sub-document fields

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

To create an index on all 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
}
>

After creating an index, we can use this index to search any sub-document field, as shown below:

>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, the query expression must follow the order of the specified index. Therefore, the index created above will 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"
}
>