How can I count and return the most repeated attribute in MongoDB?

[ReyBinario]

Hello everyone,

I’m learning MongoDB and I’m practicing with a traffic database, and I’d like to know how to get for each radar the time of the day (“Record.time”) when the most speeding tickets are recorded (the most repeated hour for each radar, something like radar X (with its attributes) and its “happy hour” )

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"
}

The closest I’ve ever come is to find out the latest hour that each radar has fined with this query:

db.traffic.aggregate( 
   [
     {
       $group:
         {
           _id: "$radar",
           poppularHour: { $max: "$Record.time" }
         }
     }
   ]
)

Also I found all the occurrences of all the hours with:

db.traffic.aggregate( [ { $unwind: "$radar" },{ $sortByCount: "$Record.time" }   ] )

or the most repeated hour with it’s occurences with:

db.trafico.aggregate([
    { "$unwind": "$radar" },
    {
        "$group": {
            "_id": "$Record.time",
            "count": { "$sum": 1 }
        }
    },
    { "$sort": { "count": -1 } },
    { "$limit": 1 } 
])

but as I said, I’m looking for each radar his most repeated hour, and even if I try everything I can’t get it.

Could please someone help me?

Thank you in advance!!

PD: When I say the most repeated hour I mean the same string Record.time (“HH:MM:SS:FFF”)

[Pavel_Duchovny]

Hi @ReyBinario,

Welcome to MongoDB community!

If you need the time per radar why not to group per radar and per time in a compound expression:

db.trafico.aggregate([
    { "$unwind": "$radar" },
    {
        "$group": {
            "_id": {"radar": "$radar", "time" : "$Record.time"},
            "count": { "$sum": 1 }
        }
    },
    { "$sort": { "_id.radar" :1, "count": -1 } },
    "$group": {
            "_id": {"radar": "$_id.radar", "count" : "$count"},
            topTimePerRadar: { $first: "$$ROOT"}
        }
    }
])

Haven’t tested the query but the idea should work.

Thanks
Pavel

[ReyBinario]

Hi @Pavel_Duchovny,

Thank you so much for your answer!! Your query works perfect.
Also I have learned new operators like “first” or “root” that I didn’t know, so thanks again for your time.

In order to improve the thread of the conversation, I would like to show you (and to everyone) the latest version I was working on in case someone else gets into this thread with doubts and helps to solve them.

*There is a small bug that keeps repeating radars even though in the last filter-stage the “count” should be equal to the maxQuantity, if you could solve the bug I would really appreciate.

db.trafico.aggregate([{
    $project: {
        radar: {
            mileage: '$radar.mileage',
            direction: '$radar.direction',
            'speed limit': '$radar.speed limit'
        },
        Record: {
            date: '$Record.date',
            time: '$Record.time',
            speed: '$Record.speed'
        }
    }
}, {
    $group: {
        _id: {
            mileage: '$radar.mileage',
            direction: '$radar.direction',
            'speed limit': '$radar.speed limit',
            hora: '$Record.time'
        },
        total: {
            $sum: 1
        }
    }
}, {
    $project: {
        _id: {
            mileage: '$_id.mileage',
            direction: '$_id.direction',
            'speed limit': '$_id.speed limit'
        },
        hora: '$_id.hora',
        total: '$total',
        maxQuantity: {
            $max: '$total'
        }
    }
}, {
    $project: {
        _id: {
            mileage: '$_id.mileage',
            direction: '$_id.direction',
            'speed limit': '$_id.speed limit'
        },
        hora: '$_id.hora',
        total: '$total',
        maxQuantity: '$maxQuantity',
        maximum: {
            $eq: ['$total', '$maxQuantity']
        }
    }
}, {
    $match: {
        maximum: true
    }
}
]);

Thanks in advance,
RB.