MongoDB aggregate on multiple fields

Question

I have only experience with RDBS. There is it possible to aggregate fields by more than one field.

t_example:

country  |  city   |   val  |  useless
---------------------------------------
Ger      |  Ber    |    10  |    abc
Ger      |  Ber    |    10  |    abc
Ger      |  MU     |    10  |    abc
FR       |  Par    |    10  |    abc

SELECT country,city,sum(val) FROM t_example GROUP BY country,city;

=>

country  |  city   |   val  
----------------------------
Ger      |  Ber    |    20  
Ger      |  MU     |    10  
FR       |  Par    |    10

Is it possible to get the same result by MongoDB if a collection contains the same Values on JSON files? I tried this on NodeJS:

dbs.collection('t_example').aggregate([{ $group: { "_id": "$country","count":{$sum:1}}}]).toArray(function(error,result){
            res.json(result);       
        });

I got a similiar result:

[{"_id":"Ger","count":30},{"_id":"Fr","count":10}]

Is it possible to break down the data by an additional field (city)? I also can´t change the key (_id). Otherwise the result is an empty Array.

_id takes document. Try _id:{country:"$country", city:"$city"} — s7vr
– s7vr, Commented Jan 6, 2018 at 16:53

Marcello · Accepted Answer · 2018-01-06 22:18:05Z

1

You can use the following pipeline.

[
  { $group: { 
    _id: { country: "$country", city: "$city" }, 
    val: { $sum: "$val" } 
  } }
]

In fact, for a group stage, _id can be a structured object.

The resulting documents look like this.

[
    {
        "_id" : {
            "country" : "Fr",
            "city" : "Par"
        },
        "val" : 10.0
    },
    {
        "_id" : {
            "country" : "Ger",
            "city" : "MU"
        },
        "val" : 10.0
    },
    {
        "_id" : {
            "country" : "Ger",
            "city" : "Ber"
        },
        "val" : 20.0
    }
]

answered Jan 6, 2018 at 22:18

Marcello

9196 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

MongoDB aggregate on multiple fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related