https://chikuwa-tech-study.blogspot.com/2022/03/mongodb-aggregation-pipeline-practice.html

mongo-aggregate.js

db.student.aggregate(
    [
        {
            "$match": {
                "grade": 4
            }
        },
        {
            "$unwind": {
                "path": "$courseIds",
                "preserveNullAndEmptyArrays": true
            }
        },
        {
            "$lookup": {
                "localField": "courseIds",
                "from": "course",
                "foreignField": "_id",
                "as": "courseInfo"
            }
        },
        {
            "$unwind": {
                "path": "$courseInfo",
                "preserveNullAndEmptyArrays": true
            }
        },
        {
            "$group": {
                "_id": "$_id",
                "name": {
                    "$first": "$name"
                },
                "grade": {
                    "$first": "$grade"
                },
                "courseInfoList": {
                    "$push": "$courseInfo"
                }
            }
        },
        {
            "$addFields": {
                "totalPoint": {
                    "$sum": "$courseInfoList.point"
                },
                "amountOfCourse": {
                    "$size": "$courseInfoList"
                }
            }
        }
    ]
);

mongo-course.json

[
    {
        "_id": "C1",
        "name": "程式設計",
        "point": 5
    },
    {
        "_id": "C2",
        "name": "英文",
        "point": 3
    },
    {
        "_id": "C3",
        "name": "計算機概論",
        "point": 4
    }
]

mongo-student.json

[
    {
        "_id": "S1",
        "name": "Vincent",
        "grade": 4,
        "courseIds": ["C1", "C2"]
    },
    {
        "_id": "S2",
        "name": "Dora",
        "grade": 3,
        "courseIds": ["C2", "C3"]
    }
]