MongoDB Aggregate - 查询以获取组中的最新项目 [英] MongoDB Aggregate - Query to get most recent item in group

问题描述

鉴于此源数据:

{ "_id" : ObjectId("1"), "productID" : 1, "amount" : 1, "date" : ISODate("2017-02-01T00:00:00Z") }
{ "_id" : ObjectId("2"), "productID" : 2, "amount" : 2, "date" : ISODate("2017-02-01T00:00:00Z") }
{ "_id" : ObjectId("3"), "productID" : 3, "amount" : 3, "date" : ISODate("2017-02-01T00:00:00Z") }
{ "_id" : ObjectId("4"), "productID" : 4, "amount" : 4, "date" : ISODate("2017-02-01T00:00:00Z") }
{ "_id" : ObjectId("5"), "productID" : 1, "amount" : 11, "date" : ISODate("2017-02-02T00:00:00Z") }
{ "_id" : ObjectId("6"), "productID" : 2, "amount" : 22, "date" : ISODate("2017-02-02T00:00:00Z") }
{ "_id" : ObjectId("7"), "productID" : 2, "amount" : 222, "date" : ISODate("2017-02-03T00:00:00Z") }
{ "_id" : ObjectId("8"), "productID" : 3, "amount" : 33, "date" : ISODate("2017-02-03T00:00:00Z") }

我想获取每个产品的最新记录(键入 productID)并打印出按 productID 排序的每个产品的该记录的完整行.所以我想要的上面的输出是:

I want to get the most recent record for each product (keyed on productID) and print out the full row for that record per product sorted on productID. So my desired output from above would be:

{ "_id" : ObjectId("5"), "productID" : 1, "amount" : 11, "date" : ISODate("2017-02-02T00:00:00Z") }
{ "_id" : ObjectId("7"), "productID" : 2, "amount" : 222, "date" : ISODate("2017-02-03T00:00:00Z") }
{ "_id" : ObjectId("8"), "productID" : 3, "amount" : 33, "date" : ISODate("2017-02-03T00:00:00Z") }
{ "_id" : ObjectId("4"), "productID" : 4, "amount" : 4, "date" : ISODate("2017-02-01T00:00:00Z") }

我是 Mongo 的新手，遇到问题，设法解决了大部分问题，但无法删除每个产品的其他旧记录.

I'm new to Mongo and having problems, managed to get most of it but couldn't delete the other older records for each product.

推荐答案

你可以使用 $last 和 $first 运算符.请注意，您应该在分组之前对文档进行排序:

You can use $last and $first operators. Note that you should sort documents before grouping:

db.so.aggregate([
 { $sort: {productID:-1, date: -1} },
 { 
     $group: {
         _id : "$productID",
         date: {$last: "$date" },
         amount: {$first: "$amount" },
         id : {$first: "$_id"}
     }
 },
 { $project: { _id: "$id", productId: "$_id", date: 1, amount: 1 } }
])

输出:

{
    "_id" : 5,
    "productId" : 1,
    "date" : ISODate("2017-02-01T00:00:00.000Z"),
    "amount" : 11
},
{
    "_id" : 7,
    "productId" : 2,
    "date" : ISODate("2017-02-01T00:00:00.000Z"),
    "amount" : 222
},
{
    "_id" : 8,
    "productId" : 3,
    "date" : ISODate("2017-02-01T00:00:00.000Z"),
    "amount" : 33
},
{
    "_id" : 4,
    "productId" : 4,
    "date" : ISODate("2017-02-01T00:00:00.000Z"),
    "amount" : 4
}

这篇关于MongoDB Aggregate - 查询以获取组中的最新项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！