CODEWITHSUNDEEP
mongodbdatabase-indexesmongodb-indexes
3bst0r
3bst0r

Reputation: 35

MongoDB deep array scan: multikey compound indexing

I have a collection of customers with their visited places, organised as follows:

{
  "customer_id": 151,
  "first_name": "Nakia",
  "last_name": "Boyle",
  "visited_places": [
    {
      "country": "Portugal",
      "cities": [
        "South Nicklausburgh",
        "East Graham"
      ]
    },
    {
      "country": "Rwanda",
      "cities": [
        "West Kristofer",
        "Effertzbury",
        "Stokeston",
        "South Darionfort",
        "Lewisport"
      ]
    }
  ]
}

I am trying to find all customers that have visited a specific city in a specific country. The query object is:

{
  "visited_places.country" : "Portugal", 
  "visited_places.cities" : "South Nicklausburgh" 
}

What would the ideal index for this query be? I tried to create a compound index like so:

collection.createIndex({
  'visited_places.cities': 1,
  'visited_places.country': 1
}

This index is indeed used, but only for finding the cities, as the execution plan explains in the IXSCAN stage:

"indexBounds": {
  "visited_places.cities": [
   "[\"South Nicklausburgh\", \"South Nicklausburgh\"]"
  ],
  "visited_places.country": [
   "[MinKey, MaxKey]"
  ]

The country is filtered out in the subsequent FETCH stage:

"filter": {
  "visited_places.country": {
   "$eq": "Portugal"
  }
 }

Why can't the query be completed only from the compound index, and what would the ideal index be for this schema and query?

Upvotes: 0

Views: 119

Answers (1)

mohammad Naimi
mohammad Naimi

Reputation: 2359

use $elemMatch like this

db.collection.find({
  "visited_places": {
    "$elemMatch": {
      "country": "Portugal",
      "cities": {
        "$elemMatch": {
          "$eq": "South Nicklausburgh"
        }
      }
    }
  }
},
)

https://mongoplayground.net/p/CKnz8VCT5rX

Upvotes: 1

Related Questions