mongo常用聚合(aggregate)案例综合

mongo常用查询方法

mongo查询通过了管道操作,可以把查询结果当作一个新集合进行链式操作
单个操作语法官网写得很清楚,我直接列举真实业务案例

Criteria 可通过Aggregation.match()转换为Aggregation,但框架提供的Criteria在对自身的操作上不太灵活,在此我继承Criteria并加了几个方法,CustomCriteria类代码如下:

import org.springframework.data.mongodb.core.query.Criteria;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

public class CustomCriteria extends Criteria {
    ArrayList<Criteria> arrayList;

    public CustomCriteria() {
        super();
        this.arrayList = new ArrayList<>();
    }

    public CustomCriteria(String key) {
        super(key);
        this.arrayList = new ArrayList<>();
    }

    public CustomCriteria(List<Criteria> criteriaChain, String key) {
        super(criteriaChain, key);
        this.arrayList = new ArrayList<>();
    }

    /**
     * @param criteria Criteria
     * @return CustomCriteria
     * @description 链式调用添加Criteria
     */
    public CustomCriteria addCriteria(Criteria criteria) {
        if (criteria != null) {
            arrayList.add(criteria);
        }
        return this;
    }

    /**
     * @param criteria Criteria
     * @return CustomCriteria
     * @description 链式调用, 在某位置添加Criteria
     */
    public CustomCriteria addCriteria(int index, Criteria criteria) {
        if (criteria != null) {
            arrayList.add(index, criteria);
        }
        return this;
    }

    /**
     * @param index    索引
     * @param criteria Criteria
     * @return CustomCriteria
     * @description 替换某个Criteria
     */
    public CustomCriteria replaceCriteria(int index, Criteria criteria) {
        arrayList.set(index, criteria);
        return this;
    }

    /**
     * @param index 索引
     * @return CustomCriteria
     * @description 删除某个Criteria, 注意对ArrayList操作的问题, 删除某值, 整个索引发生变化
     */
    public CustomCriteria removeCriteria(int index) {
        arrayList.remove(index);
        return this;
    }

    /**
     * @return CustomCriteria
     * @description 调用了end()方法后需要对Criteria修改, 必须调用此方法, 重置CriteriaChain
     */
    public CustomCriteria updateCriteriaChain() throws IllegalAccessException, NoSuchFieldException {
        Class<?> superclass = this.getClass().getSuperclass();
        Field criteriaChain = superclass.getDeclaredField("criteriaChain");
        criteriaChain.setAccessible(true);
        criteriaChain.set(this, new ArrayList<>());
        return this;
    }

    /**
     * @return CustomCriteria
     * @description 链式调用结束必须调用此方法
     */
    public CustomCriteria end() {
        if (this.arrayList.size() == 0) {
            return this;
        }
        Criteria[] cri = new Criteria[this.arrayList.size()];
        for (int i = 0; i < this.arrayList.size(); i++) {
            cri[i] = this.arrayList.get(i);
        }
        this.andOperator(cri);
        return this;
    }
}

一.aggregate使用形式:

1.脚本

db.表名.aggregate([
   
])

2.mongoTemplate

AggregationResults<T> m = mongoTemplate.aggregate(aggregation, 表名, T.class);

二.业务案例

1.包含($in)匹配,分组($group)统计($sum),统计结果排序($sort),取前五条($limit)

1).脚本

{
        "$match": {
            "areaCode": {
                "$in": [100000, 360000]
            }
        }
    },
    {
        "$group": {
            "_id": "$groupField",
            "count": {
                "$sum": 1
            }
        }
    },
    {
        "$sort": {
            "count": - 1
        }
    },
    {
        "$limit": 5
    }

2).mongoTemplate

ArrayList<AggregationOperation> operationList = new ArrayList<>();
if (motorcade == null) {   
    operationList.add(Aggregation.match(Criteria.where("areaCode").in(areaCodes)));
}
operationList.add(Aggregation.group("groupField").count().as("count"));
operationList.add(Aggregation.sort(Sort.Direction.DESC, "count"));
operationList.add(Aggregation.limit(5));
Aggregation agg = Aggregation.newAggregation(operationList);

2.字段值非空($exists)匹配, 多字段分组($group)统计($sum), 不显示($project)某字段

1).脚本

{
        "$match": {
            "nonNullField": {
                "$exists": true
            }
        }
    },
    {
        "$group": {
            "_id": {
                "groupField1": "$groupField1",
                "groupField2": "$groupField2"
            },
            "count": {
                "$sum": 1
            }
        }
    },
    {
        "$addFields": {
            //添加两个新字段,它的值分别为分组字段的值
            "newField1": "$_id.groupField1",
            "newField2": "$_id.groupField2"
        }
    },
    {
        "$project": {
            //为0则不显示,1则显示
            "_id": 0
        }
    }

2).mongoTemplate

ArrayList<AggregationOperation> operationList = new ArrayList<>();
       operationList.add(Aggregation.match(Criteria.where("nonNullField").exists(true)));
