CODEWITHSUNDEEP
mongodbaggregation-framework
nottledim
nottledim

Reputation: 85

Mongodb aggregate multilevels

I'm trying to count documents containing { date, direction, procedure } e.g {'Dec 12', 'West', 'Up' }

and I want output: foreach date, foreach direction, count each procedure type

Dec 12
    North Up 2  Down 3
    South Up 4  Down 17
    etc

It's fairly easy using javascript but I'd like to use mongodb if possible. I can't get aggregate group to filter more than one level and I'm not sure if map_reduce would help. I don't properly understand either.

I would appreciate a little guidance. Thanks

Some detail: It's a schema-less collection but the interesting bits look like this:

 { "_id" : ObjectId(), "direction" : String, "procedure" : String, "date" : String, .... , "format" : "procedure" }

The output is not critical - it could be:

[ { date: "Mmm dd",
    direction:  { procedure: count, procedure: count },
    direction:  { procedure: count, ... },
       ....
   }

  { ... }

  ...
]

e.g:

[ { date: "Dec 12",
    "West": { "Arrive": 5, "Depart": 5 },
    "East": { "Arrive": 1, "Depart": 7 },
    ...
  },
  { date: ...},
  ...
]

The more I play with it the more I think it's a bit of a stretch - That could be good advise :-)

Upvotes: 0

Views: 74

Answers (1)

dgiugg
dgiugg

Reputation: 1334

This is a solution for your aggregation pipeline:

[{
    '$group': {
        '_id': {
            'date': '$date',
            'direction': '$direction',
            'procedure': '$procedure'
        },
        'count': {'$sum': 1}
    }
},
{
    '$group': {
        '_id': '$_id.date',
        'directions': {
            '$push': {
                'direction': '$_id.direction',
                'procedure': '$_id.procedure',
                'count': '$count'
            }
        }
    }
}]

Giving the following result:

{
    _id: "Dec 12",
    directions: [
        { "direction": "North", "procedure": "Arrive", "count": 5},
        { "direction": "North", "procedure": "Depar", "count": 3},
        { "direction": "South", "procedure": "Arrive", "count": 1},
        ...
    ]
},
...

Explanation

Basically what you are asking for is a count for each (date, direction, procedure) tuple. You just want it to be a little reorganized, and more precisely: grouped by date with for each date all possible (direction, procedure) couples, and the corresponding count.

So we are exactly doing this:

  • first $group stage in the pipeline groups by unique (date, direction, procedure), putting them in the _id field, and counting occurences; at this stage the output is:

    [{ _id: { date: "Dec 12", direction: "North", procedure: "Depar" }, count: 4 }, ... ]

  • second $group stage just re-groups the results by date pushing other fields (which are embedded in a document at the _id field, as result of the previous stage) into an array at the new directions field, as (direction, procedure, count) tuples with the same date.

Upvotes: 2

Related Questions