MongoDB聚合组_id按不同字段

Question

我有收款账户:

{
  "_id": ObjectId("571a0e145d29024733793cfe"),
  "name": "Cash",
  "user": ObjectId("571a0e145d29024733793cfc")
}

和交易。事务可以有不同的字段:

{
  "_id": ObjectId("571a0e145d29024733793d06"),
  "amount": 100,
  "type": "earned",
  "account": ObjectId("571a0e145d29024733793cfe"),
  "user": ObjectId("571a0e145d29024733793cfc"),

},
{
  "_id": ObjectId("571a0e145d29024733793d04"),
  "amount": 300,
  "type": "spent",
  "account": ObjectId("571a0e145d29024733793cfe"),
  "user": ObjectId("571a0e145d29024733793cfc")
},
{
  "_id": ObjectId("571a0e145d29024733793d07"),
  "amount": 100,
  "type": "transfer",
  "sourceAccount": ObjectId("571a0e145d29024733793cfd"),
  "destinationAccount": ObjectId("571a0e145d29024733793cfe"),
  "user": ObjectId("571a0e145d29024733793cfc"),
}

我想按每个账号做一个分组统计。我写了一个聚合框架查询到数据库:

db.transactions.aggregate([

  { $match: { user: user._id } },

  {
    $group: {
      _id: '$account',

      earned: {
        $sum: {
          $cond: [{ $eq: ['$type', 'earned'] }, '$amount', 0]
        }
      },

      spent: {
        $sum: {
          $cond: [{ $eq: ['$type', 'spent'] }, '$amount', 0]
        }
      },

      deposits: {
        $sum: {
          $cond: [{ $eq: ['$type', 'transfer'] }, '$amount', 0]
        }
      },

      withdrawal: {
        $sum: {
          $cond: [{ $eq: ['$type', 'transfer'] }, '$amount', 0]
        }
      },

      maxEarned: {
        $max: {
          $cond: [{ $eq: ['$type', 'earned'] }, '$amount', 0]
        }
      },

      maxSpent: {
        $max: {
          $cond: [{ $eq: ['$type', 'spent'] }, '$amount', 0]
        }
      },

      count: { $sum: 1 }

    }
  }
]);

但它不能正常工作。它仅适用于现场账户的交易。我想按现场帐户或 sourceAccount 或 destinationAccount 分组。

我还尝试在“_id”字段中写入:

_id: { account: '$account', sourceAccount: '$sourceAccount', destinationAccount: '$destinationAccount' }

或

_id: {$or: ['$account', '$sourceAccount', '$destinationAccount']}

或

_id: {$in: ['$account', '$sourceAccount', '$destinationAccount']}

但它会导致错误的分组或不起作用。

如何对不同领域进行分组？

Answer 1

使用 $cond 运算符作为 _id 表达式，根据您指定的条件按键评估组。 <强> $cond 运算符使用比较运算符 $gt 来评估确定字段是否存在的 bool 表达式并使用此 comparison order 。因此，您可以按照以下示例重组您的管道:

db.transactions.aggregate([
    {
        "$group": {
            "_id": { 
                "$cond": [
                    { "$gt": [ "$account", null ] }, 
                    "$account", 
                    { 
                        "$cond": [
                            { "$gt": [ "$sourceAccount", null ] }, 
                            "$sourceAccount", 
                            "$destinationAccount" 
                        ] 
                    } 
                ] 
            },
            "count": { "$sum": 1 }
        }
    }
])

示例输出

{ "_id" : ObjectId("571a0e145d29024733793cfd"), "count" : 1 }
{ "_id" : ObjectId("571a0e145d29024733793cfe"), "count" : 2 }