我有一个这样的聚合查询:

db.yc_promotions.aggregate([
{$match:
    {
        city_from: {$in: ['x1','x2','x3']},
        city_to: {$in: ['y1','y2','y3']},
        date_ret: {$lte: "2013-06-30"},
        updated: {"$gte": "2013-02-01"}
    }
},
{$group:
    {
        _id: {city_to: "$city_to"},
        min: { $min: "$total_price" }
    }
}
])

查询响应是这样的形式:

{"result" : [
    {
        "_id" : {
            "city_to" : "y1"
        },
        "min" : 2404.72
    },
    {
        "_id" : {
            "city_to" : "y2"
        },
        "min" : 1619.2
    }
    ...
 ]}

但我想以最低价格退回元素，而不仅仅是值(value)和城市代码。我该怎么做？

谢谢!

最佳答案

看起来您应该能够使用类似以下内容添加其他字段(我忽略了匹配项):

db.test.aggregate(
    { $group: {
        _id: { city_to: "$city_to" },
        min: { $min: "$total_price"  },
        city_from: { $min: "$city_from" }
    } }
);

但这行不通。因为 $min 将处理每个 字段。举个例子:

> db.test.find();
{ "_id" : ObjectId("51156a11056d6f966f268f7f"), "city_from" : "LHR", "city_to" : "JFK", "total_price" : 500 }
{ "_id" : ObjectId("51156a1a056d6f966f268f80"), "city_from" : "LHR", "city_to" : "LGA", "total_price" : 400 }
{ "_id" : ObjectId("51156a1e056d6f966f268f81"), "city_from" : "DUB", "city_to" : "LGA", "total_price" : 400 }
{ "_id" : ObjectId("51156a27056d6f966f268f82"), "city_from" : "DUB", "city_to" : "JFK", "total_price" : 300 }
> db.test.aggregate( {$group: { _id: { city_to: "$city_to" }, min: { $min: "$total_price"  }, city_from: { $min: "$city_from" } } } );
{
    "result" : [
        {
            "_id" : {
                "city_to" : "LGA"
            },
            "min" : 400,
            "city_from" : "DUB"
        },
        {
            "_id" : {
                "city_to" : "JFK"
            },
            "min" : 300,
            "city_from" : "DUB"
        }
    ],
    "ok" : 1
}

city_from: LHR 完全消失了，尽管 LHR->LGA 和 DUB->LGA 的最低价格相同。 city_from: { $min: "$city_from"} 仅返回 [ DUB, LGA ] 的字符串最小值。您当然可以拥有多个具有相同最低价格的文档(在本例中，LHR->LGA 和 DUB->LGA 均为 400)。

您必须分两步完成此操作:

> result = db.test.aggregate( {$group: { _id: { city_to: "$city_to" }, min: { $min: "$total_price"  } } } );
> final = []
> result.result.forEach(function(entry) { final.push( { city_to: entry._id.city_to, min: entry.min, docs: db.test.aggregate( { $match: { city_to: entry._id.city_to, total_price: entry.min } } ).result } ) } );

然后给出结果:

> final
[
    {
        "city_to" : "LGA",
        "min" : 400,
        "docs" : [
            {
                "_id" : ObjectId("51156a1a056d6f966f268f80"),
                "city_from" : "LHR",
                "city_to" : "LGA",
                "total_price" : 400
            },
            {
                "_id" : ObjectId("51156a1e056d6f966f268f81"),
                "city_from" : "DUB",
                "city_to" : "LGA",
                "total_price" : 400
            }
        ]
    },
    {
        "city_to" : "JFK",
        "min" : 300,
        "docs" : [
            {
                "_id" : ObjectId("51156a27056d6f966f268f82"),
                "city_from" : "DUB",
                "city_to" : "JFK",
                "total_price" : 300
            }
        ]
    }
]

关于mongodb - 如何从 MongoDB 中的组聚合返回整个对象，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14772344/