Mongodb query with fields in the same documents

mongodb compare two fields in same document
mongodb-query nested documents
mongodb or
mongodb query nested array
mongodb cmp
mongodb query subdocument array
mongodb not equal
spring data mongodb query nested documents

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?

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.

Query on Embedded/Nested Documents, MongoDB Manual: How to query on embedded documents/nested documents/​subdocuments/nested fields. Query/select by embedded documents/nested  MongoDB query with fields in the same document? You can use $where operator for this. To understand the concept, let us create a collection with the document. The query to create a collection with a document is as follows −

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.

eq operator, The query will also match documents where the value of the tags field is the string "B" . Equals an Array Value¶. The following example queries the inventory​  From the MongoDB docs: A projection can explicitly include several fields. In the following operation, find () method returns all documents that match the query. In the result set, only the item and qty fields and, by default, the _id field return in the matching documents.

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}} 
  ]);

Mongodb query expressions that compare fields from the same , You have nested documents in your mongodb db.And you need to query for a particular condition as well as for equality(comparison) among 2  In MongoDB, I need to use group aggregation (I believe), in order to get the number of documents in a collection with the same value. I need to get these results returned to me from greatest to least, and then get the common value for each result. Eg. I have a normal query with a range (eg. field "value" > 5).

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.

MongoDB query with fields in the same document?, You can use $where operator for this. To understand the concept, let us create a collection with the document. The query to create a collection  By default, queries in MongoDB return all fields in matching documents. To limit the amount of data that MongoDB sends to applications, you can include a projection document to specify or restrict fields to return. This page provides examples of query operations with projection using the db.collection.find () method in the mongo shell.

Thanks all for solving my problem -- concerning the answers that use aggregate(), one thing that confused me at first is that $eq (or $in, or lots of other operators) has different meaning depending on where it is used. In a find(), or the $match phase of aggregation, $eq takes a single value, and selects matching documents:

db.items.aggregate([{$match: {_id: {$eq: ObjectId("5be5feb45da16064c88e23d4")}}}])

However, in the $project phase of aggregation, $eq takes an Array of 2 expressions, and makes a new field with value true or false:

db.items.aggregate([{$project: {new_field: {$eq: ["$_id", "$foreignID"]}}}])

In passing, here's the query I used in my project to find all items whose list of linked items (due to a bug) linked to themselves:

db.items.aggregate([{$project: {idIn: {$in: ["$_id","$header.links"]}, "header.links": 1}}, {$match: {idIn: true}}])

How to Query and Filter Elements from MongoDB Arrays, Skip to: How to query array string values and embedded documents visually The smallest unit of data in MongoDB is a field-value pair, like: in arrays and to project only some array fields, and all of this in the same query,  If the documents to merge include the same field name, the field in the resulting document has the value from the last document merged for the field. ← $max (aggregation) $meta (aggregation) → © MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.

MongoDB fetch documents from collection with selective fields , we are going to discuss how to fetches data for a specific columns or except a specific column from a collection in MongoDB. Fetch documents from collection with selective fields Previous: A simple MongoDB query The MongoDB Query Language cannot always meaningfully express queries over documents whose field names contain these characters (see SERVER-30575). Until support is added in the query language, the use of $ and. in field names is not recommended and is not supported by the official MongoDB drivers.

MongoDB, MongoDB - Query Embedded Documents Using Mongo Shell · MongoDB - Field Update Operators · MongoDB - Less than Operator $lt · MongoDB and Python  query: document: Optional. Specifies selection filter using query operators. To return all documents in a collection, omit this parameter or pass an empty document ({}). projection: document: Optional. Specifies the fields to return in the documents that match the query filter. To return all fields in the matching documents, omit this parameter.

MongoDB update one or more fields of one or all documents, The document selector describes which document(s) from the collection should be updated. The same query statements as in the; MongoDB find function can be​  The db.collection.find () method returns a cursor to the matching documents. The MongoDB Compass Find operation opens a cursor to the matching documents of the collection based on the find query. For more information on sampling in MongoDB Compass, see the Compass FAQ.

Comments
  • This question is answered here: stackoverflow.com/questions/4442453/…
  • I think $addFields would be better than $project for this use case.
  • You're probably correct. $addFields wasn't introduced until v3.4 and we were still using v3.2 when I answered. If you have time to write an answer using $addFields please do so! We're still using v3.2 unfortunately.
  • I've submitted edit request with the $addFields operator.
  • Thank you for the edit but I rejected it. Your edit should be a new answer because it's a different way of answering the question.