Mongodb query with fields in the same documents

Solution 1:

You could use $where:

db.myCollection.find( { $where: "this.a1.a != this.a2.a" } )

However, be aware that this won't be very fast, because it will have to spin up the java script engine and iterate each and every document and check the condition for each.

If you need to do this query for large collections, or very often, it's best to introduce a denormalized flag, like areEqual. Still, such low-selectivity fields don't yield good index performance, because he candidate set is still large.

Solution 2:

update

using the new $expr operator available as of mongo 3.6 you can use aggregate expressions in find query like this:

  db.myCollection.find({$expr: {$ne: ["$a1.a", "$a2.a"] } });

Although this comment solves the problem, I think a better match for this use case would be to use $addFields operator available as of version 3.4 instead of $project.

db.myCollection.aggregate([
     {"$match":{"a1":{"$exists":true},"a2":{"$exists":true}}},
     {"$addFields": {
           "aEq": {"$eq":["$a1.a","$a2.a"]}
         }
     },
     {"$match":{"aEq": false}} 
  ]);

Solution 3:

To avoid JavaScript use the aggregation framework:

db.myCollection.aggregate([
  {"$match":{"a1":{"$exists":true},"a2":{"$exists":true}}},
  {"$project": {
      "a1":1,
      "a2":1,
      "aCmp": {"$cmp":["$a1.a","$a2.a"]}
    }
  },
  {"$match":{"aCmp":0}}
])

On our development server the equivalent JavaScript query takes 7x longer to complete.

Update (10 May 2017)

I just realized my answer didn't answer the question, which wanted values that are not equal (sometimes I'm really slow). This will work for that:

db.myCollection.aggregate([
  {"$match":{"a1":{"$exists":true},"a2":{"$exists":true}}},
  {"$project": {
      "a1":1,
      "a2":1,
      "aEq": {"$eq":["$a1.a","$a2.a"]}
    }
  },
  {"$match":{"aEq": false}}
])

$ne could be used in place of $eq if the match condition was changed to true but I find using $eq with false to be more intuitive.