How to count maximun cardinality per month?

Hello everyone,

I’m learning MongoDB and I’m practicing with a traffic database, and I’d like to know how to get the most popular radar per month (this means that for each month, the query has to show the radar with the highest number of records on it).

This is how a document looks like in the database:

{
"_id" : "20110418123336260",
"radar" : {
    "mileage" : "15",
    "direction" : "ascending",
    "speed limit" : "180"
    },
"Record" : {
    "date" : "18/04/2007",
    "time" : "12:33:36.260", //Hour<24 and .260 are ms
    "speed" : "193"
     }
}

Thanks to the help of @Pavel_Duchovny in a similar example in my last post, I got really close to the final result, but in the end I got stucked in this query, which finds for each radar his maximum number of appeareances per month:

db.traffic.aggregate([{$project: {
  "_id": "$radar",
  array :{
      $split:["$Record.date","/"]
  } 
}}, {$group: {
  _id: {radar: "$_id", month: {$arrayElemAt: ["$array",1]}},
  count: {"$sum": 1 }

 }}, {$sort: {
  "_id.radar": 1,
  "_id.month": 1,
  "count": -1
}}]);

This is how the data looks like after this query (and as I said, it continues with the rest of the months for this radar until 12 documents (12 months), when next radar is showed)

Could please someone help me? I tried to group in the next stage and I tried to use first, $ROOT,etc properly but with no luck.

PD: Would it be possible to show also an average of the speed of the radar fines? If so, how would it be done? I imagine it would be necessary to project the Record.speed and in some group/project stage do this, but when?:

 avg : {"$avg" : "$_id.Record.speed"}

Thank you in advance!!

1 Like

Hi @ReyBinario,

It seems that the there is one document per radar? You use _id as the radar Id.

But in your data example the Record field is not an array, this is where I am confused.

I can help you if you clarify the exact document structure and and output example.

Best regards,
Pavel

1 Like

Hi @Pavel_Duchovny,

Yes, there is one document per radar (the real document has a lot of information, but I filtered it to simplify the example).
Each document has the object “radar” and the object “Record” , and in order to get only the month I used “split” in Record.date (String) and created an array. In this array, the month (string) is storaged in position “1”.
I hope I clarified the structure.

Anyway, here is an image of the data of this query:

db.trafico.find({},{"radar":1,"Record":1,_id:0}).pretty();

and the output (one doccument):

{
        "radar" : {
                "mileage" : 15,
                "direction" : "ascending",
                "speed limit" : 180
        },
        "Record" : {
                "date" : "18/04/2007",
                "time" : "12:33:36.260",
                "speed" : 193
        }
}

I hope I have solved your doubts.
If you need anything else please let me know.
Thank you very much for your time, best regards,
RB.

Hi @ReyBinario,

So if each radar has only one document why do you group and count them? It sounds like it will always be 1.

Or do you have a document for each recording? How do you identify a radar? By:

"radar" : {
                "mileage" : 15,
                "direction" : "ascending",
                "speed limit" : 180
        },

Thanks,
Pavel