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-31 10:08:55Z

4

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.

edited Jul 31, 2017 at 10:08

answered Jul 31, 2017 at 9:56

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stefan Konno Over a year ago

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?

Neil Lunn Over a year ago

@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.

Stefan Konno Over a year ago

Thanks again, I'll give it a try. I appreciate your effort, have a nice day!

oae · Accepted Answer · 2020-07-23 19:30:08Z

1

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

answered Jul 23, 2020 at 19:30

oae

1,6521 gold badge18 silver badges26 bronze badges

Collectives™ on Stack Overflow

Compare embedded document to parent field with mongoDB

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related