//java代码中可通过如下方式加入mongo脚本
operationList.add(context -> Document.parse("{\n" +
                "    $group: {\n" +
                "        _id: {\n" +
                "            groupField1: \"$groupField1\",\n" +
                "            groupField2: \"$groupField1\",\n" +
                "        },\n" +
                "        count: {\n" +
                "            $sum: 1\n" +
                "        }\n" +
                "    }\n" +
                "}"));
operationList.add(context -> Document.parse("{\n" +
                "        $addFields: {\n" +
                "            newField1: \"$_id.groupField1\",\n" +
                "            newField2: \"$_id.groupField2\"\n" +
                "        }\n" +
                "    }"));
operationList.add(Aggregation.project().andExclude("_id"));
Aggregation agg = Aggregation.newAggregation(operationList);

3.求余($mod), 求差($subtract),分组($group)统计($sum)

集合存了某类型(包含大类小类),这个例子的大类和小类应该满足哈夫曼编码,且树高为3,树宽为10,舍去祖父节点.举例:大类用10,20,30…表示, 小类用11,12(归属10这大类)…21,22(归属20这大类)… 表示
现在需要对大类进行统计
先通过管道将小类转为大类再统计(这里用求余作差的方法:大类=小类-(大类%10) )

1).脚本

 {
        "$project": {
            "typeCode": 1,
            "tmp": {
                "$mod": ["$typeCode", 10]
            }
        }
    },
    {
        "$project": {
            "bigTypeCode": {
                "$subtract": ["$typeCode", "$tmp"]
            }
        }
    },
    {
        "$group": {
            "_id": "$bigTypeCode",
            "count": {
                "$sum": 1
            }
        }
    }

2).mongoTemplate

ArrayList<AggregationOperation> operationList = new ArrayList<>();
operationList.add(Aggregation.project("typeCode").and("typeCode").mod(10).as("tmp"));
operationList.add(context -> Document.parse(" {\n" +
                "        \"$project\": {\n" +
                "            \"bigTypeCode\": {\n" +
                "                \"$subtract\": [\"$typeCode\", \"$tmp\"]\n" +
                "            }\n" +
                "        }\n" +
                "    }"));
operationList.add(context -> Document.parse("{\n" +
                "        \"$group\": {\n" +
                "            \"_id\": \"$bigTypeCode\",\n" +
                "            \"count\": {\n" +
                "                \"$sum\": 1\n" +
                "            }\n" +
                "        }\n" +
                "    }"));

Aggregation agg = Aggregation.newAggregation(operationList);

4.且($and)匹配, 大于($gt)小于等于($lte)

1).脚本

{
        "$match": {
            "$and": [ {
                "time": {
                    "$lte": ISODate("2021-03-02T00:00:00Z")
                }
            }, {
                "time": {
                    "$gt": ISODate("2021-02-23T08:06:52.528Z")
                }
            }]
        }
    }

2).mongoTemplate

Criteria criteria = new Criteria();
criteria.addCriteria(Criteria.where("time").lte(endTime));
criteria.addCriteria(Criteria.where("time").gt(startTime));
//Criteria 可通过Aggregation.match()转换为Aggregation
Aggregation agg = Aggregation.newAggregation(Aggregation.match(criteria));

5.时间转字符($dateToString), 多分组($group)统计($sum)

业务需要按天统计某类型的总数,但时间字段精确到了毫秒值
先将日期格式化为年-月-日,再对格式时间和类型做多字段统计

1).脚本

{
        "$project": {
            "type": 1,
            "formatTime": {
                "$dateToString": {
                    //格式化字符串
                    "format": "%Y-%m-%d",
                    //time为需要格式的字段
                    "date": "$time"
                }
            }
        }
    },
    {
        "$group": {
            "_id": {
                "time": "$time",
                "type": "$type"
            },
            "count": {
                "$sum": 1
            }
        }
    }

2).mongoTemplate

ArrayList<AggregationOperation> operationList = new ArrayList<>();
operationList.add(Aggregation.project("type").and("time").dateAsFormattedString("%Y-%m-%d").as("formatTime"));
operationList.add(Aggregation.group("formatTime", "type").count().as("count"));
Aggregation aggregation = Aggregation.newAggregation(operationList);

6.过滤字段值为空字符($ne),再分组($group)统计($sum)

1).脚本

{
        "$match": {
            "field": {
                "$ne": ""
            }
        }
    },
    {
        "$group": {
            "_id": "$field",
            "count": {
                "$sum": 1
            }
        }
    }

2).mongoTemplate

ArrayList<AggregationOperation> operationList = new ArrayList<>();
operationList.add(Aggregation.match(Criteria.where("field").ne("")));
operationList.add(Aggregation.group("field").count().as("count"));
Aggregation agg = Aggregation.newAggregation(operationList);

7.分组($group)并条件($cond:if:else)求和($sum)

1).脚本

{
        $group: {
            _id: {
                field1: "$field1",
                field2: "$field2"
            },
            count1: {
                $sum: { $cond: { if: { $eq: [ "$field3", value1] }, then: 1, else: 0 } }
            },
			count2: {
                $sum: { $cond: { if: { $eq: [ "$field3", value2 ] }, then: 1, else: 0 } }
            }
        }
    }