MongoDB Aggregate Query Group By

Question

Say I have a MongoDB collection containing the following information:

{
  cust_id: "abc123",
  ord_date: ISODate("2012-11-02T17:04:11.102Z"),
  state: 'CA',
  price: 50,
  item: apple,
  color: red
}
{
  cust_id: "abc123",
  ord_date: ISODate("2012-11-02T17:04:11.102Z"),
  state: 'WA',
  price: 25,
  item: apple,
  color: green
}
{
  cust_id: "abc123",
  ord_date: ISODate("2012-11-02T17:04:11.102Z"),
  state: 'CA',
  price: 75,
  item: orange,
  color: orange
}
{
  cust_id: "def456",
  ord_date: ISODate("2012-11-02T17:04:11.102Z"),
  state: 'OR',
  price: 75,
  item: apple,
  color: red
}

I want to do an aggregate sum of the total price of orders grouped by state, where the item is 'apple' and color is 'red'. My query that I have is:

{
  $match: {$and: [{item : "apple"}, {color : "red"}]},
  $group: {_id: {state: "$state", cust_id: "$cust_id"}, total: {$sum: "$price"}}
}

However, I would like the ability to have my resultant cust_id contained in _id be an array/map/some structure that contains a list of all the customer ids that comprise my aggregate total. Thus I would want my output to contain

cust_id {'abc123', 'def456'}

Is there a way to do with this mongo aggregate/querying? Or perhaps a better way to structure this query such that I can total the cost for red apples, grouped by state, and include all the customers that fall in this category? I was placing this in the _id portion in order to extract information but it's not important that any of this data be contained there. I would just like a way to group by state and obtain a collection of all customer ids with the aforementioned aggregate selection.

chridam chridam · Accepted Answer · 2015-04-20T18:23:24

Yes, in your aggregation $group pipeline you can use the $addToSet aggregation operator to add the cust_ids to an array whilst you can still group by state:

db.collection.aggregate([
    {
        "$match": {
            "item": "apple", 
            "color" : "red"
        }
    },
    {
        "$group": {
            "_id": "$state",
            "cust_id": {
                "$addToSet": "$cust_id"
            },
            "total": {
                "$sum": "$price"
            }
        }
    }
]);

Output:

/* 1 */
{
    "result" : [ 
        {
            "_id" : "OR",
            "cust_id" : [ 
                "def456"
            ],
            "total" : 75
        }, 
        {
            "_id" : "CA",
            "cust_id" : [ 
                "abc123"
            ],
            "total" : 50
        }
    ],
    "ok" : 1
}

MongoDB Aggregate Query Group By

1 Answers