db.getCollection("table111").aggregate(
// Pipeline
[
// Stage 1,匹配数据
{
$match: { $and : [ { "status" : "SUCCESS" }, { "createTime" : { $gte : ISODate("2023-03-14T00:00:00.134+0000") } }, { "createTime" : { $lte : ISODate("2023-03-14T23:59:59.134+0000") } } ] }
},
// Stage 2,按照时间格式化并分组
{
$project: {
// specifications
字段1:1,
字段2:1,
statisticDate:{
$dateToString: {
// format: "%Y-%m-%d %H:%M:%S",
format: "%Y-%m-%d",
date: "$createTime"
}
}
}
},
// Stage 3,多字段分组
{
$group:
{
_id: {"字段1名":"$字段1", "字段2名":"$字段2", "project中的字段statisticDate":"$statisticDate"},
count: { "$sum" : 1 },
}
}
],
// Options
{
}
);
Java代码:
Criteria criteria = Criteria.where("status").is("SUCCESS");
MatchOperation match = Aggregation.match(criteria);
ProjectionOperation project = Aggregation.project("字段1", "字段2", "statisticDate").andExpression("{$dateToString: {format: '%Y-%m-%d',date: '$createTime'}}").as("statisticDate");
GroupOperation groupOperation = Aggregation.group(Fields.fields("字段1", "字段2", "project中的字段statisticDate")).count().as("count");
Aggregation aggregation = Aggregation.newAggregation(match, project, groupOperation);
List<JSONObject> mappedResults = mongoTemplate.aggregate(aggregation, xxx.class, JSONObject.class).getMappedResults();
查询得到数据格式:
[
{
"count": 42,
"_id": {
"字段1": "aaa",
"字段2": "aaaaa",
"statisticDate": "2023-02-17"
}
},
{
"count": 1,
"_id": {
"字段1": "bbb",
"字段2": "bbbbb",
"statisticDate": "2023-02-20"
}
}
]