如何在两个集合之间建立查找连接 [英] How to Make a Lookup connection between two Collection

问题描述

目标:
此sql及其结果应与mongoDB的查询代码中的结果相同.
换句话说，对于mongoDB，结果相同.

Goal:
This sql and its result should be the same result from mongoDB's query code.
In order words, same result but for mongoDB.

问题:
如何在Mongo DB的查询代码中与 People 和 Role 相关的查找连接?

Problem:
How to you make a lookup connection in relation to People and Role in Mongo DB's query code?

信息:
我是mongo DB的新手

Info:
I'm new in mongo DB

SQL code 
    SELECT 
        a.*,
        '.' AS '.',
        b.*,
        '.'  AS '.',
        c.*
    FROM 
        [db1].[dbo].[People_Course_Grade] a
        INNER JOIN [db1].[dbo].[People] b on a.PeopleId = b.PeopleId
        INNER JOIN [db1].[dbo].[Role] c on b.RoleId = c.RoleId

Json数据:

Role:  
[{"RoleId":1,"Name":"Student"},{"RoleId":2,"Name":"Teacher"}]

People_Course_Grade:   
[{"People_Course_GradeId":1,"PeopleId":1,"CourseId":1}, 
 {"People_Course_GradeId":2,"PeopleId":2,"CourseId":1}, 
 {"People_Course_GradeId":3,"PeopleId":3,"CourseId":2}, 
 {"People_Course_GradeId":4,"PeopleId":1,"CourseId":2}]

Course:
[{"CourseId":1,"Name":"Java"},{"CourseId":2,"Name":"Java II"}, 
 {"CourseId":3,"Name":"Statistik 1"}]

db.People_Course_Grade.aggregate([

    {
        $lookup:{
            from: "People",            
            localField: "people_id",   
            foreignField: "_id",       
            as: "people"               
        }
    },
    {   $unwind:"$people" },   


    {   
        $project:{
            course_id : 1,
            people_id : 1,
      //      grade_id : 1,
            Name : "$people.Name",


        } 
    }

]);

推荐答案

您需要以双 $ arrayElemAt 始终获取单个查找结果中的元素.要使您的结构扁平化，可以将 $ replaceRoot 与 $ mergeObjects (从和course到根级别.

You need to start with double $lookup since you have three collections. Then you can use $arrayElemAt to always get single element from lookup's result. To flatten your structure you can use $replaceRoot with $mergeObjects (promotes all the fields from people and course to root level.

db.People_Course_Grade.aggregate([
    {
        $lookup:{
            from: "Role",            
            localField: "PeopleId",   
            foreignField: "RoleId",       
            as: "people"               
        }
    },
    {
        $lookup:{
            from: "Course",            
            localField: "CourseId",   
            foreignField: "CourseId",       
            as: "course"               
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $mergeObjects: [
                    "$$ROOT",
                    { $arrayElemAt: [ "$people", 0 ] },
                    { $arrayElemAt: [ "$course", 0 ] },
                ]
            }
        }
    },
    {
        $project: {
            people: 0,
            course: 0
        }
    }
])

蒙戈游乐场

$arrayElemAt始终可以像您尝试的那样用$unwind替换.您在name字段上还存在命名冲突，因此可能需要运行$project重命名这些字段之一-否则最终结果中只会得到其中一个.

$arrayElemAt can always be replaced with $unwind like you tried. You also have a naming conflict on name field so probably you need to run $project to rename one of those fields - otherwise you'll get only one of them in final result.

这篇关于如何在两个集合之间建立查找连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！