I struggle writing Mongo Queries. I can never understand why it doesn't return what I expect and yes I am reading the documentation but apparently to dumb to understand.

In Compass I write this query for a State collection with a document array of cities.

{"Cities.CityName":"Denver"}

This returns to me the State of Colorado document with ALL the cities in the document array. My sample below just shows one city document but they are all there.

{
"_id": {"$oid":"6146ada531696ee91a3f9fa4"},
"StateName": "Colorado",
"StateCode": "CO",
"Cities": [{
    "_id": {"$oid":"6146ada531696ee91a3f5a50"},
    "CityName": "Denver",
    "Latitude": "39.55666000",
    "Longitude": "-104.89609000"
    }...]
}

OK so I'm thinking clearly we matched on the CityName now just project the _id of the City document.

{"Cities._id":1}

But this always returns to me the State document id NOT the matched City Document _id.

What am I doing wrong?

CodePudding user response：

1) You can use $ (projection) or $elemMatch (projection):

• use case: The positional $ operator limits the contents of an <array> to return the first element that matches the query condition on the array.

• ex: { "Cities.$": 1 } or ​{"Cities": { "$elemMatch": { "CityName": "Denver" } } }

• result:

{
 ​"_id":{"$oid":"6146ada531696ee91a3f9fa4"},
 ​"Cities":[{
   ​"_id":{"$oid":"6146ada531696ee91a3f5a50"},
   ​"CityName":"Denver",
   ​"Latitude":"39.55666000",
   ​"Longitude":"-104.89609000"
 ​}]
}

Playground

Note: You have to specify the required result fields in the projection!

2) You can use $filter aggregation operator, supported from MongoDB 4.4 in find() method:

• use case: Selects a subset of an array to return based on the specified condition. Returns an array with only those elements that match the condition. The returned elements are in the original order.

• ex:

{ 
  "Cities": { 
    "$filter": { 
      "input": "$Cities", 
      "cond": { "$eq": ["$$this.CityName", "Denver"] } 
    } 
  } 
}

• result:

{
 ​"_id":{"$oid":"6146ada531696ee91a3f9fa4"},
 ​"Cities":[{
   ​"_id":{"$oid":"6146ada531696ee91a3f5a50"},
   ​"CityName":"Denver",
   ​"Latitude":"39.55666000",
   ​"Longitude":"-104.89609000"
 ​}...{}]
}

Playground

Note: You have to specify the required result fields in the projection!

2.1) You can use $map aggregation operator to select only _ids from the Cities array:

• use case: Applies an expression to each item in an array and returns an array with the applied results.

• ex:

{ 
  "Cities": { 
    "$map": {
      "input": {
        "$filter": { 
          "input": "$Cities", 
          "cond": { "$eq": ["$$this.CityName", "Denver"] } 
        }
      },
      "in": "$$this._id"
    }
  } 
}

• result:

{
  "_id":{"$oid":"6146ada531696ee91a3f9fa4"},
  "Cities":[
    {"$oid":"6146ada531696ee91a3f5a50"},
    {"$oid":"6146ada531696ee91a3f5a51"},
    ....
  ]
}

Playground

Note: You have to specify the required result fields in the projection!

3) You can use aggregation method aggregate() for more customization:

- ex:

• $match to check query condition
• $addFields to add or format the existing properties
• $filter and $map i have explained in 2) point

db.collection.aggregate([
  { "$match": { "Cities.CityName": "Denver" } },
  {
    "$addFields": {
      "Cities": {
        "$map": {
          "input": {
            "$filter": {
              "input": "$Cities",
              "cond": { "$eq": ["$$this.CityName", "Denver"] }
            }
          },
          "in": "$$this._id"
        }
      }
    }
  }
])

Playground

• result:

[
  {
    "Cities": [
      ObjectId("6146ada531696ee91a3f5a50")
    ],
    "StateCode": "CO",
    "StateName": "Colorado",
    "_id": ObjectId("6146ada531696ee91a3f9fa4")
  }
]