MongoDB聚合复杂对象而不破坏其结构

Question

我有一个集合，其内容如下:

[
    {
        date: "01-01-2001",
        hour: 7,
        level2: [
            {
                name: "level2A",
                level3: [
                    {
                        name: "level3A",
                        statistic1: 1,
                        statistic2: 1,
                    },
                    {
                        name: "level3B",
                        statistic1: 2,
                        statistic2: 2,
                    }
                ]
            },
            {
                name: "level2B",
                level3: [
                    {
                        name: "level3A",
                        statistic1: 3,
                        statistic2: 3,
                    },
                    {
                        name: "level3B",
                        statistic1: 4,
                        statistic2: 4,
                    }
                ]
            }
        ]
    },
    {
        date: "01-01-2001",
        hour: 8,
        level2: [
            {
                name: "level2A",
                level3: [
                    {
                        name: "level3A",
                        statistic1: 5,
                        statistic2: 5,
                    },
                    {
                        name: "level3B",
                        statistic1: 6,
                        statistic2: 6,
                    }
                ]
            },
            {
                name: "level2B",
                level3: [
                    {
                        name: "level3A",
                        statistic1: 7,
                        statistic2: 7,
                    },
                ]
            }
        ]
    }
]

在第一个级别中，我有一个日期、一个小时和一个级别 2 元素的集合。我每小时都有一个元素。

在第二级中，我有一个名称和一个 3 级元素的集合。请注意，该名称对于当前数组来说是唯一的，但可以在不同时间的对象中重复。

在最后一个关卡中，我有一个名字和一些号码。请注意，该名称对于当前数组来说是唯一的，但可以在不同 level2 元素的对象中重复。

我需要按日期聚合数据，统计数字将通过键进行汇总:日期+ level2.name + level3.name，而不破坏我的复杂对象的结构。对于我在此处发布的示例内容，我希望获得以下输入结果:{date: "01-01-2001"}:

[
    {
        date: "01-01-2001",
        hour: null,
        level2: [
            {
                name: "level2A",
                level3: [
                    {
                        name: "level3A",
                        statistic1: 6,
                        statistic2: 6,
                    },
                    {
                        name: "level3B",
                        statistic1: 8,
                        statistic2: 8,
                    }
                ]
            },
            {
                name: "level2B",
                level3: [
                    {
                        name: "level3A",
                        statistic1: 10,
                        statistic2: 10,
                    },
                    {
                        name: "level3B",
                        statistic1: 4,
                        statistic2: 4,
                    }
                ]
            }
        ]
    },
]

如何在不破坏结构然后在 Node.js 代码中重建它的情况下做到这一点？

Answer 1

尝试如下所示的操作。首先，展开所有嵌套数组，对第 3 级统计字段进行分组和求和，然后对第 3 级进行分组和推送，然后对第 2 级进行分组和推送，最后将字段投影到结果。

db.collection.aggregate([{
    $unwind: "$level2"
}, {
    $unwind: "$level2.level3"
}, {
    $group: {
        _id: {
            date: "$date",
            level2: "$level2.name",
            level3: "$level2.level3.name"
        },
        statistic1: {
            $sum: "$level2.level3.statistic1"
        },
        statistic2: {
            $sum: "$level2.level3.statistic2"
        }
    }
}, {
    $group: {
        _id: {
            date: "$_id.date",
            level2: "$_id.level2"
        },
        level3: {
            $push: {
                name: "$_id.level3",
                statistic1: "$statistic1",
                statistic2: "$statistic2"
            }
        }
    }
}, {
    $group: {
        _id: "$_id.date",
        level2: {
            $push: {
                name: "$_id.level2",
                level3: "$level3"
            }
        }
    }
}, {
    $project: {
        _id: 0,
        date: "$_id",
        level2: "$level2"
    }
}]);

输出:

{
    "level2": [{
        "name": "level2A",
        "level3": [{
            "name": "level3B",
            "statistic1": 8,
            "statistic2": 8
        }, {
            "name": "level3A",
            "statistic1": 6,
            "statistic2": 6
        }]
    }, {
        "name": "level2B",
        "level3": [{
            "name": "level3B",
            "statistic1": 4,
            "statistic2": 4
        }, {
            "name": "level3A",
            "statistic1": 10,
            "statistic2": 10
        }]
    }],
    "date": "01-01-2001"
}