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

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

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

问题

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

  1. {
  2.   "_id": ObjectId('648031bd784fbf6081de41cf'),
  3.   "orgId": 1,
  4.   "applications": {
  5.     "_id": ObjectId('6479ddda073ced427d04e9dd'),
  6.     "orgId": 1,
  7.     "firstTimeInstalled": [
  8.       {
  9.         "refId": ObjectId('648031bd784fbf6081de41cf'),
  10.         "installDate": "2023-06-08T09:18:49.233+00:00"
  11.       },
  12.       {
  13.         "refId": ObjectId('6479ddda073ced427d04e9dd'),
  14.         "installDate": "2023-06-08T09:18:49.233+00:00"
  15.       }
  16.     ]
  17.   }
  18. }

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

我尝试过以下操作：

  1. {
  2.   $match: {
  3.     "applications.firstTimeInstalled.refId": {
  4.       $ne: "$_id"
  5.     }
  6.   }
  7. }

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

更让我困惑的是，即使我这样做：

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

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

英文:

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

  1. {
  2.   "_id": ObjectId('648031bd784fbf6081de41cf'),
  3.   "orgId": 1,
  4.   "applications": {
  5.     "_id": ObjectId('6479ddda073ced427d04e9dd'),
  6.     "orgId": 1,
  7.     "firstTimeInstalled": [
  8.       {
  9.         "refId": ObjectId('648031bd784fbf6081de41cf'),
  10.         "installDate": "2023-06-08T09:18:49.233+00:00"
  11.       },
  12.       {
  13.         "refId": ObjectId('6479ddda073ced427d04e9dd'),
  14.         "installDate": "2023-06-08T09:18:49.233+00:00"
  15.       }
  16.     ]
  17.   }
  18. }

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:

  1. {
  2.   $match: {
  3.     "applications.firstTimeInstalled.refId": {
  4.       $ne: "$_id"
  5.     }
  6.   }
  7. }

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:

  1. {
  2.   'applications.firstTimeInstalled.refId': {
  3.     $nin: [ ObjectId('647746cfd9baa823f877c08f') ]
  4.   }
  5. }

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 进行否定。

  1. db.collection.aggregate([
  2.   {
  3.     $match: {
  4.       $expr: {
  5.         $not: {
  6.           $anyElementTrue: {
  7.             $map: { 
  8.               input: "$applications.firstTimeInstalled",
  9.               in: { $eq: [ "$$this.refId", "$_id" ] }
  10.             }
  11.           }
  12.         }
  13.       }
  14.     }
  15.   }
  16. ])

playground

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

引用：

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

  1. db.collection.aggregate([
  2.   {
  3.     $match: {
  4.       $expr: {
  5.         $not: {
  6.           $in: ["$_id", "$applications.firstTimeInstalled.refId"]
  7.         }
  8.       }
  9.     }
  10.   }
  11. ])

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

  1. db.collection.aggregate([
  2.   {
  3.     $match: {
  4.       $expr: {
  5.         $not: {
  6.           $anyElementTrue: {
  7.             $map: { 
  8.               input: "$applications.firstTimeInstalled",
  9.               in: { $eq: [ "$$this.refId", "$_id" ] }
  10.             }
  11.           }
  12.         }
  13.       }
  14.     }
  15.   }
  16. ])

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,

  1. db.collection.aggregate([
  2.   {
  3.     $match: {
  4.       $expr: {
  5.         $not: {
  6.           $in: ["$_id", "$applications.firstTimeInstalled.refId"]
  7.         }
  8.       }
  9.     }
  10.   }
  11. ])

playground

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

发表评论

匿名网友

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

确定