CODEWITHSUNDEEP
mongodbmongodb-query
user641887
user641887

Reputation: 1576

Multikey Indexes with Bounds mongodb

I am trying to figure out multikey index's with bounds with an example I have.

s1:PRIMARY> db.inventory.find();
{ "_id" : 2, "item" : "ABCx", "ratings" : [ 4, 3 ] }
{ "_id" : 3, "item" : "ABC", "ratings" : [ 2, 9 ] }
{ "_id" : 4, "item" : "XYZ", "ratings" : [ 4, 3 ] }

I do a find like this 

s1:PRIMARY> db.inventory.find( { ratings: { $gte: 5 ,$lte:8} } );

output :

{ "_id" : 3, "item" : "ABC", "ratings" : [ 2, 9 ] }

As per the documentation here and I quote (quote contains a different example than mine)

The query searches the ratings array for at least one element greater than or equal to 3 and at least one element less than or equal to 6. Because a single element does not need to meet both criteria, MongoDB does not intersect the bounds and uses either [ [ 3, Infinity ] ] or [ [ -Infinity, 6 ] ]. MongoDB makes no guarantee as to which of these two bounds it chooses.

https://docs.mongodb.com/manual/core/multikey-index-bounds/

based on this documentation, i should have all 3 rows as my output instead of just one row.

Can someone clarify how does this work ?

Upvotes: 0

Views: 108

Answers (1)

Nick Bull
Nick Bull

Reputation: 9866

Both $gte and $lte must match at least one element within the document's result array in the .find() method. So, for $gte: 5, $lte: 8:

  • _id: 2 has [ 4, 3 ] => $lte: 8 matches either element, but $gte: 5 cannot match either 3 or 4, so not returned;
  • _id: 3 has [ 2, 9 ] => $lte: 8 matches 2 and $gte: 5 matches 9, so it is returned;
  • _id: 4 has [ 4, 3 ] => same as _id: 2.

Therefore, it makes sense that only document with _id: 3 is returned. Add an element that is $gte: 5 to _id: 2 or _id: 4 document's result array, and it'll match that document!

Upvotes: 1

Related Questions