Mongodb聚合行到列 [英] Mongodb Aggregation Rows to Columns

问题描述

我有以下数据集.我需要按帐户"对它们进行分组，然后将Element_Fieldname变成一列.

I have the following dataset. I need to group them by Account, and then turn the Element_Fieldname into a column.

var collection = [
    {
        Account:12345,
        Element_Fieldname:"cars",
        Element_Value:true
    },
    {
        Account:12345,
        Element_Fieldname:"boats",
        Element_Value:false
    }
]

这是我尝试将行转换为列的方法，但是不起作用.

This was my attempt to convert rows to columns, but its not working.

db.getCollection('my_collection').aggregate([{
            $match : {
                Element_Fieldname : {
                    $in : ["cars", "boats"]
                }
            }
        }, {
            $group : {
                _id : "$Account",
                values : {
                    $addToSet : {
                        field : "$Element_Fieldname",
                        value : "$Element_Value"
                    }
                }
            }
        }, {
            $project : {
                Account : "$_id",
                cars : {
                    "$cond" : [{
                            $eq : ["$Element_Fieldname", "cars"]
                        }, "$Element_Value", null]
                },
                boats : {
                    "$cond" : [{
                            $eq : ["$Element_Fieldname", "day_before_water_bottles"]
                        }, "$Element_Value", null]
                },
            }
        }
    ])

这只是在我的cars和boats字段中给了我null.任何帮助都会很棒.

This just gives me null in my cars and boats fields. Any help would be great.

这是我想要的结果:

var desiredResult = [
    {
        Account:12345,
        cars:true,
        boats:false
    }
]

推荐答案

这很棘手，但是您会得到所需的:-)

this is a big tricky but you will get what you need :-)

请在聚合管道顶部添加$ match

please add $match on the top of aggregation pipeline

db.collection.aggregate([{
            $project : {
                _id : 0,
                "Account" : 1,
                car : {
                    $cond : [{
                            $eq : ["$Element_Fieldname", "cars"]
                        }, "$Element_Value", null]
                },
                boats : {
                    $cond : [{
                            $eq : ["$Element_Fieldname", "boats"]
                        }, "$Element_Value", null]
                },
            }
        },
        {
            $group : {
                _id : "$Account",
                carData : {
                    $addToSet : "$car"
                },
                boatsData : {
                    $addToSet : "$boats"
                }
            }
        }, {
            $unwind : "$carData"
        }, {
            $match : {
                carData : {
                    $ne : null
                }
            }
        }, {
            $unwind : "$boatsData"
        }, {
            $match : {
                boatsData : {
                    $ne : null
                }
            }
        },
    ])

和结果

{
    "_id" : 12345,
    "carData" : true,
    "boatsData" : false
}

这篇关于Mongodb聚合行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！