I try to find documents where property rate in object is bigger than property rate in other object (both are in the same array)

Example document:

{
  "rates": [
    {
      "name": "x",
      "rate": 5
    },
    {
      "name": "y",
      "rate": 4
    }
  ]
}

This is how i tried:

db.ratesCollection.find({  
  $where: 
    {"rates": { $elemMatch: {name: "x", "rate"}}} > 
    {"rates": { $elemMatch: {name: "y", "rate"}}} 
}).pretty()

The problem is that I don't know how to compare values when firstly I need to find object in array.

CodePudding user response：

You can use $reduce to process the array and fetch x_rate and y_rate first. After that compare the 2 values to get the result.

db.collection.aggregate([
  {
    "$addFields": {
      "x_rate": {
        "$reduce": {
          "input": "$rates",
          "initialValue": null,
          "in": {
            $cond: [
              {
                $eq: [
                  "$$this.name",
                  "x"
                ]
              },
              "$$this.rate",
              "$$value"
            ]
          }
        }
      },
      "y_rate": {
        "$reduce": {
          "input": "$rates",
          "initialValue": null,
          "in": {
            $cond: [
              {
                $eq: [
                  "$$this.name",
                  "y"
                ]
              },
              "$$this.rate",
              "$$value"
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $gt: [
          "$x_rate",
          "$y_rate"
        ]
      }
    }
  },
  {
    "$project": {
      x_rate: false,
      y_rate: false
    }
  }
])

Here is the Mongo playground for your reference.

CodePudding user response：

Query

• x>y <=> x-y > 0
• reduce to find the x-y and check if its bigger than 0
• initial value=0 if we find x we add if we find y subtract, else keep value as it is

Test code here

aggregate(
[{"$match":
  {"$expr":
   {"$gt":
    [{"$reduce":
      {"input":"$rates",
       "initialValue":0,
       "in":
       {"$switch":
        {"branches":
         [{"case":{"$eq":["$$this.name", "x"]},
           "then":{"$add":["$$value", "$$this.rate"]}},
          {"case":{"$eq":["$$this.name", "y"]},
           "then":{"$subtract":["$$value", "$$this.rate"]}}],
         "default":"$$value"}}}},
     0]}}}])