CODEWITHSUNDEEP
mongodbmongodb-query
TomG
TomG

Reputation: 2529

MongoDB - Use $where inside $elemMatch

Let's say I have a collection with accounts that each account has array of users:

{
  _id: 1,
  users: [
    {
      firstName: a,
      lastName: b
    },
    {
      firstName: c,
      lastName: c
    }
  ]
}

I want to query all accounts where at least one user has the same firstName and lastName

I tried to do something like this:

db.accounts.find({users: {
    $elemMatch: {
        $where: "this.firstName == this.lastName"
    }
}})

But of course it didn't work because of this error:

Can't canonicalize query: BadValue $elemMatch cannot contain $where expression

I want to use the find and not aggregate. Is there some other way to do it?

Upvotes: 4

Views: 2006

Answers (3)

Blakes Seven
Blakes Seven

Reputation: 50406

Bit out of the box, but since $where relies on JavaScript evaluation, then this aggregate with $redact should actually run a lot faster since it uses native operators:

db.collection.aggregate([
    { "$redact": {
        "$cond": {
            "if": {
                "$anyElementTrue": {
                    "$map": {
                        "input": "$users",
                        "as": "user",
                        "in": { "$eq": [ "$$user.firstName", "$$user.lastName" ] }
                    }
                }
            },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }}
])

So the $anyElementTrue combined with $map basically works the same as JavaScript .some() here, in that if any of the elements actually had the same "firstName" as "lastName", then this is a true match and the document is "kept". Otherwise it is "pruned" and discarded.

So with the native operator speed, I would go with that over $where evaulation. I know you say you don't want to, but you possibly did not consider the statement was this simple or this much faster than the alternative either.

Upvotes: 2

zangw
zangw

Reputation: 48396

Please try to use $where with Array.some as below.

> db.collection.find({
             $where: function() {
                  return this.users.some(function(obj){ 
                           return obj.firstName == obj.lastName;
                 })
            }})

Upvotes: 4

Alex Blex
Alex Blex

Reputation: 37048

The documentation is quite clear about it:

You cannot specify a $where expression as a query criterion for $elemMatch.

What you can do, is iterate the array within $where:

db.collection.find(function(){
    for(var i in this.users) {
        if(this.users[i].firstName == this.users[i].lastName) {
            return true;
        }
    }
})

Upvotes: 0

Related Questions