Mongodb aggregation-accumulate

问题:

I have collection PurchaseOrder and nested array PurchaseOrderLines, in the PurchaseOrderLines I have two fields ReceivingQty and SendingQty.

I want to calculate inventory trending(Group => ProductID, Date, TotalReceivingQty-TotalSendingQty) Result should be something like this:

ProductID | TotalQty | SeqTotal | Date

1         | 5        | 5        | 01-01

1         | -2       | (5-2)=3  | 01-02

1         | 10       | (3+10)=13| 01-03

1         | -5       | (13-5)=8 | 01-04

I've done this:

db.PurchaseOrder.aggregate(
  [       
    { "$unwind":"$PurchaseOrderLines"},
    {
      "$group": {
        "_id": {
          "Warehouse": "$Warehouse",
          "PurchaseOrderLines_ProductID": "$PurchaseOrderLines.ProductID",
          "WarehouseTypeID": "$WarehouseTypeID"
          ,"NowDate": "$NowDate"
        },
        "TotalReceivingQty":{"$sum":"$PurchaseOrderLines.ReceivingQty"},
        "TotalSendingQty":{"$sum":"$PurchaseOrderLines.SendingQty"},
        "GProductID":{"$first":"$PurchaseOrderLines.ProductID"}
      }
    },
    {
        "$lookup": {
               from: "Product",
               localField: "GProductID",
               foreignField: "ProductId",
               as: "product_doc_list"
             }
    },
    {
      "$project": {
        "ProductID": "$_id.PurchaseOrderLines_ProductID",
        "Product": {
            $map: {
              input: "$product_doc_list",
              as: "product_doc",
              in: "$$product_doc"
            }
        },
        "TotalQty": {"$subtract": ["$TotalReceivingQty", "$TotalSendingQty"]},
        "WarehouseTypeID": "$_id.WarehouseTypeID",
        "Warehouse": "$_id.Warehouse",
        "TotalReceivingQty":"$TotalReceivingQty",
        "TotalSendingQty":"$TotalSendingQty",
        "Date":"$_id.NowDate"
      }
    }
],{ allowDiskUse: true });

This answer used $lookup to self join. but I can't figure out how it works with arrays.

Samples:

/* 1 */
{   
    "ProductID" : NumberDecimal("26.101"),
    "TotalQty" : NumberDecimal("52.000000"),
    "TotalReceivingQty" : NumberDecimal("52.000000"),
    "TotalSendingQty" : 0,
    /* need this property */
    "SeqTotal":sum(prev(SeqTotal),TotalQty)=NumberDecimal("52.000000"),
    "Date" : "01-01"
},

/* 2 */
{
    "ProductID" : NumberDecimal("26.101"),
    "TotalQty" : NumberDecimal("-1.000000"),
    "TotalReceivingQty" : 0,
    "TotalSendingQty" : NumberDecimal("1.000000"),
    /* need this property */
    "SeqTotal":sum(prev(SeqTotal),TotalQty)=52-1=>51,
    "Date" : "01-03"
},

/* 3 */
{
    "ProductID" : NumberDecimal("26.101"),
    "TotalQty" : NumberDecimal("1.000000"),
    "TotalReceivingQty" : NumberDecimal("1.000000"),
    "TotalSendingQty" : 0,
    /* need this property */
    "SeqTotal":sum(prev(SeqTotal),TotalQty)=51+1=>52,
    "Date" : "01-04  "
},

/* 4 */
{
    "ProductID" : NumberDecimal("26.101"),
    "TotalQty" : NumberDecimal("92.000000"),
    "TotalReceivingQty" : NumberDecimal("92.000000"),
    "TotalSendingQty" : 0,
    /* need this property */
    "SeqTotal":sum(prev(SeqTotal),TotalQty)=52+92=>144,
    "Date" : "01-17"
},

/* 5 */
{
    "ProductID" : NumberDecimal("26.101"),
    "TotalQty" : NumberDecimal("-96.000000"),
    "TotalReceivingQty" : 0,
    "TotalSendingQty" : NumberDecimal("96.000000"),
    /* need this property */
    "SeqTotal":sum(prev(SeqTotal),TotalQty)=144-96=>48,
    "Date" : "01-20"
}

回答1:

OK! Thank to @Veeram for his comment, I ended up with this solution.

db.PurchaseOrder.aggregate(
    [    
        { '$unwind': '$PurchaseOrderLines'},
        {
            '$group': {
                    _id: {
                        'wh': '$Warehouse',
                        'pid': '$PurchaseOrderLines.ProductID',
                        'wht': '$WarehouseTypeID',
                        'dt': '$NowDate'
                    },
                    'trq':{'$sum': {$ifNull: [ '$PurchaseOrderLines.ReceivingQty', 0 ] }},
                    'tsq':{'$sum': {$ifNull: [ '$PurchaseOrderLines.SendingQty', 0 ] }}
            }
        },
        {
            '$sort': {
                 '_id.dt': 1
            }
        },
        {
            '$group': {
                '_id': {pid:'$_id.pid'},
                '_gp': { '$push': '$_id' },
                'totals': { '$push': {'$subtract': ['$trq', '$tsq']} }
            }
        },
        {
            '$unwind': {
                'path' : '$_gp',
                'includeArrayIndex' : 'index'
            }
        },
        {
        '$lookup': {
               from: 'Product',
               localField: '_id.pid',
               foreignField: 'ProductId',
               as: 'product_doc_list'
             }
        },
        {
            '$project': {
                '_id': 0,
                'Product': {
                    $map: {
                      input: '$product_doc_list',
                      as: 'product_doc',
                      in: '$$product_doc'
                    }
                 },
                 'WarehouseTypeID': '$_gp.wht',
                 'Warehouse': '$_gp.wh',
                 'TotalQty': { '$arrayElemAt': [ '$totals', '$index' ] },
                 'SeqTotal':{ '$sum': { '$slice': [ '$totals', { '$add': [ '$index', 1 ] } ] } },
                 'Date':'$_gp.dt'
            }
        }
    ],{allowDiskUse:true}
);