Creating indexes (Multikey index) and complound indexes on array in MongoDB - And its Limitations

You are here : Home / MongoDB Tutorial



Contents of page >
  • 1) Creating indexes on array in MongoDB >
  • 2) Limitations on index (Multikey index) on array in MongoDB >
  • 2.1) We cannot create compound index (compound Multikey index) on array
  • 2.2) But, you CAN create compound index (compound Multikey index) when we are indexing on non-array and array field >
  • 3) Let's create compound index (compound Multikey index) on fields of array with Embedded Documents in MongoDB >

1) Creating indexes (Multikey index) on array in MongoDB >

Now, let's create index (Multikey index)  on array.

1.1) First, create and insert in STUDENT collection >
db.STUDENT.insert({
 _id: 1,
 FIRST_NAME: "Ankit",
 PHONE: [    1234, 2345   ]
})


1.2) Now, create a index (Multikey index) on array >
To create a index (Multikey index) on array, use the db.collection.createIndex() method:

1.3) Whenever we create index on array field MongoDB automatically creates a multikey index. We need not to explicitly specify the multikey type.
db.STUDENT.createIndex( { PHONE : 1 } )

1.4) Let’s understand above line >
Above, we created index of array field PHONE in ascending order. By using -1 we can create index in descending order.
The index will be created which following contains two index keys, each of them points to the same document.
  • 1234,
  • 2345
As it contains two index keys - That’s why it’s called Multikey index on arrays.

1.6) Query 1 : Let’s find the document with array field in document using index (Multikey index) >
db.STUDENT.find( { PHONE : 1234   } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ 1234, 2345 ] }


1.7) Query 2 : Let’s find the document with array field in document using index (Multikey index) >
db.STUDENT.find( { PHONE : [ 1234, 2345  ] } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ 1234, 2345 ] }

MongoDB can use the index (multikey index) to find documents that have 1234 at any position in the PHONE array. Then, MongoDB retrieves these documents and filters for documents whose PHONE array equals the query array [ 1234, 2345 ].




2) Limitations on index (Multikey index) on array  in MongoDB>
2.1) We cannot create compound index (compound Multikey index) on array
2.1.1) Let's say collection is like this >
db.STUDENT.insert({
 _id: 1,
 FIRST_NAME: "Ankit",
 PHONE: [    1234, 2345   ],
 X: [    1, 2   ]
})

2.1.2) Now, we cannot create compound index (compound Multikey index) on array
Example- We cannot create below index because both PHONE and X are array fields.
db.STUDENT.createIndex( { PHONE: 1 , X : 1 } )

Above, line is invalid and any attempt to execute above will throw error  "exception: cannot index parallel arrays [X] [PHONE]"


2.2) But, you CAN create compound index (compound Multikey index) when we are indexing on non-array and array field >

2.2.1) create and insert in STUDENT collection >
db.STUDENT.insert({
 _id: 1,
 FIRST_NAME: "Ankit",
 PHONE: [    1234, 2345   ]
})


2.2.2) Now, let's create compound index (compound Multikey index) when we are indexing on non-array field and array field.

Example- We can create below index because
PHONE is array but
FIRST_NAME is non-array field.

db.STUDENT.createIndex( { FIRST_NAME : 1, PHONE : 1 } )


2.2.3) Query 1 : Let’s find the document with compound index (compound Multikey index) from non-array and array field >
db.STUDENT.find( { PHONE : 1234   } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ 1234, 2345 ] }



2.2.4) Query 2 : Let’s find the document with compound index (compound Multikey index) from non-array and array field >
db.STUDENT.find( { FIRST_NAME : "Ankit", PHONE : 1234 } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ 1234, 2345 ] }




3) Let's create compound index (compound Multikey index) on fields of array with Embedded Documents >

3.1) create and insert in STUDENT collection >
db.STUDENT.insert({
 "_id": 1,
 "FIRST_NAME": "Ankit",
 "PHONE": [
   {"PHONE_NUMBER": 1234, "PHONE_ID": 11 },
   {"PHONE_NUMBER": 2345, "PHONE_ID": 12, }
 ]
})
db.STUDENT.insert({
 "_id": 2,
 "FIRST_NAME": "Ankit",
 "PHONE": [
   {"PHONE_NUMBER": 3456, "PHONE_ID": 13 },
   {"PHONE_NUMBER": 4567, "PHONE_ID": 14, }
 ]
})

3.2) Now, let's create compound index (compound Multikey index) on fields of array with Embedded Documents >
db.STUDENT.createIndex( { "PHONE.PHONE_NUMBER" : 1, "PHONE.PHONE_ID" : 1  } )

3.3) Query 1 : Let’s find the document using compound index ( compound Multikey index) on fields of array with Embedded Documents>
db.STUDENT.find( { "PHONE.PHONE_NUMBER" : 1234, "PHONE.PHONE_ID" : 12   } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ { "PHONE_NUMBER" : 1234, "PHONE_ID" : 11 }, { "PHONE_NUMBER" : 2345, "PHONE_ID" : 12 } ] }

3.4) Query 2 : Let’s find the document using compound index ( compound Multikey index) on fields of array with Embedded Documents>
db.STUDENT.find( { "PHONE.PHONE_NUMBER" : 1234  } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ { "PHONE_NUMBER" : 1234, "PHONE_ID" : 11 }, { "PHONE_NUMBER" : 2345, "PHONE_ID" : 12 } ] }




Having any doubt? or you liked the tutorial! Please comment in below section.
Please express your love by liking JavaMadeSoEasy.com (JMSE) on facebook, following on google+ or Twitter. You may join our fbGroup or linkedInGroup as well.



RELATED LINKS>

What is MongoDB - A quick introduction to database


Must read for you :