Mongodb Aggregation Rows to Columns

Question

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]
                },
            }
        }
    ])

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

And this is my desired results:

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

profesor79 · Accepted Answer · 2016-05-05 14:53:14Z

2

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

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
                }
            }
        },
    ])

and result

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

edited May 5, 2016 at 14:53

answered May 5, 2016 at 14:47

profesor79

9,5033 gold badges36 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rob Over a year ago

You solved it, awesome. How do you think this would do at scale though? My working dataset will be in the 10's of millions?

profesor79 Over a year ago

so first you will need to use disk:true in this case. The process itself is not complicated, so you could try to write this report using $out to new collection instead of running it on every user request

chridam · Accepted Answer · 2016-05-05 14:56:09Z

It is not possible to do the type of computation you are describing with the aggregation framework, however there is a proposed $arrayToObject expression which will give you the functionality to peek into the key names, and create new key/values dynamically.

For example, you could do

db.collection.aggregate([
    {
        "$match": { "Element_Fieldname":{ "$in": ["cars", "boats"] } }
    },    
    {
        "$group": {
            "_id": "$Account",
            "attrs": { 
                "$push": {
                    "key": "$Element_Fieldname",
                    "val": "$Element_Value"
                } 
            }            
        }
    },
    {
        "$project": {
            "Account": "$_id",
            "_id": 0,
            "newAttrs": {
                "$arrayToObject": {
                    "$map": {
                        "input": "$attrs",
                        "as": "el",
                        in: ["$$el.key", "$$el.val"]
                    }
                }
            }
        }
    },
    {
        "$project": {
            "Account": 1,
            "cars": "$newAttrs.cars",
            "boats": "$newAttrs.boats"
        }
    }
])

Vote for this jira ticket https://jira.mongodb.org/browse/SERVER-23310 to get this feature.

As a workaround, mapreduce seems like the available option. Consider running the following map-reduce operation:

db.collection.mapReduce(
    function() {
        var obj = {};
        obj[this.Element_Fieldname] = this.Element_Value;        
        emit(this.Account, obj);
    },
    function(key, values) {
        var obj = {};
        values.forEach(function(value) {
            Object.keys(value).forEach(function(key) {                
                obj[key] = value[key];
            });
        });
        return obj;
    },
    { "out": { "inline": 1 } }
)

Result:

{
    "_id" : 12345,
    "value" : {
        "cars" : true,
        "boats" : false
    }
}

Collectives™ on Stack Overflow

Mongodb Aggregation Rows to Columns

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related