Mongodb query $agregate $group $count and other friends

Question

I have currently this kind of records in my mongo base :

/* 1 */
{
    "_id" : ObjectId("5bc746282c9bf51af0ff8afb"),
    "horodate" : ISODate("2018-10-17T14:24:38.975Z"),
    "id_track" : 835,
    "type_cmd" : "F"
}

/* 2 */
{
    "_id" : ObjectId("5bc746282c9bf51af0ff8afb"),
    "horodate" : ISODate("2018-10-17T14:24:40.928Z"),
    "id_track" : 853,
    "type_cmd" : "R"
}

I'm looking for a mean with a mongo request to obtain an answer to this litteral request.

"type_cmd" can be a A, R, P, C, F

For the period of yesterday 3.am to today 3.am, for each "id_track",count how many records, and count how many "type_cmd" for each type of "type_cmd".

To obtain a result like this :

id_track     Nb_records    Type A     Type_R      Type_P     etc...
853          652           52         54          25         XX
842          52            6          7           15         XX
35           25            12         5           2          XX

Many thanks to help me, it's the first time I work with this kind of database.

I'm currently learn how to do request from mongoshell, but it's very very different of mysql.

Answer 1

Many many thanks for your help buthere is the result, only one record and no trace of id_track ?

/* 1 */ {
    "_id" : "A",
    "type_cmd_count" : 92.0 }

/* 2 */ {
    "_id" : "F",
    "type_cmd_count" : 92.0 }

/* 3 */ {
    "_id" : "R",
    "type_cmd_count" : 91.0 }

/* 4 */ {
    "_id" : "P",
    "type_cmd_count" : 92.0 }

/* 5 */ {
    "_id" : "C",
    "type_cmd_count" : 92.0 }

Answer 2

I hope this points you in the right direction. Here is an aggregation that I believe gets you pretty close. You would obviously replace the dates with the yesterday at 3am and today at 3am.

The whole pipeline:

db.getCollection("test").aggregate(

    // Pipeline
    [
        // Find documents between yesterday @ 3am and today @ 3am
        {
            $match: {
            $and: [ { "horodate": { $gte: ISODate("2018-10-17T03:00:00.000+0000") } }, { "horodate": { $lte: ISODate("2018-10-18T03:00:00.000+0000") } } ] }
        },

        // Group documents by id_track and add type_cmd to array
        {
            $group: {
            _id: '$id_track',
            type_cmd: {$addToSet: "$type_cmd"}
            }
        },

        // Deconstruct the type_cmd array
        {
            $unwind: "$type_cmd"
        },

        // group by type_cmd and count the number of documents
        {
            $group: {
             _id: "$type_cmd", 
             type_cmd_count: { $sum:1} 
             }
        },

    ]

);

Here are the results at each stage to hopefully help visualise what is happening.

Stage 2 - Group documents by id_track

{ 
    "_id" : 853.0, 
    "type_cmd" : [
        "R"
    ]
}
{ 
    "_id" : 835.0, 
    "type_cmd" : [
        "F"
    ]
}

Stage 3 - Deconstruct the type_cmd array

{ 
    "_id" : 853.0, 
    "type_cmd" : "R"
}
{ 
    "_id" : 835.0, 
    "type_cmd" : "F"
}

Stage 3 - Count

{ 
    "_id" : "F", 
    "type_cmd_count" : 1.0
}
{ 
    "_id" : "R", 
    "type_cmd_count" : 1.0
}