In the sample_training database, companies collection, there is data like the following one:

Exercise: How many companies in the sample_training.companies collection have offices in the city of "Seattle"?

The query I thought of was with the dot notation as follows:

db.companies.find({ "offices.0.city": "Seattle" }).count()

This returns 110.

However, the site gives the following query as the correct one

db.companies.find({ "offices": { "$elemMatch": { "city": "Seattle" } } }).count()

This returns 117.

I have checked that my query seems to work fine as well, but I don't know why they differ in their result.

CodePudding user response：

The difference is you are only looking at the first element (index 0) in the array. You are specifying the index to look at.

Meaning, if we have the following database:

[
  {
    "offices": [
      {
        "city": "Dallas"
      },
      {
        "city": "Jacksonville"
      }
    ]
  }
]

With the following query:

db.collection.find({
  "offices.0.city": "Jacksonville"
})

It would return nothing.

Whereas, if we used this query, the element does not have to be the first index.

db.collection.find({
  "offices": {
    "$elemMatch": {
      "city": "Jacksonville"
    }
  }
})

Live Demos:

Working - https://mongoplayground.net/p/wnX-arcooa7

Not Working - https://mongoplayground.net/p/zFWV00TzZjj

CodePudding user response：

I went to the wine db - https://www.pdbmbook.com/playground/mongo/wine/view/pgdb____1635519319_617c0b57588c7

And I did:

db.products.find( { "type": "rose"}).count(); 
Result = 3 
db.products.find({ "products.0.type": "rose" }).count();
Result: 0
db.products.find({ "products": { "$elemMatch": { "type": "rose" } } }).count()
Result: 0

I suspect I get back 0 since the online playground I used is limited in functionality. Nevertheless I would assume any field that references the index of the object e.g. "offices.0.city" would mean you are starting the count higher up the tree or at 0.