Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongodb query with fields in the same documents

Tags:

mongodb

I have the following json:

{
  "a1": {"a": "b"},
  "a2": {"a": "c"}
}

How can I request all documents where a1 and a2 are not equal in the same document?

like image 738
Rusty Robot Avatar asked Dec 08 '11 15:12

Rusty Robot


People also ask

How do I capture a specific field in MongoDB?

You can select a single field in MongoDB using the following syntax: db. yourCollectionName. find({"yourFieldName":yourValue},{"yourSingleFieldName":1,_id:0});

What is __ V in MongoDB?

The __v field is called the version key. It describes the internal revision of a document. This __v field is used to track the revisions of a document. By default, its value is zero ( __v:0 ).

Does MongoDB query support join between collections?

Much like you would use a join to combine information from different tables in a relational database, MongoDB has a $lookup operation that allows you to join information from more than one collection.


4 Answers

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.

like image 124
mnemosyn Avatar answered Nov 09 '22 14:11

mnemosyn


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}} 
  ]);
like image 37
Mohammed Essehemy Avatar answered Nov 09 '22 14:11

Mohammed Essehemy


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.

like image 32
Paul Avatar answered Nov 09 '22 12:11

Paul


MongoDB uses Javascript in the background, so

{"a": "b"} == {"a": "b"}

would be false.

So to compare each you would have to a1.a == a2.a

To do this in MongoDB you would use the $where operator

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

This assumes that each embedded document will have a property "a". If that isn't the case things get more complicated.

like image 6
Cormac Mulhall Avatar answered Nov 09 '22 13:11

Cormac Mulhall