MongoDb聚合$ lookup与数组中的外部_ids [英] MongoDb aggregation $lookup with foreign _ids in arrays
问题描述
我是MongoDb的新手.我的状态还不错，但是还没有专家.我正在尝试以一种有意义的方式设置我的收藏集.我想在指向_ids的数组以及包含_ids的对象的数组中保留指向外部文档的一些链接.
I'm a MongoDb novice. I'm getting pretty good, but no expert yet. I'm trying setup my collections in a way that makes sense. I'd like to keep some links to foreign docs inside arrays of just _ids and also arrays of objects that have _ids.
我创建了一个带有注释的JSON文档，我认为该注释可以充分显示我的工作意图...
I created a JSON doc with notes that I think fully shows what I'm trying to do...
// ( item ) Character Inventory/Items collection
[
{
"_id": "1234",
"name": "Sword",
"descr": "Long sword, well worn, light rust",
"encumber": 2,
"del": false
},
{
"_id": "1271",
"name": "Pouch",
"descr": "Small leather waist pouch, suitable for coins",
"encumber": 0,
"del": false
}
]
// ( charnpcclass ) Character Classes collection
[
{ "_id": "2", "name": "Thief", "del": false },
{ "_id": "3", "name": "Cleric", "del": false }
]
// ( charnpcalign ) Character Alignments collection
[
{ "_id": "3", "name": "Lawful Good", "del": false },
{ "_id": "4", "name": "Neutral", "del": false }
]
// ( character ) Characters collection
[
{
"_id": "3345",
"name": "Offut 'Dead Dog' Dubro",
"description": "Halfling, scruffy, looks homeless",
"align": ObjectId("4"),
"classes": [
ObjectId("2"),
ObjectId("3")
],
"carrying": [
{ "itemId": ObjectId("1271"), "qty":1, "where": "Sheath inside vest", "visible": false }
{ "itemId": ObjectId("1234"), "qty":1, "where": "Sword scabbard at waist", "visible": true }
],
"del": false
}
]
// ------------------------------------------------------------
// This is my MongoDb aggregation in the REST api routes
var linkedModels = [
{
"$match": { "del": false }
}, {
"$lookup": {
from: "charnpcclass",
localField: "classes",
foreignField: "_id",
as: "linked_classes"
}
}, {
"$lookup": {
from: "charnpcalign",
localField: "alignId",
foreignField: "_id",
as: "linked_align"
}
}, {
"$lookup": {
from: "item",
localField: "carrying.itemId",
foreignField: "_id",
as: "linked_carrying"
}
}
];
db.collection('character').aggregate(linkedModels).toArray(function (err, docs) {
res.json(201, docs);
next();
});
// Query for Character, return items carrying with data from items collection
// ------------------------------------------------------------
// WHAT I *WANT* IN RESPONSE...
{
"id": "3345",
"name": "Offut 'Dead Dog' Dubro",
"description": "Halfling, scruffy, looks homeless",
"align": "4",
"classes": [
"2",
"3"
],
"carrying": [
{ "itemId": "1271", "qty":1, "where": "Sheath inside vest", "visible": false }
{ "itemId": "1234", "qty":1, "where": "Sword scabbard at waist", "visible": true }
],
"linked_align": [
{ "_id": "4", "name": "Neutral" },
],
"linked_classes": [
{ "_id": "2", "name": "Thief" },
{ "_id": "3", "name": "Cleric" }
],
"linked_carrying": [
{ "_id": "1271", "name": "Dagger", "encumber": 0 },
{ "_id": "1234", "name": "Sword", "encumber": 2 }
]
}
// ------------------------------------------------------------
// WHAT I ACTUALLY GET IN RESPONSE
{
"id": "3345",
"name": "Offut 'Dead Dog' Dubro",
"description": "Halfling, scruffy, looks homeless",
"align": "4",
"classes": [
"2",
"3"
],
"carrying": [
{ "itemId": "1271", "qty":1, "where": "Sheath inside vest", "visible": false }
{ "itemId": "1234", "qty":1, "where": "Sword scabbard at waist", "visible": true }
],
"linked_align": [
{ "_id": "4", "name": "Neutral" },
],
"linked_classes": [],
"linked_carrying": []
}
我希望您注意到的问题在JSON响应示例底部的上方.我的链接数组为空，我不确定如何解决此问题.
The problem that I hope you noticed is just above, at bottom of JSON response example. My linked arrays are empty and I'm not sure how to solve this.
非常感谢您的专家MongoDb查询建议:-)
I would greatly appreciate your expert MongoDb querying advice :-)
推荐答案
您必须$unwind展平标量和子文档的外部_id，并在管道末尾添加$group阶段以取回原始文件结构.
You have to $unwind to flatten the both scalar and sub document foreign _ids and add $group stage at the end of the pipeline to get back the original structure.
$first累加器保留字段，而$push和$arrayElemAt保留数组值以调整$unwind
$first accumulator to keep the fields and $push with $arrayElemAt to accumulate the array values to adjust for $unwind
var linkedModels = [
{
"$match": { "del": false }
},
{
"$lookup": {
from: "charnpcalign",
localField: "align",
foreignField: "_id",
as: "linked_align"
}
},
{
"$unwind":"$classes"
},
{
"$lookup": {
from: "charnpcclass",
localField: "classes",
foreignField: "_id",
as: "linked_classes"
}
},
{
"$group": {
"_id": "$_id",
"name": {"$first":"$name"},
"align": {"$first":"$align"},
"classes":{"$push":"$classes"},
"carrying":{"$first":"$carrying"},
"linked_align":{"$first":"$linked_align"},
"linked_classes":{"$push":{"$arrayElemAt":["$linked_classes",0]}}
}
},
{
"$unwind":"$carrying"
},
{
"$lookup": {
from: "item",
localField: "carrying.itemId",
foreignField: "_id",
as: "linked_carrying"
}
},
{
"$group": {
"_id": "$_id",
"name": {"$first":"$name"},
"align": {"$first":"$align"},
"classes":{"$first":"$classes"},
"linked_align":{"$first":"$linked_align"},
"carrying":{"$push":"$carrying"},
"linked_carrying":{"$push":{"$arrayElemAt":["$linked_carrying",0]}}
}
}
]
您不需要在3.4版本的标量数组(classes)上使用$unwind，并且可以替换{"classes":{"$push":"$classes"}}& {"linked_classes":{"$push":{$arrayElemAt:["$linked_classes",0]}}}和{"classes":{"$first":"$classes"}}& {"linked_classes":{"$first":"$linked_classes"}}.
You don't need the $unwind on the scalar array (classes) in 3.4 version and you can replace the {"classes":{"$push":"$classes"}} & {"linked_classes":{"$push":{$arrayElemAt:["$linked_classes",0]}}} with {"classes":{"$first":"$classes"}} & {"linked_classes":{"$first":"$linked_classes"}} respectively.
这篇关于MongoDb聚合$ lookup与数组中的外部_ids的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
