如何在MongoDB中匹配同一文档中两个字段的比较?

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

How can I match on the comparison of two fields in the same document in MongoDB?

问题

我在Mongo聚合中的一个阶段,有这样的文档:

{
  "_id": ObjectId('648031bd784fbf6081de41cf'),
  "orgId": 1,
  "applications": {
    "_id": ObjectId('6479ddda073ced427d04e9dd'),
    "orgId": 1,
    "firstTimeInstalled": [
      {
        "refId": ObjectId('648031bd784fbf6081de41cf'),
        "installDate": "2023-06-08T09:18:49.233+00:00"
      },
      {
        "refId": ObjectId('6479ddda073ced427d04e9dd'),
        "installDate": "2023-06-08T09:18:49.233+00:00"
      }
    ]
  }
}

我想要匹配只有在applications.firstTimeInstalled不包含一个refId等于文档根部的_id的对象的文档。

我尝试过以下操作:

{
  $match: {
    "applications.firstTimeInstalled.refId": {
      $ne: "$_id"
    }
  }
}

但我仍然得到不符合条件的文档,似乎不起作用。

更让我困惑的是,即使我这样做:

'applications.firstTimeInstalled.refId': {
  $nin: [ObjectId('647746cfd9baa823f877c08f')]
}

正如你所看到的,我手动注入了_id,但这仍然返回了文档...这怎么可能?

英文:

I am at a stage in my Mongo aggregation where I have documents like this:

{
  "_id": ObjectId('648031bd784fbf6081de41cf'),
  "orgId": 1,
  "applications": {
    "_id": ObjectId('6479ddda073ced427d04e9dd'),
    "orgId": 1,
    "firstTimeInstalled": [
      {
        "refId": ObjectId('648031bd784fbf6081de41cf'),
        "installDate": "2023-06-08T09:18:49.233+00:00"
      },
      {
        "refId": ObjectId('6479ddda073ced427d04e9dd'),
        "installDate": "2023-06-08T09:18:49.233+00:00"
      }
    ]
  }
}

I want to only match on documents where applications.firstTimeInstalled does not contain an object where refId is equal to the _id in the root of the document.

I've tried to do this:

{
  $match: {
    "applications.firstTimeInstalled.refId": {
      $ne: "$_id"
    }
  }
}

But I still get documents back that doesn't meet the criteria so doesn't seem to work.

The thing that confuses me even more is that even if I do this:

{
  'applications.firstTimeInstalled.refId': {
    $nin: [ ObjectId('647746cfd9baa823f877c08f') ]
  }
}

As you can see I inject the _id manually, but this still returns the document... How is that possible?

答案1

得分: 0

可能的解决方案是通过 $map 遍历 firstTimeInstalled 数组,如果映射后的数组具有 $anyElementTrue,则表示存在一个具有 _id === refId 的元素。 因此,使用 $not 进行否定。

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $not: {
          $anyElementTrue: {
            $map: { 
              input: "$applications.firstTimeInstalled",
              in: { $eq: [ "$$this.refId", "$_id" ] }
            }
          }
        }
      }
    }
  }
])

playground

编辑 - 更好的解决方案
一个更好的解决方案在这里,感谢turivishal

引用:

$match 不能直接检查内部字段的条件,你需要使用 $expr 运算符,$not$in 运算符来匹配你的条件。

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $not: {
          $in: ["$_id", "$applications.firstTimeInstalled.refId"]
        }
      }
    }
  }
])

playground

英文:

a possible solution is to $map through the firstTimeInstalled array and if the mapped array has $anyElementTrue it means there is an element with _id === refId.
So take the negation with $not

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $not: {
          $anyElementTrue: {
            $map: { 
              input: "$applications.firstTimeInstalled",
              in: { $eq: [ "$$this.refId", "$_id" ] }
            }
          }
        }
      }
    }
  }
])

playground

EDIT - better solution
a better solution here thanks to turivishal
quoting

> The $match can’t allow checking the internal fields condition directly, you need to use $expr operator, $not and $in operator to match your condition,

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $not: {
          $in: ["$_id", "$applications.firstTimeInstalled.refId"]
        }
      }
    }
  }
])

playground

huangapple
  • 本文由 发表于 2023年6月8日 18:06:53
  • 转载请务必保留本文链接:https://go.coder-hub.com/76430757.html
匿名

发表评论

匿名网友

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

确定