根据字符串匹配ObjectId的MongoDB聚合

Question

我有以下文档，也可以在mongo操场上找到：https://mongoplayground.net/p/zhcoi1BF0Ny

db={
MyCollectionOne: [
{
"firstId": "10",
"secondId": "123456789012345678901234"
},
{
"firstId": "11",
"secondId": "999999999999999999999999"
}
],
MyCollectionTwo: [
{
"_id": ObjectId("123456789012345678901234"),
"otherFieldOne": "Some Data",
"otherFieldTwo": [
{
someNumber: 7
}
]
},
{
"_id": ObjectId("999999999999999999999999"),
"otherFieldOne": "Some Other Data",
"otherFieldTwo": [
{
someNumber: 9
},
{
someNumber: 39
}
]
}
]
}

给定firstId，我需要确定要返回的正确MyCollectionTwo条目。例如，如果给我一个11的firstId，我会用它来查找相应的secondId(即"99999999999999999999"(。我需要将secondId值转换为ObjectId，然后查看MyCollectionTwo_id字段，直到找到匹配的字段。

我尝试了一下，非常接近，但不知道如何正确地执行字符串->objectId转换。

db.MyCollectionTwo.aggregate([
{
$lookup: {
from: "MyCollectionOne",
localField: "_id",
foreignField: "secondId",
as: "Temp"
}
},
{
$unwind: "$Temp"
},
{
$match: {
"Temp.firstId": "11"
}
},
{
$project: {
_id: 1,
otherFieldOne: 1,
otherFieldTwo: 1
}
}
]).find()

我知道有一个let/pipeline可以使用$toObjectId，但我无法在上面的上下文中使用它。

如有任何帮助，我们将不胜感激。谢谢

Answer 1

带有管道的$lookup应如下所示：

$lookup: {
from: "MyCollectionOne",
let: {
id: "$_id"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
{
$toObjectId: "$secondId"
},
"$$id"
]
}
}
}
],
as: "Temp"
}

Mongo游乐场示例

Answer 2

你可以试试这个

db.MyCollectionTwo.aggregate([
{
$lookup: {
//searching collection name
from: "MyCollectionOne",
//setting variable [searchId] where your string converted to ObjectId
let: {
"searchId": {
$toObjectId: "$secondId"
}
},
//search query with our [searchId] value
"pipeline": [
//searching [searchId] value equals your field [_id]
{
"$match": {
"$expr": [
{
"_id": "$$searchId"
}
]
}
},

],
as: "Temp"
}
},
{
$unwind: "$Temp"
},
{
$match: {
"Temp.firstId": "11"
}
},
{
$project: {
_id: 1,
otherFieldOne: 1,
otherFieldTwo: 1
}
}
]).find()

https://mongoplayground.net/p/es0j0AiPDCj