Recent chat messages?

Question

MongoDB

We have an array with a list of chat ids - [1, 2]
Task: to extract one last message in each chat from the messages collection.
The message collection 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?

Answer 1

If the load is large, then you can make a cache collection where only the latest messages in the context of the chat will be stored.
If the load is small, then you can choose one at a time.