CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Stefan Konno
Stefan Konno

Reputation: 1337

Compare embedded document to parent field with mongoDB

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.

Upvotes: 5

Views: 1878

Answers (2)

oae
oae

Reputation: 1652

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

Upvotes: 1

Neil Lunn
Neil Lunn

Reputation: 151112

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.

Upvotes: 4

Related Questions