匹配MongoDB文档的数组元素,根据元素值中定义的条件。

huangapple go评论53阅读模式
英文:

MongoDB match document's array element based on condition defined in the element's value

问题

我在我的数据库中有一个 MongoDB 文档(v6.0.3):

{
  "user": "123",
  "alerts": [
    {
      "alertDirection": "$gte",
      "alertValue": 9999,
      "location": "home",
      "available": false,
      "position": 28
    },
    {
      "alertDirection": "$lte",
      "alertValue": 50,
      "location": "home",
      "available": true,
      "position": 14
    },
    {
      "alertDirection": "$gte",
      "alertValue": 100,
      "location": "home",
      "available": true,
      "position": 71
    },
    {
      "alertDirection": "$gte",
      "alertValue": 100,
      "location": "out",
      "available": true,
      "position": 43
    }
  ]
}

我根据一些条件匹配需要的数组元素:

{
  "$match": {
    "alerts": {
      "$elemMatch": {
        "$and": [
          { "location": "home" },
          { "available": true }
        ]
      }
    }
  }
}

是否可能扩展我的 $match 以仅获取匹配 alertValue 基于 alertDirection 值和我拥有的变量的文档?

例如,我有一个值为 10 的变量 myValue。我想仅匹配数组中 myValue 大于或小于 alertValue 的元素,这取决于 alertDirection。在我的示例中,唯一的匹配将是以下元素(因为 10小于或等于 50):

{
  "alertDirection": "$lte",
  "alertValue": 50,
  "location": "home",
  "available": true,
  "position": 14
}

或者如果 myValue100,则只有以下元素将被匹配(因为 100大于或等于 100):

{
  "alertDirection": "$gte",
  "alertValue": 100,
  "location": "home",
  "available": true,
  "position": 71
}
英文:

I have a MongoDB (v6.0.3) document in my database:

{
  "user": "123",
  "alerts": [
    {
      "alertDirection": "$gte",
      "alertValue": 9999,
      "location": "home",
      "available": false,
      "position": 28
    },
    {
      "alertDirection": "$lte",
      "alertValue": 50,
      "location": "home",
      "available": true,
      "position": 14
    },
    {
      "alertDirection": "$gte",
      "alertValue": 100,
      "location": "home",
      "available": true,
      "position": 71
    },
    {
      "alertDirection": "$gte",
      "alertValue": 100,
      "location": "out",
      "available": true,
      "position": 43
    }
  ]
}

And I'm matching array elements I need based on few conditions:

{
  $match: {
    "alerts": {
      $elemMatch: {
        $and: [
          { "location": "home" },
          { "available": true }
        ]
      }
    }
  }
}

Is it possible to expand my $match to fetch only documents with alerts elements matching alertValue based on alertDirection value and a variable I have?

For example, I have a variable myValue with value 10. I'd like to match only elements in array where myValue is either higher or lower than alertValue, depending on alertDirection. In my example, the only match would be the following element (because 10 is lower or equal than 50):

{
  "alertDirection": "$lte",
  "alertValue": 50,
  "location": "home",
  "available": true,
  "position": 14
}

Or if myValue would be 100, only the following element would be matched (because 100 is greater or equal than 100):

{
  "alertDirection": "$gte",
  "alertValue": 100,
  "location": "home",
  "available": true,
  "position": 71
}

答案1

得分: 1

以下是翻译好的部分:

你可以尝试这个查询:

关键点在于使用 $cond,如果 "direction" 是 $gte,那么就与 $gte 进行比较,否则与 $lte 进行比较(但要小心使用 $literal 获取字符串文字。

最后一步是分组,以相同的方式将结果放入数组中,就像在数据库中一样,但这是可选的。

db.collection.aggregate([
  {
    "$unwind": "$alerts"
  },
  {
    "$match": {
      "alerts.location": "home",
      "alerts.available": true,
      "$expr": {
        "$cond": {
          "if": {
            "$eq": ["$alerts.alertDirection", {"$literal": "$gte"}]
          },
          "then": {"$gte": [yourValue, "$alerts.alertValue"]},
          "else": {"$lte": [yourValue, "$alerts.alertValue"]}
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "user": {
        "$first": "$user"
      },
      "alerts": {
        "$push": "$alerts"
      }
    }
  }
])

示例在这里

英文:

You can try this query:

The trick here is to use $cond and if the "direction" is $gte then compare with $gte otherwise $lte (but be careful to use $literal to get the string literal.

And the last step is a group to get result into an array in the same way that are into DB but is optional.

db.collection.aggregate([
  {
    "$unwind": "$alerts"
  },
  {
    "$match": {
      "alerts.location": "home",
      "alerts.available": true,
      "$expr": {
        "$cond": {
          "if": {
            "$eq": ["$alerts.alertDirection",{"$literal": "$gte"}]
          },
          "then": {"$gte": [yourValue,"$alerts.alertValue"]},
          "else": {"$lte": [yourValue,"$alerts.alertValue"]}
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "user": {
        "$first": "$user"
      },
      "alerts": {
        "$push": "$alerts"
      }
    }
  }
])

Example here

huangapple
  • 本文由 发表于 2023年1月9日 00:21:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/75049411.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定