MongoDB多条件分组聚合查询

1、样例数据

{
    "_id" : ObjectId("5fa13fb76c3107345a82c047"),
    "_class" : "java.util.HashMap",
    "date" : "2020-11-03",
    "result" : "命中失败",
    "activityCode" : "TEST_CODE_123",
    "activityName" : "测试活动123",
    "channel" : "大唐"
}
{
    "_id" : ObjectId("5fa13fb76c3107345a82c049"),
    "_class" : "java.util.HashMap",
    "date" : "2020-11-03",
    "result" : "命中失败",
    "activityCode" : "TEST_CODE_456",
    "activityName" : "测试活动456",
    "channel" : "大汉"
}
{
    "_id" : ObjectId("5fa13fb76c3107345a82c050"),
    "_class" : "java.util.HashMap",
    "date" : "2020-11-03",
    "result" : "命中失败",
    "activityCode" : "TEST_CODE_456",
    "activityName" : "测试活动456",
    "channel" : "大秦"
}

 

2、需求分析

需要统计某一日期下不同活动不同渠道的调用量

3、统计SQL

db.collectionName.aggregate([{
    $match: {
        "date": "2020-11-03",
        "result": "命中失败"
    }
},
{
    $group: {
        _id: {
            activityCode: "$activityCode",
            channel: "$channel"
        },
        activityCode: {
            "$first": "$activityCode"
        },
        channel: {
            "$first": "$channel"
        },
        total: {
        $sum: 1
        }
    }
},
{
    $project: {
        _id: 0
    }
}])

4、SQL说明

#MongoDB的聚合管道将MongoDB文档在一个管道处理完毕后将结果传递给下一个管道处理
db.COLLECTION_NAME.aggregate({管道操作1},{管道操作2},...)

#这里其实就两个问题：如何多条件分组、如何展示各个分组条件的列
#多条件分组写法
{$group:{_id:{"别名1":"$分组字段1"，"别名2":"$分组字段2"，····},total:{$sum:1}}}

#展示各个分组条件的列
#如果不经过处理直接查询会得到如下值：
{
  "_id" : {
    "activityCode" : "TEST_CODE_123",
    "channel" : "大唐"
  },
  "total" : 1
}
#这里activityCode、channel就会被包裹在_id里面，需要手工处理
#取出_id里分组字段对应的值写法
{ $group: { _id: { activityCode: "$activityCode", channel: "$channel" }, activityCode: { "$first": "$activityCode" }, channel: { "$first": "$channel" }, total: { $sum: 1 } } }
#说明：
   $first:根据资源文档的排序获取第一个文档数据  别名1:{"$first": "$分组字段1"}、别名2:{"$first": "$分组字段2"}
或者$last:根据资源文档的排序获取最后一个文档数据 别名1:{"$last": "$分组字段1"}、 别名2:{"$last": "$分组字段2"}
#得到结果
{
  "_id" : {
    "activityCode" : "TEST_CODE_123",
    "channel" : "大唐"
  },
  "activityCode" : "TEST_CODE_123",
  "channel" : "大唐",
  "total" : 1
}
#最后在 $project: { _id: 0} 配置_id隐藏输出，得到想要的结果:
{
  "activityCode" : "TEST_CODE_123",
  "channel" : "大唐",
  "total" : 1
}