mongodb 多个组在一个结果中

Question

我在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 个聚合查询来获得我想要的结果，但我只是想知道是否有可能通过 1 个命令获得包含 2 组组的结果？

预期结果如下:

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
  },
  ...
 ]
},...]

Answer 1

您可以在单个查询中执行此操作，您只需要在处理文档方面有点创意，然后基本上执行两个 $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" }
    }}

])

基本上总结这里的方法，你正在做的是复制每个文档并分配一个代表“订单”或“交付”的“类型”。然后，当您按“客户”、“日期”和“类型”分组时，您可以根据当前类型有条件地决定选择哪个“日期”，并只需对该键下的“数量”求和即可。

由于结果是每个客户的“订单”和“交付”数组，因此您有条件地 $push 到该数组，文档值或 false 取决于每个数组当前文档的“类型”。

最后，由于这些数组现在包含 false 的值以及所需的文档，您可以过滤掉这些值并确保您的数组处于正确的“日期”顺序(如果您确实需要)。

是的，列表中有两个以上 $group阶段，繁重的工作实际上是在两个分组中完成的，如果您需要，其他分组只是用于数组操作，但它会为您提供准确有序的结果。

所以这可能不是您想到的第一种方法，但展示了一些有趣的转换想法，您可以将其用于各种 aggregation operators为了解决问题。这是做什么的:)