如何在mongoDB中使用聚合获得此结果 [英] How to get this result with aggregate in mongoDB

问题描述

我有发票收据...

{
"_id" : 1,
"items" : [ 
    {
        "_id" : 1,
        "value" : 100,
        "price" : 9500
    }, 
    {
        "_id" : 2,
        "value" : 200,
        "price" : 9500
    }
],
"costs" : [ 
    {
        "_id" : 1,
        "price" : 100
    }, 
    {
        "_id" : 2,
        "price" : 150
    }, 
    {
        "_id" : 3,
        "price" : 250
    }
]}

我会获得总计的发票金额...

I get Invoice amount with aggregate...

换句话说=>

{$ sum:{$ multiply:{[$ items.value，$ items.price]}}}--{$ sum:"$ costs.price"};

{$sum : {$multiply :{[$items.value, $items.price]} }} - {$sum : "$costs.price"};

{
  "_id" : 1,
  "amount" : 2849500
}

非常认为...

;-)

推荐答案

Mongo 3.4版本

$ map，将商品的价格&价值和$ reduce来计算商品价格和价值和$ reduce来计算成本价格的总和.减去之前的$减去值即可得出最终金额.

$map to multiply the item's price & value and $reduce to calculate the sum of item's price & value and $reduce to calculate the sum of cost's price. $subtract values from earlier reduce to get the final amount.

aggregate([{
    $project: {
        _id: 1,
        amount: {
            $subtract: [{
                $reduce: {
                    input: {
                        $map: {
                            input: "$items",
                            as: "item",
                            in: {
                                $multiply: ["$$item.value", "$$item.price"]
                            }
                        }
                    },
                    initialValue: 0,
                    in: {
                        $add: ["$$value", "$$this"]
                    }
                }
            }, {
                $reduce: {
                    input: "$costs.price",
                    initialValue: 0,
                    in: {
                        $add: ["$$value", "$$this"]
                    }
                }
            }]
        }

    }
}])

Mongo 3.x版本

第一个$ project乘以项目的值&价格.接下来进行分组以计算项目和成本字段的总和，这将导致每个项目和成本字段具有一个数组值，最终项目仅使用$ arrayElemAt来查看两个数组中的数组值，以将它们彼此相减.

First $project to multiply item's value & price. Next grouping to calculate the sum for both items and costs fields, which will result in one array value for each item and cost field and final project looks at only array value from both arrays with $arrayElemAt to subtract the values from each other.

aggregate(
    [{
        $project: {
            vpItems: {
                $map: {
                    input: "$items",
                    as: "item",
                    in: {
                        $multiply: ["$$item.value", "$$item.price"]
                    }
                }
            },
            costs: '$costs'
        }
    }, {
        $group: {
            _id: '$_id',
            vpItems: {
                $addToSet: {
                    $sum: '$vpItems'
                }
            },
            pCosts: {
                $addToSet: {
                    $sum: '$costs.price'
                }
            }
        }
    }, {
        $project: {
            _id: 1,
            amount: {
                $subtract: [{
                    $arrayElemAt: ["$vpItems", 0]
                }, {
                    $arrayElemAt: ["$pCosts", 0]
                }]
            }
        }
    }])

Mongo 2.6版本

$ unwinding items and group to calcualte计算值的总和，乘以该项目的价格&价值和$ unwind成本来计算商品价格和值，并从先前的分组中减去$的值，以计算最终金额.

$unwind items and group to calcualte sum of values returned from multiply the item's price & value and $unwind costs to calculate the sum of item's price & value and project to $subtract values from previous grouping to calculate final amount.

aggregate([{
      $unwind: '$items'
  }, {
      $group: {
          _id: '$_id',
          totalItems: {
              $sum: {
                  $multiply: ["$items.value", "$items.price"]
              }
          },
          costs: {
              $first: '$costs'
          }
      }
  }, {
      $unwind: '$costs'
  }, {
      $group: {
          _id: '$_id',
          totalItems: {
              $first: '$totalItems'
          },
          totalPrice: {
              $sum: '$costs.price'
          }
      }
  }, {
      $project: {
          _id: 1,
          amount: {
              $subtract: ['$totalItems', '$totalPrice']
          }
      }
  }])

这篇关于如何在mongoDB中使用聚合获得此结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！