首页 文章

MongoDb在数组中使用外部_id进行聚合$ lookup

提问于
浏览 次
0

我是MongoDb的新手 . 我变得很好，但还没有专家 . 我正在尝试以一种有意义的方式设置我的收藏品 . 我想在只有_ids的数组中保留一些外部文档的链接，以及有_ids的对象数组 .

我创建了一个带有注释的JSON文档，我认为这些文档完全显示了我正在尝试做的事情......

// ( 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响应示例的底部 . 我的链接数组是空的，我不知道如何解决这个问题 .

非常感谢您的专家MongoDb查询建议:-)

mongodb mongodb-query aggregation-framework

1 回答

  • 3

    您必须 $unwind 来展平标量和子文档外部_id并在管道末尾添加 $group 阶段以恢复原始结构 .

    $first accumulator保持字段和 $push 与 $arrayElemAt 累积数组值以调整 $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":{"$first":"$classes"}} 和 {"linked_classes":{"$first":"$linked_classes"}} 替换 {"classes":{"$push":"$classes"}} 和 {"linked_classes":{"$push":{$arrayElemAt:["$linked_classes",0]}}} .

    回复于

相关问题