如何在mongodb中获取每个组的最新N条记录?

问题描述

每个城市我都有几个条目.如何获得每个城市的最新3个条目?喜欢:

I have a couple of entries for each cities. How to get lastest 3 entries of each city? Like:

City1
记录1
记录2
记录3

City2
记录1
记录2
记录3

City3
记录1
记录2
记录3

City1
record 1
record 2
record 3

City2
record 1
record 2
record 3

City3
record 1
record 2
record 3

架构:

var schema = mongoose.Schema({
  city: {type: String},
  title: {type: String},
  created: {type: Number, default: Math.floor(new Date() / 1000)}
})

我尝试过的代码:

Collection
.find('location $in ' + cities)
.aggregate({$group: {location: "$location"}})
.sort('created: 1')
.limit(3).exec(function(err, docs) { res.render('index', { hash: docs }); });

因此，如何执行查询结果:每个城市的3个最新标题

So, how to perform a query where I should have as result: 3 most recent titles of each city

推荐答案

在 mongoDB 3.2 中，您可以使用以下形式的汇总查询来执行此操作:

In mongoDB 3.2 you can perform this by using the aggregate query of following form:

db.collection.aggregate(
  {$sort: {created: -1}},
  {$group: {_id:'$city', title:{$push: '$title'}},
  {$project: {_id:0, city: '$_id', mostRecentTitle: {$slice: ['$title', 0, 2]}}}
)

使用mongoDB 3.0很难做到这一点.有一个非常肮脏的技巧可以在3.0中实现.它涉及几个步骤和其他集合.

Its very difficult to achieve the same using mongoDB 3.0. A very dirty trick is there to achieve this in 3.0. It involves a couple of steps and other collection.

首先进行汇总，然后将结果输出到名为"aggr_out"的临时集合中

First do an aggregate and output the result to a temporary collection called 'aggr_out'

查询:

db.collection.aggregate([
  {$sort: {created: -1}},
  {$group: {_id:'$city', title:{$push: '$title'}},
  {$project: {city: '$_id', mostRecentTitle: '$title'}},
  {$out: 'aggr_out'}]
)

使用上面的查询，mostRecentTitle将具有从索引0、1、2，...排序的所有最近标题.如果对此结果感到满意，因为您已经在mostRecentTitle的索引0、1和2中得到了结果.其他标题可以在应用程序端简单地忽略.

Using above query, mostRecentTitle will have all the recent titles ordered from index 0, 1, 2, ... If you are happy with this result using this as you already have the result in indexes 0,1 and 2 of mostRecentTitle. Other titles can be simple ignored in application side.

如果您仍然不满意，请对输出集合'aggr_out'进行更新，并从该集合中读取数据.查询是

Still if you are not happy, do an update on the output collection 'aggr_out', and read data from this collection. The query is,

db.aggr_out.update(
  {},
  {$push: {
    mostRecentTitle: {$each:[], $slice:3}
    }
  }
)

以上操作将对mostRecentTitle数组进行切片，使其具有最新的三个标题.阅读此收藏以获得所需的结果.

The above operation will slice the mostRecentTitle array so that it has most recent three titles. Do a read on this collection to get your desired result.

这篇关于如何在mongodb中获取每个组的最新N条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！