I have a collection where each document has an array (called elements) in it. Each element within this array represents an object with a name and age value. I have some documents where in the array, I will have duplicate objects, or at least objects with some similar values (i.e. the name is the same).

My collection looks like this (notice how the 2nd document has 2 instances of Bob):

[
  {
    "_id": {
      "$oid": "6395471f80495752e7208c63"
    },
    "elements": [
      {
        "name": "Alice",
        "age": 20
      },
      {
        "name": "Bob",
        "age": 21
      },
      {
        "name": "Charlie",
        "age": 23
      }
    ]
  },
  {
    "_id": {
      "$oid": "6395486980495752e7208c67"
    },
    "elements": [
      {
        "name": "Alice",
        "age": 20
      },
      {
        "name": "Bob",
        "age": 21
      },
      {
        "name": "Bob",
        "age": 24
      }
    ]
  }
]

I want to be able to build a query with $elemMatch so that if I want to, I can find a document which has multiple instances of the same $elemMatch element, i.e. I want to be able to find a document which has an elements array with 2 Bob's in it.

I have tried doing a query like the one below, but with no success.

db.collection.find({
  $and: [
    {
      elements: {
        $elemMatch: {
          name: "Bob"
        }
      }
    },
    {
      elements: {
        $elemMatch: {
          name: "Bob"
        }
      }
    }
  ]
})

The intended result of this query would be as follows:

[
  {
    "_id": ObjectId("6395486980495752e7208c67"),
    "elements": [
      {
        "age": 20,
        "name": "Alice"
      },
      {
        "age": 21,
        "name": "Bob"
      },
      {
        "age": 24,
        "name": "Bob"
      }
    ]
  }
]

Here is a MongoPlayground link which may make the problem easier to view.

CodePudding user response：

Your current attempt does not instruct the database to find two different array elements which match the (same) condition. The second array entry in the first sample document is allowed to satisfy both of the (duplicated) conditions that are $anded together, hence it matching and being returned.

To instruct the database to do that additional checking, we'll need to use something like the $reduce operator. Typically these were available in the aggregation framework, but we can pull them in here via $expr. That component of the query might look like this:

  $expr: {
    $gte: [
      {
        $reduce: {
          input: "$elements",
          initialValue: 0,
          in: {
            $sum: [
              "$$value",
              {
                $cond: [
                  {
                    $eq: [
                      "$$this.name",
                      "Bob"
                    ]
                  },
                  1,
                  0
                ]
              }
            ]
          }
        }
      },
      2
    ]
  }

Playground example here.

A different approach would be to use $size after processing the elements array via $filter. Something like this:

  $expr: {
    $gte: [
      {
        $size: {
          $filter: {
            input: "$elements",
            cond: {
              $eq: [
                "$$this.name",
                "Bob"
              ]
            }
          }
        }
      },
      2
    ]
  }

Example playground here.

In either case you will want to retain the original filter so that the database can more efficiently identify candidate documents prior to doing the more expensive processing to identify the final results.

As an aside, $elemMatch isn't strictly necessary here. Assuming elements is always an array and that you're only querying a single condition, the dot notation equivalent ('elements.name': 'Bob') would yield the same as the $elemMatch version.