Group By but retaining the original document contents

Working with Data
1

If I use the Aggregate Framework, just to do a simple “group by” function, how do I retain the original document contents? Is there a preferred practice?

In my case I have a collection of golf scores, such as (note, total equals the sum of the holes array values):

{
    "compId": "607019361c071256e4f0d0d5",
    "courseId": "608952e3abebbd503ba6e115",
    "playerId": "609d0993906429612483cea0",
    "holes": {
        "1": 2,
        "2": 1,
        "3": 2,
        "4": 2,
        "5": 2,
        "6": 2,
        "7": 0,
        "8": 2,
        "9": 3
    },
    "playerName": "Tiger Woods",
    "total": 16
}

Tiger may have multiple documents, for multiple rounds that I want to sum the total, but I also want to show all the holes (iterate over the holes sub-array).

If I apply a simple group by function, it only returns the $sum'ed total.

[{
    $match: {
        "playerName": "Dan Burt"
    }
}, {
    $group: {
        _id: "$playerName",
        "total": {
            $sum: "$total"
        }
    }
}]

Returns:

{
  "_id" : "Tiger Woods",
  "total" : 45
}
1 Like
2

You can use $push and create an array of whatever fields you want to keep. You can use $$ROOT to keep the entire original document but it’s not really a good idea when you know which fields you actually will need - it’s better to just push those fields then.

Asya

3 Likes
3

is this the normal / preferred method?

I can try some testing scenarios tonight, but can you indicate how I would do it with documents structured as above, so that the holes array in each document would be preserved?

They would actually be tagged against the courseId value, so would that become an array key?

Something like:

{
  "_id" : "Tiger Woods",
  "total" : 45
  "holes" : {
    "course1" : { ... },
    "course2" : { ... },
    "courseN" : { ... }
  }
}
4

So I have been able to add in the holes array using a $push as follows:

{
  _id: "$playerName",
  total: {
    $sum: "$total"
  },
  rounds: { $push: "$holes" }
}

Which returns a document as:

image

Can I put the courseId from each of the source documents (from before the $group) so I can reference it later - basically so my PHP page can loop through these and add the scores to the correct tables.

Ideally, it would look like what I posted in my previous thread. Is this just posting the array key as well as the value?

5

Bump! Any one with suggestions?

1 Like
6

You would do the same thing but adding a subdocument:

rounds: { $push: {course:"$courseId", holes: "$holes" } }

Alternatively, you could group by course pushing holes - then if you want a single document per player you can group again pushing course which now has holes array… Not sure exactly the format you want but you can definitely get it with some permutation of these techniques.