英文:
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" ] }
}
}
}
}
}
}
])
编辑 - 更好的解决方案
一个更好的解决方案在这里,感谢turivishal
引用:
$match
不能直接检查内部字段的条件,你需要使用$expr
运算符,$not
和$in
运算符来匹配你的条件。
db.collection.aggregate([
{
$match: {
$expr: {
$not: {
$in: ["$_id", "$applications.firstTimeInstalled.refId"]
}
}
}
}
])
英文:
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" ] }
}
}
}
}
}
}
])
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"]
}
}
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论