mongodb - 在相同的结构中加入并合并外部文档

Question

我有两个用于构建调查表的集合：

调查收集有一个文件：

{
    "_id" : ObjectId("5bbcebfc2436f1d3e6275a5c"),
    "name" : "Some survey name",
    "score" : 12,
    "evidences" : [ 
        {
            "name" : "Evidence Method 1",
            "sections" : [ 
                {
                    "name" : "section1",
                    "questions" : [ 
                        "QID1", 
                        "QID2"
                    ]
                }, 
                {
                    "name" : "section2",
                    "questions" : [ 
                        "QID3", 
                        "QID4"
                    ]
                }
            ]
        }, 
        {
            "name" : "Evidence method 2",
            "sections" : [ 
                {
                    "name" : "section1",
                    "questions" : [ 
                        "QID5"
                    ]
                }, 
                {
                    "name" : "section2",
                    "questions" : [ 
                        "QID6"
                    ]
                }
            ]
        }
    ]
}

问题集有以下文件：

/* 1 */
{
    "_id" : ObjectId("5bbcec1e2436f1d3e6275a6a"),
    "questionId" : "QID1",
    "answer" : "",
    "options" : [ 
        {
            "value" : "yes",
            "label" : "YES"
        }, 
        {
            "value" : "no",
            "label" : "NO"
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5bbcec322436f1d3e6275a73"),
    "questionId" : "QID2",
    "answer" : "",
    "options" : [ 
        {
            "value" : "yes",
            "label" : "YES"
        }, 
        {
            "value" : "no",
            "label" : "NO"
        }
    ]
}

/* 3 */
{
    "_id" : ObjectId("5bbe12fd2436f1d3e62795e7"),
    "questionId" : "QID3",
    "answer" : "",
    "options" : [ 
        {
            "value" : "yes",
            "label" : "YES"
        }, 
        {
            "value" : "no",
            "label" : "NO"
        }
    ]
}

/* 4 */
{
    "_id" : ObjectId("5bbe130a2436f1d3e62795ef"),
    "questionId" : "QID4",
    "answer" : "",
    "options" : [ 
        {
            "value" : "yes",
            "label" : "YES"
        }, 
        {
            "value" : "no",
            "label" : "NO"
        }
    ]
}

/* 5 */
{
    "_id" : ObjectId("5bbe7fdc2436f1d3e627a108"),
    "questionId" : "QID5",
    "answer" : "",
    "options" : [ 
        {
            "value" : "yes",
            "label" : "YES"
        }, 
        {
            "value" : "no",
            "label" : "NO"
        }
    ]
}

/* 6 */
{
    "_id" : ObjectId("5bbe7fe92436f1d3e627a10e"),
    "questionId" : "QID6",
    "answer" : "",
    "options" : [ 
        {
            "value" : "yes",
            "label" : "YES"
        }, 
        {
            "value" : "no",
            "label" : "NO"
        }
    ]
}

我正在使用 mongodb 4，目前使用以下查询，我可以加入 2 个集合，但它所做的是将引用的文档放在文档中的单独字段中，我希望它位于文档结构本身中.

db.createView (
"surveyquestions",
"survey",
    [
        {
                $lookup: {
                from: "questions",
                localField: "evidences.sections.questions",
                foreignField: "questionId",
                as: "question_docs"
            }
        }
    ]
)

Veeram 的回答，除了它通过证据生成 2 个不同的文件而不是如下的一个之外，它的工作原理是 -

/* 1 */
{
    "_id" : {
        "_id" : {
            "_id" : ObjectId("5bbcebfc2436f1d3e6275a5c"),
            "evidences_name" : "Evidence Method 1"
        }
    },
    "name" : "Some survey name",
    "score" : 12,
    "evidences" : [ 
        {
            "sections" : [ 
                {
                    "name" : "section1",
                    "questions" : [ 
                        {
                            "_id" : ObjectId("5bbcec1e2436f1d3e6275a6a"),
                            "questionId" : "QID1",
                            "answer" : "",
                            "options" : [ 
                                {
                                    "value" : "yes",
                                    "label" : "YES"
                                }, 
                                {
                                    "value" : "no",
                                    "label" : "NO"
                                }
                            ]
                        }, 
                        {
                            "_id" : ObjectId("5bbcec322436f1d3e6275a73"),
                            "questionId" : "QID2",
                            "answer" : "",
                            "options" : [ 
                                {
                                    "value" : "yes",
                                    "label" : "YES"
                                }, 
                                {
                                    "value" : "no",
                                    "label" : "NO"
                                }
                            ]
                        }
                    ]
                }, 
                {
                    "name" : "section2",
                    "questions" : [ 
                        {
                            "_id" : ObjectId("5bbe12fd2436f1d3e62795e7"),
                            "questionId" : "QID3",
                            "answer" : "",
                            "options" : [ 
                                {
                                    "value" : "yes",
                                    "label" : "YES"
                                }, 
                                {
                                    "value" : "no",
                                    "label" : "NO"
                                }
                            ]
                        }, 
                        {
                            "_id" : ObjectId("5bbe130a2436f1d3e62795ef"),
                            "questionId" : "QID4",
                            "answer" : "",
                            "options" : [ 
                                {
                                    "value" : "yes",
                                    "label" : "YES"
                                }, 
                                {
                                    "value" : "no",
                                    "label" : "NO"
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    ]
}

/* 2 */
{
    "_id" : {
        "_id" : {
            "_id" : ObjectId("5bbcebfc2436f1d3e6275a5c"),
            "evidences_name" : "Evidence method 2"
        }
    },
    "name" : "Some survey name",
    "score" : 12,
    "evidences" : [ 
        {
            "sections" : [ 
                {
                    "name" : "section1",
                    "questions" : [ 
                        {
                            "_id" : ObjectId("5bbe7fdc2436f1d3e627a108"),
                            "questionId" : "QID5",
                            "answer" : "",
                            "options" : [ 
                                {
                                    "value" : "yes",
                                    "label" : "YES"
                                }, 
                                {
                                    "value" : "no",
                                    "label" : "NO"
                                }
                            ]
                        }
                    ]
                }, 
                {
                    "name" : "section2",
                    "questions" : [ 
                        {
                            "_id" : ObjectId("5bbe7fe92436f1d3e627a10e"),
                            "questionId" : "QID6",
                            "answer" : "",
                            "options" : [ 
                                {
                                    "value" : "yes",
                                    "label" : "YES"
                                }, 
                                {
                                    "value" : "no",
                                    "label" : "NO"
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    ]
}

标签： mongodbmongodb-queryaggregation-framework

Answer 1

您可以尝试以下聚合。

db.createView(
    "surveyquestions",
    "survey",
    [
      {"$unwind":"$evidences"},
      {"$unwind":"$evidences.sections"},
      {"$lookup":{
        "from":"questions",
        "localField":"evidences.sections.questions",
        "foreignField":"questionId",
        "as":"evidences.sections.questions"
      }},
      {"$group":{
        "_id":{"_id":"$_id","evidences_name":"$evidences.name"},
        "name":{"$first":"$name"},
        "score":{"$first":"$score"},
        "sections":{"$push":"$evidences.sections"}
       }},
       {"$group":{
         "_id":"$_id._id",
         "name":{"$first":"$name"},
         "score":{"$first":"$score"},
         "evidences":{"$push":{"name":"$_id.evidences_name","sections":"$sections"}}
       }}
     ]
)