CODEWITHSUNDEEP
mongodbmapreducemongodb-query
Matthias Herrmann
Matthias Herrmann

Reputation: 2790

Mongodb Map Reduce: How can I group the result?

I'm using map reduce and not the aggregation Framework because I do some string manipulation and value checks.

I have a query which returns the device count which have specific operating systems. E.g. one query which I have already implemented returns a result which looks like this: [{"_id":"Android","value":800}, {"_id":"Windows Phone","value":50}]

I'm already using the query parameter to boost performance. Now I want to use map reduce and the result should be grouped by the Buildings where the devices are located.

The database structure: A collection which contains multiple documents having the properties: OperatingSystem, Building

{ "_id" : ObjectId("5a0174c616bcd022e04fcc43"), "OperatingSystem": "Android", "Building": xy"}

Note: this is simplified, e.g. the OperatingSystem contains the version number in the string, but I'm already dealing with that.

What I have tried so far:

let map = function () {
    // some manipulation of property values
    // type checks + error handling
    emit({op: this.operatingSystem, this.building], 1); 
}

let reduce = function (key, values) {
        return Array.sum(values);
}

But the result is not in the format which I want:

[{"_id":"Android","value":222}, {"_id":{"operatingSystem":"Windows Phone","building":"xy"},"value":2}, {"_id":{"operatingSystem":"Android","building":"xy"},"value":5}]

The issues with the result: The key should only be the building and the properties should be the operating systems with their count for the specific building and the first entry is useless and should not be part of the result.

The result should look like this:

[{"Building": "xy", "deviceCounts": [{"Android": 50}, "Windows Phone": 20}]}, {"Building: zz" //......

How can I make building the key and save the device count by operating system and bulding in the same property?

Upvotes: 0

Views: 865

Answers (1)

kevinadi
kevinadi

Reputation: 13815

If the desired output format is all you require (you didn't mention what string manipulation you're doing), it is possible using the aggregation framework. For example:

> db.test.find()
{ "_id": ObjectId(...), "OperatingSystem": "Android", "Building": "xy" }
{ "_id": ObjectId(...), "OperatingSystem": "Android", "Building": "xy" }
{ "_id": ObjectId(...), "OperatingSystem": "Android", "Building": "xy" }
{ "_id": ObjectId(...), "OperatingSystem": "Android", "Building": "xy" }
{ "_id": ObjectId(...), "OperatingSystem": "Android", "Building": "xy" }
{ "_id": ObjectId(...), "OperatingSystem": "Windows Phone", "Building": "xy" }
{ "_id": ObjectId(...), "OperatingSystem": "Windows Phone", "Building": "xy" }
{ "_id": ObjectId(...), "OperatingSystem": "Android", "Building": "zz" }
{ "_id": ObjectId(...), "OperatingSystem": "Android", "Building": "zz" }
{ "_id": ObjectId(...), "OperatingSystem": "Android", "Building": "zz" }
{ "_id": ObjectId(...), "OperatingSystem": "Windows Phone", "Building": "zz" }
{ "_id": ObjectId(...), "OperatingSystem": "Windows Phone", "Building": "zz" }

In this example, there are 2 Windows Phone and 5 Android in building xy, and 2 Windows Phone and 3 Android in building zz.

I find this aggregation pipeline will output the desired format:

db.test.aggregate([
    // count occurences of building & operating system combination
    {$group: {_id: {building: '$Building', os: '$OperatingSystem'}, count: {$sum:1}}},
    // group by building
    {$group: {_id: '$_id.building', device: {$push: {k: '$_id.os', v: '$count'}}}},
    // project into the desired format
    {$project: {Building: '$_id', deviceCounts: {$arrayToObject: '$device'}}}
])

Example output is:

{ "_id": "xy", "Building": "xy", "deviceCounts": { "Windows Phone": 2, "Android": 5 } }
{ "_id": "zz", "Building": "zz", "deviceCounts": { "Android": 3, "Windows Phone": 2 } }

Note that there's an additional _id field in the output, since MongoDB required every document to have an _id field.

The aggregation pipelines used ($arrayToObject) is only available in MongoDB 3.4 series.

Upvotes: 1

Related Questions