Compare embedded document to parent field with mongoDB

Question

Consider the following collection, where the parent document has a amount field with the value 100000 and there's an embedded array of documents with the same field amount and the same value.

{
  "_id" : ObjectId("5975ce5f05563b6303924914"),
  "amount" : 100000,
  "offers" : [ 
    {
      "amount": 100000
    }
  ]
}

Is there any way to match all objects that has at least one embedded document offer with the same amount as the parent?

If I for example query this, it works just fine:

find({ offers: { $elemMatch: { loan_amount: 100000 } } })

But I don't know the actual value 100000 in the real query I'm trying to assemble, I would need to use a variable for the parent documents amount field. Something like this.

find({ offers: { $elemMatch: { loan_amount: "parent.loan_amount" } } })

Thankful for any suggestions. I was hoping to do this with $eq or $elemMatch, and to avoid aggregates, but maybe it's not possible.

Neil Lunn · Accepted Answer · 2017-07-31T10:08:55.107

Standard queries cannot "compare" values in documents. This is actually something you do using .aggregate() and $redact:

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$gt": [
          { "$size": {
            "$filter": {
              "input": "$offers",
              "as": "o",
              "cond": { "$eq": [ "$$o.amount", "$amount" ] }
            }
          }},
          0
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

Here we use $filter to compare the values of "amount" in the parent document to those within the array. If at least one is "equal" then we "$$KEEP" the document, otherwise we "$$PRUNE"

In most recent versions, we can shorten that using $indexOfArray.

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$ne": [
          { "$indexOfArray": [ "$offers.amount", "$amount" ] },
          -1
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

If you actually only wanted the "matching array element(s)" as well, then you would add a $filter in projection:

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$gt": [
          { "$size": {
            "$filter": {
              "input": "$offers",
              "as": "o",
              "cond": { "$eq": [ "$$o.amount", "$amount" ] }
            }
          }},
          0
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }},
  { "$project": {
    "amount": 1,
    "offers": {
      "$filter": {
        "input": "$offers",
        "as": "o",
        "cond": { "$eq": [ "$$o.amount", "$amount" ] }
      }
    }
  }}
])

But the main principle is of course to "reduce" the number of documents returned to only those that actually match the condition as a "first" priority. Otherwise you are just doing unnecessary calculations and work that is taking time and resources, for results that you later would discard.

So "filter" first, and "reshape" second as a priority.

Thanks so much, would it have been easier if the offers document had their own collection with a reference to the other document. This aggregation seems like a lot of work, I mean performancewise, is it better to put the offer document in it's own collection and reference the other documents id? — Stefan Konno, Jul 31 '17 at 10:29
@StefanKonno "Performance wise" putting things in another collection is **worse**. Joins cost, and dearly. This is why you "should" be using MongoDB in the first place. There is no "easy" way to compare one field to another. Any method has a cost, and this is true of relational databases as well. — Neil Lunn, Jul 31 '17 at 10:33
Thanks again, I'll give it a try. I appreciate your effort, have a nice day! — Stefan Konno, Jul 31 '17 at 10:55

score 1 · Answer 2 · answered Jul 23 '20 at 19:30

I think since MongoDB version 3.6 you can actually do this with a simple filter using the expr operator.

Something along those lines:

find({
  $expr: {
    $in: [
      "$amount",
      "$offers.amount"
    ]
  }
})

See a live example on mongoplayground.net

Compare embedded document to parent field with mongoDB

2 Answers2

Linked