英文:
MongoDB Aggregation - Conditional $lookup depending if a field exists or not
问题
我有两个名为collection1
和collection2
的集合,它们的结构如下所示,
// collection1
[
{ "_id": 100, "text": "i am good", "history":[{"date":"2023-6-1", "updated":"2023-6-2"}] },
{ "_id": 101, "text": "i am naughty"}
]
// collection2
[
{ "_id": 1,collection1Id:100, "date":"2023-6-1", "updated":"2023-6-2" }, // is present in 1 so should not goto this collection
{ "_id": 2,collection1Id:101, "date":"2023-7-3", "updated":"2023-7-5" }
]
我希望聚合查询运行如下,如果字段(history
)在collection1
中不存在,则应进行查找collection2
。请参考下面的代码:
db.collection1.aggregate([
如果在collection1中不存在history
{
从collection2查找
}
])
期望的输出
执行聚合后,我希望结果如下所示:
[
{ "_id": 100, "text": "i am good", "history":[{"date":"2023-6-1", "updated":"2023-6-2"}] },
{ "_id": 2,"text": "i am naughty", "history":[{"_id": 2, "collection1Id": 101, "date":"2023-7-3", "updated":"2023-7-5"}] } // history has been taken from collection 2
]
第一行仅来自collection1
,因为history存在,而第二条记录来自collection2
,因为在主要集合中不存在history
字段。
英文:
I have two collections called collection1
, and collection2
and they are structured as follows,
// collection1
[
{ "_id": 100, "text": "i am good", "history":[{"date":"2023-6-1", "updated":"2023-6-2"}] },
{ "_id": 101, "text": "i am naughty"}
]
// collection2
[
{ "_id": 1,collection1Id:100, "date":"2023-6-1", "updated":"2023-6-2" }, // is present in 1 so should not goto this collection
{ "_id": 2,collection1Id:101, "date":"2023-7-3", "updated":"2023-7-5" }
]
I want the aggregation query to run such that if the field (history
) does not exist inside collection1
then lookup to collection2
should be made. See the code below:
db.collection1.aggreagte([
if history is not exist in collection1
{
lookup from collection2
}
])
Expected Output
After the aggregation executes, I want the result to be shown below:
[
{ "_id": 100, "text": "i am good", "history":[{"date":"2023-6-1", "updated":"2023-6-2"}] },
{ "_id": 2,"text": "i am naughty", "history":[{"_id": 2, collection1Id: 101, "date":"2023-7-3", "updated":"2023-7-5"}], } // history has been taken from collection 2
]
The first row is from collection1
only because history exists and the second record is from collection2
where I did lookup as the history
field did not exist in the primary collection.
答案1
得分: 3
我不认为可以在条件下进行 $lookup
连接另一个集合。
但是,您可以在 history
字段不存在时创建它,然后将其赋值为 collection2
的值。否则,保持其现有值。
db.collection1.aggregate([
{
"$lookup": {
"from": "collection2",
"localField": "_id",
"foreignField": "collection1Id",
"as": "collection2"
}
},
{
$set: {
history: {
"$cond": {
"if": {
$eq: [
{
$type: "$history"
},
"missing"
]
},
"then": "$collection2",
"else": "$history"
}
}
}
},
{
$unset: "collection2"
}
])
英文:
I don't think that is possible to conditional $lookup
join another collection.
But, you can achieve create the history
field if the history
field is missing then assign it with the collection2
value. Otherwise, remain its existing value.
db.collection1.aggregate([
{
"$lookup": {
"from": "collection2",
"localField": "_id",
"foreignField": "collection1Id",
"as": "collection2"
}
},
{
$set: {
history: {
"$cond": {
"if": {
$eq: [
{
$type: "$history"
},
"missing"
]
},
"then": "$collection2",
"else": "$history"
}
}
}
},
{
$unset: "collection2"
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论