我想通过在 MongoDB 上使用 $lookup 和 $match 来获取带有外键的文档。

有一个“Jobs”集合，用于存储 Job 文档。在作业文档中有两个字段用作外键“creatorParent”和“Children”。 CreatorParent 是“Users”集合的外键，Children 数组包含用户 child 的 ID。

当我列出所有作业时，我想从“用户”集合中检索 CreatorParent ID 和 ChildrenID 的详细信息。我想用 ParentDetail 和 ChildDetail 编码“工作”文档。我不想为此编写自定义方法。是否可以使用 MongoDB 查询来处理它？<​​>

顺便说一下，我是 MongoDB 的初学者，所以应该在 Children 和 CreatorParent 上存储所需的详细信息而不是存储 ObjectId？

用户文档:

{
    "_id" : ObjectId("58daf84877733645eaa9b44f"),
    "email" : "meto93@gmail.com",
    "password" : "vpGl+Fjnef616cRgNbCkwaFDpSI=",
    "passwordsalt" : "99397F4A9D3A499D96694547667E74595CE994D2E83345D6953EF866303E8B65",
    "children" : [ 
        {
            "_id" : ObjectId("58daf84977733645eaa9b450"),
            "name" : "Mert",
            "age" : 5,
            "additionalinformation" : "ilk cocuk",
            "creationtime" : ISODate("2017-03-28T23:56:56.952Z"),
            "userid" : ObjectId("58daf84877733645eaa9b44f"),
            "gender" : null
        }, 
        {
            "_id" : ObjectId("58daf84977733645eaa9b451"),
            "name" : "Sencer",
            "age" : 7,
            "additionalinformation" : "ikinci cocuk",
            "creationtime" : ISODate("2017-03-28T23:56:56.952Z"),
            "userid" : ObjectId("58daf84877733645eaa9b44f"),
            "gender" : null
        }
    ]
}

工作

{
    "_id" : ObjectId("58db0a2d77733645eaa9b453"),
    "creationtime" : ISODate("2017-03-29T01:13:17.509Z"),
    "startingtime" : ISODate("2017-04-03T13:00:00.000Z"),
    "endingtime" : ISODate("2017-04-03T17:00:00.000Z"),
    "children" : [ 
        ObjectId("58daf84977733645eaa9b450"), 
        ObjectId("58daf84977733645eaa9b451")
    ],
    "creatorparent" : ObjectId("58daf84877733645eaa9b44f"),
    "applicants" : []
}

最佳答案

如果我没理解错的话。使用 MongoDB 3.4 的 $addFields 和 $lookup 聚合步骤可以实现类似的解决方案。

Mongo 聚合:

[
    {
        $addFields: { 
            "job":"$$ROOT"
        }
    },
    {
        $unwind: {
            path : "$children"
        }
    },
    {
        $lookup: {
            "from" : "users",
            "localField" : "creatorParent",
            "foreignField" : "_id",
            "as" : "creatorParent"
        }
    },
    {
        $lookup: {
            "from" : "users",
            "localField" : "children",
            "foreignField" : "_id",
            "as" : "children"
        }
    },
    {
        $group: {
            "_id": "$_id",
            "job": { "$first": "$job" },
            "creatorParent" : { "$first" : "$creatorParent" },
            "children": { "$addToSet": {  $arrayElemAt: [ "$children", 0 ]  } }
        }
    }
]

输出将如下所示:

{ "_id" : ObjectId("58da9cb6340c630315348114"), 
"job" : {
    "_id" : ObjectId("58da9cb6340c630315348114"), 
    "name" : "Developer", 
    "creatorParent" : ObjectId("58da9c79340c630315348113"), 
    "children" : [
        ObjectId("58da9c6d340c630315348112"), 
        ObjectId("58da9c5f340c630315348111")
    ], 
    "hourly_rate" : 12.0, 
    "additional_information" : "other infos"
}, 
"creatorParent" : [
    {
        "_id" : ObjectId("58da9c79340c630315348113"), 
        "name" : "The Boss", 
        "age" : 40.0
    }
], 
"children" : [
    {
        "_id" : ObjectId("58da9c5f340c630315348111"), 
        "name" : "James", 
        "age" : 28.0
    }, 
    {
        "_id" : ObjectId("58da9c6d340c630315348112"), 
        "name" : "Andrew", 
        "age" : 26.0
    }
]}

更新:

如果你用这个替换最后一个 $group 阶段:

{
    "_id": "$_id",
    "name": { "$first": "$name" },
    "jobstatus": { "$first": "$jobstatus" },
    "hourlyrate": { "$first":"$hourlyrate" },
    "creatorparent" : { "$first" : "$creatorparent" },
    "children": { "$addToSet": {  $arrayElemAt: [ "$children", 0 ]  } }
}

然后你可以实现你想要的，但是在这个$group阶段你必须用$first一个一个地指定工作的每个领域表达。

关于mongodb - $lookup 和 $match Mongodb golang，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/43074187/