Compare embedded document to parent field with mongoDB

user_250553 0
Viewed 1518

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.

Thanks!

aggregation-framework mongodb-query mongodb
2 Answers

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

user_6720080
Related