我有类似的文档存储在mongodb
{
"_id":"transaction_id"
"customer":"some customer",
"order_date":Date('2011-01-01'),
"delivery_date":Date('2011-01-15'),
"amt":500.0,
"qty":50
},
{
"_id":"transaction_id"
"customer":"some customer",
"order_date":Date('2011-01-01'),
"delivery_date":Date('2011-02-04'),
"amt":500.0,
"qty":50
}
我希望对订单日期和交货日期做一些汇总，以绘制每月订购和交付给每个客户的库存总量。
授予我可以运行2个聚合查询来获得我想要的，但我只是想知道是否有可能获得具有2组1命令的结果?
预期结果如下:
results:[{
_id:{
customer:"some customer"
},
orders:[
{
year:2011,
month:1,
qty:100
},
...
]
deliveries:[
{
year:2011,
month:1,
qty:50
},
{
year:2011,
month:2,
qty:50
},
...
]
},...]
您可以在单个查询中做到这一点，您只需要在操作文档方面有点创意，然后基本上执行两个 $group 阶段，首先按日期添加，然后按客户添加。
首先对于当前的MongoDB 2.6及以上版本，由于使用了一些操作符:
db.transactions.aggregate([
// Project an additional array, stands for "order", "delivery"
{ "$project": {
"_id": 0,
"customer": 1,
"order_date": 1,
"delivery_date": 1,
"qty": 1,
"type": { "$literal": ["o","d"] }
}},
// Unwind that array, creates two documents by "type"
{ "$unwind": "$type" },
// Group by "customer", "type" and date
{ "$group": {
"_id": {
"customer": "$customer",
"type": "$type",
"year": {
"$year": {
"$cond": [
{ "$eq": [ "$type", "o" ] },
"$order_date",
"$delivery_date"
]
}
},
"month": {
"$month": {
"$cond": [
{ "$eq": [ "$type", "o" ] },
"$order_date",
"$delivery_date"
]
}
}
},
"qty": { "$sum": "$qty" }
}},
// Group on the "customer" selecting which array to add to
{ "$group": {
"_id": "$_id.customer",
"orders": {
"$push": {
"$cond": [
{ "$eq": [ "$_id.type", "o" ] },
{
"year": "$_id.year",
"month": "$_id.month",
"qty": "$qty"
},
false
]
}
},
"deliveries": {
"$push": {
"$cond": [
{ "$eq": [ "$_id.type", "d" ] },
{
"year": "$_id.year",
"month": "$_id.month",
"qty": "$qty"
},
false
]
}
}
}},
// Getting rid of the `false` values in there
{ "$project": {
"orders": { "$setDifference": [ "$orders", [false] ] },
"deliveries": { "$setDifference": [ "$deliveries", [false] ] },
}},
// But "sets" are not considered ordered, so sort them
{ "$unwind": "$orders" },
{ "$sort": { "orders.year": 1, "orders.month": 1 } },
{ "$group": {
"_id": "$_id",
"orders": { "$push": "$orders" },
"deliveries": { "$first": "$deliveries" }
}},
{ "$unwind": "$deliveries" },
{ "$sort": { "deliveries.year": 1, "deliveries.month": 1 } },
{ "$group": {
"_id": "$_id",
"orders": { "$first": "$orders" },
"deliveries": { "$push": "$deliveries" }
}}
)
只是在2.6之前的版本中做得有点不同:
db.transactions.aggregate([
// Project an additional array, stands for "order", "delivery"
{ "$project": {
"_id": 0,
"customer": 1,
"order_date": 1,
"delivery_date": 1,
"qty": 1,
"type": { "$cond": [ 1, ["o","d"], 0 ] }
}},
// Unwind that array, creates two documents by "type"
{ "$unwind": "$type" },
// Group by "customer", "type" and date
{ "$group": {
"_id": {
"customer": "$customer",
"type": "$type",
"year": {
"$year": {
"$cond": [
{ "$eq": [ "$type", "o" ] },
"$order_date",
"$delivery_date"
]
}
},
"month": {
"$month": {
"$cond": [
{ "$eq": [ "$type", "o" ] },
"$order_date",
"$delivery_date"
]
}
}
},
"qty": { "$sum": "$qty" }
}},
// Group on the "customer" selecting which array to add to
{ "$group": {
"_id": "$_id.customer",
"orders": {
"$push": {
"$cond": [
{ "$eq": [ "$_id.type", "o" ] },
{
"year": "$_id.year",
"month": "$_id.month",
"qty": "$qty"
},
false
]
}
},
"deliveries": {
"$push": {
"$cond": [
{ "$eq": [ "$_id.type", "d" ] },
{
"year": "$_id.year",
"month": "$_id.month",
"qty": "$qty"
},
false
]
}
}
}},
// Filter `false` and sort on date
{ "$unwind": "$orders" },
{ "$match": { "orders": { "$ne": false } } },
{ "$sort": { "orders.year": 1, "orders.month": 1 } },
{ "$group": {
"_id": "$_id",
"orders": { "$push": "$orders" },
"deliveries": { "$first": "$deliveries" }
}},
{ "$unwind": "$deliveries" },
{ "$match": { "deliveries": { "$ne": false } } },
{ "$sort": { "deliveries.year": 1, "deliveries.month": 1 } },
{ "$group": {
"_id": "$_id",
"orders": { "$first": "$orders" },
"deliveries": { "$push": "$deliveries" }
}}
])
由于结果是每个客户的"订单"one_answers"交付"数组，因此您可以有条件地 $push 到该数组，或者是文档值或false，这取决于文档当前的"类型"是每个数组。
最后，由于这些数组现在包含false的值以及所需的文档，因此您可以过滤掉这些值，并确保您的数组在正确的"日期"顺序中，如果您确实需要的话。
是的，清单有两个以上的 $group 阶段，繁重的工作实际上是在两个组中完成的，其他的只是在那里进行数组操作，如果您需要的话，但是它会给您精确和有序的结果。
因此，这可能不是您可能想到的第一种方法，但它展示了一些有趣的转换思想，您可以使用各种聚合操作符来解决问题。
我正在经历一个类似的问题，其中我需要得到我的结果在多个组分类，看着所有这些答案让我的头旋转。经过一番研究，我终于找到了我要找的东西。
MongoDB在3.4版本中引入了一个新的命令$facet，这使得在一个命令中包含多个组非常容易。看看他们的文档:美元方面(聚合)
我本打算在这里用文字解释的，但我认为他们的文档写得更清楚，写得更漂亮，有很好的例子。
希望能有所帮助。
如果我没理解错的话:
db.collName.aggregate({$project:{
customer:1,
order:{
qty:"$qty",
year:{$year:"$order_date"},
month:{$month:"$order_date"}
},
delivery:{
qty:"$qty",
year:{$year:"$delivery_date"},
month:{$month:"$delivery_date"}
}
}
},
{$group:{
_id:{
customer:"$customer"
},
orders: { $push:"$order" },
deliveries:{ $push:"$delivery"}
}
});