如何使用匹配，排序和限制来订购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屋！