如何使用匹配,排序和限制来订购MongoDB聚合 [英] How to order MongoDB Aggregation with match, sort, and limit
问题描述
我当前的汇总是:
db.group_members.aggregate({
$match: { user_id: { $in: [1,2,3] } }
}, {
$group: { _id: "$group_id" }
}, {
$sort: { last_post_at: -1 }
}, {
$limit: 5
})
对于以下文档结构:
{
_id: '...',
user_id: '...',
group_id: '...',
last_post_at: Date,
}
我也在{user_id: 1, last_post_at: -1}
由于我的索引已经在last_post_at
上,因此排序没有用吗?我不确定100%如何订购.
Since my index is already on last_post_at
is the sort useless? I'm not 100% sure how the ordering of this.
我的最终目标是复制此SQL:
My end goal is to replicate this SQL:
SELECT DISTINCT ON (group_id)
FROM group_members
WHERE user_id in [1,2,3]
ORDER_BY last_post_at DESC
LIMIT 5
我想知道如何使它对于非常大的group_members表现出色,并且仍然以正确的顺序返回它.
I'm wondering how to make it performant for a very large group_members and still return it in the right order.
更新: 我希望找到一种解决方案,以限制加载到内存中的文档数量.这将是一个相当大的集合,并且访问非常频繁.
UPDATE: I'm hoping to find a solution that will limit the number of documents loaded into memory. This will be a fairly large collection and accessed very frequently.
推荐答案
将$ sort放在$ group之前,否则MongoDB无法使用索引来帮助进行排序.
Put the $sort before the $group, otherwise MongoDB can't use the index to help with sorting.
但是,与组group_members集合的总大小相比,您看起来要查询的user_id数量相对较少.因此,我建议仅在user_id上建立索引.在这种情况下,MongoDB必须按last_post_at对您的结果进行排序,但这值得以使用user_id进行初始查找的索引作为交换.
However, in your query it looks like you want to query for a relatively small number of user_ids compared to the total size of your group_members collection. So I recommend an index on user_id only. In that case MongoDB will have to sort your results in memory by last_post_at, but this is worthwhile in exchange for using an index for the initial lookup by user_id.
这篇关于如何使用匹配,排序和限制来订购MongoDB聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!