Consider a scenario where there is a users collection. Each user document includes an array of posts called posts. The goal is to query this collection as follows:
Retrieve 2 posts, starting at index Nstart and ending at index Nend, from each user in the username array: ['user1', 'user2']. Merge these posts into a single array, ordered by the ascending value of post.postedOn integer.
If we look at the example data:
{
username: "user1",
posts: [
{ id: 1, text: "123" ... },
{ id: 2, text: "456" ... },
{ id: 3, text: "789" ... },
...
]
},
{
username: "user2",
posts: [
{ id: 1, text: "abc" ... },
{ id: 2, text: "def" ... },
{ id: 3, text: "ghi" ... },
...
]
}
...
The desired output would be:
{
posts: {
{ id: 1, text: "123" ... },
{ id: 2, text: "456" ... },
{ id: 1, text: "abc" ... },
{ id: 2, text: "def" ... },
}
}
Attempts have been made with multiple uses of the aggregate function. One of the queries tried was:
db.getCollection("users").aggregate([{ $match: { username: { $in: ["admin", "admin2"] } } }, { $project: { posts: 1 } }])
However, this returns two user objects, each containing all of their posts instead of a single posts array that can be manipulated further.
The ultimate goal is to achieve the following:
Inputs
- users:
['user1', 'user2'] - starting posts index:
4 - ending posts index:
8
Result
An array of posts from selected users, totaling 8 (IDs 4-8 from each user).