代码之家 › 专栏 › 技术社区 › Krimson

聚合父文档中的嵌套文档

mongodb

Krimson · 技术社区 · 8 年前

我有以下数据:

{ 
    "make" : "Toyota", 
    "name" : "Corolla", 
    "year" : "2012"
    "color" : "Blue"
}
{ 
    "make" : "Toyota", 
    "name" : "Camry", 
    "year" : "2016"
    "color" : "Blue"
}
{ 
    "make" : "Toyota", 
    "name" : "Prius", 
    "year" : "2012"
    "color" : "Black"
}
{ 
    "make" : "Nissan", 
    "name" : "PathFinder", 
    "year" : 2012.0
    "color" : "Black"
}
{ 
    "make" : "Nissan", 
    "name" : "Qashqai", 
    "year" : 2011.0
    "color" : "Black"
}
{ 
    "make" : "Nissan", 
    "name" : "X-Trail", 
    "year" : 2009.0
    "color" : "Blue"
}

我想将聚合查询写入分组依据 make 然后 color 在 制作 组

因此,我想要的结果摘录如下

[{
    _id: "Toyota"
    colors: [
        {
            _id: "Black"
            cars: [{
                "make" : "Toyota", 
                "name" : "Prius", 
                "year" : "2012"
                "color" : "Black"
            }]
        },
        {
            _id: "Blue"
            cars:[{ 
                "make" : "Toyota", 
                "name" : "Corolla", 
                "year" : "2012"
                "color" : "Blue"
            },
            { 
                "make" : "Toyota", 
                "name" : "Camry", 
                "year" : "2016"
                "color" : "Blue"
            }]
        }
    ]
},
{
.... Nissan cars
}]

我已经尝试了以下管道,并已接近我想要的结果,但并不理想

db.Cars.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $group: {
                _id: {make: '$make', color: '$color'},
                cars: {$push: '$$CURRENT'}
            }
        },

        // Stage 2
        {
            $group: {
                _id: '$_id.make',
                colors: {$push: '$$CURRENT'}
            }
        },
    ]
);

这导致了这种情况(请注意 _id 颜色字段):

[{ 
    "_id" : "Toyota", 
    "colors" : [
        {
            "_id" : {
                "make" : "Toyota", 
                "color" : "Black"
            }, 
            "cars" : [
                {
                    "make" : "Toyota", 
                    "name" : "Prius", 
                    "year" : "2012", 
                    "color" : "Black"
                }
            ]
        }, 
        {
            "_id" : {
                "make" : "Toyota", 
                "color" : "Blue"
            }, 
            "cars" : [
                {
                    "make" : "Toyota", 
                    "name" : "Corolla", 
                    "year" : "2012", 
                    "color" : "Blue"
                }, 
                {
                    "make" : "Toyota", 
                    "name" : "Camry", 
                    "year" : "2016", 
                    "color" : "Blue"
                }
            ]
        }
    ]
}
...
]

任何关于编写查询以使输出更接近所需结果的帮助都会很好

1 回复 | 直到 8 年前

Saravana 8 年前

添加一个 $project 或 $addFields 阶段至 $map colors._id 从…起 colors._id.color

管道

db.cars.aggregate(
    [
        {
            $group: {
                _id: {make: '$make', color: '$color'},
                cars: {$push: '$$CURRENT'}
            }
        },
        {
            $group: {
                _id: '$_id.make',
                colors: {$push: '$$CURRENT'}
            }
        },
        {$project : {colors : {$map : {input : "$colors", as : "color", in : { "_id" : "$$color._id.color", cars : "$$color.cars" }}}}}

    ]
).pretty()

输出

>db.cars.aggregate( [ {$project : {_id : 0}}, { $group: { _id: {make: '$make', color: '$color'}, cars: {$push: '$$CURRENT'} } }, { $group: { _id: '$_id.make', colors: {$push: '$$CURRENT'} } }, {$project : {colors : {$map : {input : "$colors", as : "color", in : { "_id" : "$$color._id.color", cars : "$$color.cars" }}}}} ] ).pretty()

{
    "_id" : "Toyota",
    "colors" : [
        {
            "_id" : "Black",
            "cars" : [
                {
                    "make" : "Toyota",
                    "name" : "Prius",
                    "year" : "2012",
                    "color" : "Black"
                }
            ]
        },
        {
            "_id" : "Blue",
            "cars" : [
                {
                    "make" : "Toyota",
                    "name" : "Corolla",
                    "year" : "2012",
                    "color" : "Blue"
                },
                {
                    "make" : "Toyota",
                    "name" : "Camry",
                    "year" : "2016",
                    "color" : "Blue"
                }
            ]
        }
    ]
}
....//output omitted