Get count of latest record statuses

[Nick_Sam (Nikita Samko)]

Hi everyone!

I have a collection of message logs like (I’ve excluded _id from example, but it presents):

[
    {
        "message_id": 3,
        "message_status": 2,
        "created_at": "2023-02-06T22:15:08.696+00:00"
    },
    {
        "message_id": 3,
        "message_status": 1,
        "created_at": "2023-02-05T22:28:08.696+00:00"
    },
    {
        "message_id": 2,
        "message_status": 1,
        "created_at": "2023-02-06T22:15:08.696+00:00"
    },
    {
        "message_id": 1,
        "message_status": 2,
        "created_at": "2023-02-06T22:15:08.696+00:00"
    },
    {
        "message_id": 1,
        "message_status": 1,
        "created_at": "2023-02-05T22:28:08.696+00:00"
    }
]

For this collection I wanted to get message count for each latest message_status (only with latest created_at)
F.e.:

[
    {
        "message_status": 1 
        "count" : 1 # message_id: 2 has lastest status 1
    },
    {

        "message_status": 2
        "count": 2 # message_id: 3 and 1 have lastest status 2
    }
]

I tried to use $group by message_id to get latest status, how can I sum these statuses for all collection?

{ $group: { _id: "$message_id", latest_status: {$last: "$status_id"}}}

Thanks!

[steevej (Steeve Juneau)]

You were going in the right direction with your $group.

For $last and $first to have a meaning you however need to $sort first , otherwise the order is not defined.
Note that $sort descending and use $first rather than ascending and $last. My gut feeling is that it is potentially more efficient, but I really do not know.

sort = { "$sort" : { "created_at" : -1 } }

Then the $group by message id:

group = { "$group" : {
    "_id" : "$message_id" ,
    "status" : { "$first" : "$message_status" }
} }

Then we $count the statuses with

count = { "$group" : {
    "_id" : "$status" ,
    "count" : { "$sum" : 1 }
} }

Personally, I would stop here because I have the requested information. But to get the format you wish the following cosmetic $project can be used.

cosmetic = { "$project" : {
    "message_status" : "$_id" ,
    "count" : 1 ,
    "_id" : 0
} }

The pipeline would then be:

pipeline = [ sort , group , count , cosmetic ]

[Nick_Sam (Nikita Samko)]

Thank you very much!