We have an array with a list of id chats - [1, 2]

Task: from the collection of messages, pull out one last message in each chat.

A collection of messages is shown below.

created_at - timestamp time

 [ { chat_id: 1, text: '123', created_at: 1 }, { chat_id: 1, text: '123', created_at: 2 }, { chat_id: 1, text: '123', created_at: 3 }, { chat_id: 2, text: 'asd123', created_at: 1 }, { chat_id: 2, text: 'asd123', created_at: 3 }, { chat_id: 5, text: '123312', created_at: 1 } ]

The request below is not correct, I wrote it so that the essence was clear.

 db.message.find({chat_id: {$in: [1, 2]}}, {}, { sort: {created_at: -1}, limit: 1 });

This should be the result:

 [ { chat_id: 1, text: '123', created_at: 3 }, { chat_id: 2, text: 'asd123', created_at: 3 } ]

Please write the correct request so that I know how to do it.

And what will happen if I have 10,000,000 entries in the messages collection?

    1 answer 1

    You need an aggregation framework and the $last or $first . Operator;

    • need to filter documents $match operator helps us to do it

    • sort documents with timestamp in descending order using $sort operator

    • group documents using the $group statement and return the last document of each group using $last

       db.message.aggregate([ { "$match": { "chat_id": { "$in": [ 1, 2 ] }}}, { "$sort": { "created_at": 1 }}, { "$group": { "_id": "$chat_id", "text": { "$last": "$text" }, "chat_id": { "$last": "$chat_id" }, "created_at": { "$last": "$created_at" } } } ])

    result:

     { "_id" : 2, "text" : "asd123", "chat_id" : 2, "created_at" : 3 } { "_id" : 1, "text" : "123", "chat_id" : 1, "created_at" : 3 }

    What will happen if I have 10,000,000 entries in the messages collection?

    this will work but you can create an index in created_id to improve performance.