mongoTemplate aggregate教程

头像
结义
    阅读 18 分钟
    2

    mongo 教程

    aggregate聚合

    1. aggreagate基本语法
    {
  aggregate: "<collection>" || 1,
  pipeline: [ <stage>, <...> ],
  explain: <boolean>,
  allowDiskUse: <boolean>,
  cursor: <document>,
  maxTimeMS: <int>,
  bypassDocumentValidation: <boolean>,
  readConcern: <document>,
  collation: <document>,
  hint: <string or document>,
  comment: <any>,
  writeConcern: <document>,
  let: <document> // Added in MongoDB 5.0
}
    1. aggregate的stage操作符
      常用的stage操作符
    2. $match: 匹配过滤
    3. $group: 分组
    4. $project: 标记输出文档的的字段,_id字段默认输出
    5. $unwind:将文档中的数组拆分成各个字段
    6. $skip: 跳过多少字段
    7. $limit: 限制输出文档数量

    note:pipeline顾名思义就是就是管道，也就是mongo的数据会经过这些管道内的操作逐渐的处理，然后在最后得到想要的结果，限制，内存不超过100 megabytes，stage步骤不能超过1000。
    建立文档books如下:

    { "_id" : 1, "title" : "abc123", "author" : { "last" : "zzz", "first" : "aaa" }, "price": 100,"prePrice":[80,90,100]}

    project

     db.books.aggregate( [ { $project : { _id: 0, title : 1 , author : 1 } } ] )

    运行结果:

    { "title" : "abc123", "author" : { "last" : "zzz", "first" : "aaa" } }

    project stage常使用的操作符号，一般常见的计算加减乘除平方等

    • abs: 计算绝对值
    • add： 计算相加，也就时同一行的多个字段或者直接加常量
    • multiply： 相乘
    • subtract： 减

    unwind 将字段为数组的文档，拆分成单独的文档。有点addToSet的逆过程的味道。

    db.books.aggregate([{ $ unwind: "$prePrice"}]);

    {
    "_id" : 1.0,
    "title" : "abc123",
    "author" : {
        "last" : "zzz",
        "first" : "aaa"
    },
    "price" : 100.0,
    "prePrice" : 80
}

/* 2 */
{
    "_id" : 1.0,
    "title" : "abc123",
    "author" : {
        "last" : "zzz",
        "first" : "aaa"
    },
    "price" : 100.0,
    "prePrice" : 90
}

/* 3 */
{
    "_id" : 1.0,
    "title" : "abc123",
    "author" : {
        "last" : "zzz",
        "first" : "aaa"
    },
    "price" : 100.0,
    "prePrice" : 100
}

    match就是单纯的mongo查询过滤，查看mongo的find查询语法。

    // todo

    group mongo的分组命令

    db.books.insertMany([
  { "_id" : 8751, "title" : "The Banquet", "author" : "Dante", "copies" : 2 },
  { "_id" : 8752, "title" : "Divine Comedy", "author" : "Dante", "copies" : 1 },
  { "_id" : 8645, "title" : "Eclogues", "author" : "Dante", "copies" : 2 },
  { "_id" : 7000, "title" : "The Odyssey", "author" : "Homer", "copies" : 10 },
  { "_id" : 7020, "title" : "Iliad", "author" : "Homer", "copies" : 10 }
])
    db.books.aggregate([{ $group: {_id : "$author",max: {$max: "$copies"}, sum:{$sum: "$copies"}}}])

    note: sum在group时，如果直接sum:1，输出的该group的分组条数，同mysql的count(*).

    {
    "_id" : "Homer",
    "max" : 10.0,
    "sum" : 20.0
}
 
/* 2 */
{
    "_id" : "Dante",
    "max" : 2.0,
    "sum" : 5.0
}

    增加需求，要求同时返回作者的书名称。

    db.books.aggregate([{ $group: {_id : "$author",max: {$max: "$copies"}, sum:{$sum: "$copies"},books: {$addToSet: "$title"}}}])
    {
    "_id" : "Homer",
    "max" : 10.0,
    "sum" : 20.0,
    "books" : [ 
        "Iliad", 
        "The Odyssey"
    ]
}

/* 2 */
{
    "_id" : "Dante",
    "max" : 2.0,
    "sum" : 5.0,
    "books" : [ 
        "Eclogues", 
        "Divine Comedy", 
        "The Banquet"
    ]
}

    增加一个stage，构造自己想要的自己想要的输出字段名称。

     db.books.aggregate([{ $group: {_id : "$author",max: {$max: "$copies"}, sum:{$sum: "$copies"},books: {$addToSet: "$title"}}},{$project: {"_id":0,"name":"$_id", "max":1, "sum":1}}])
    {
    "max" : 10.0,
    "sum" : 20.0,
    "name" : "Homer"
}
{
    "max" : 2.0,
    "sum" : 5.0,
    "name" : "Dante"
}

    在group操作stage时，可以配合的操作符号

    • addToSet : 将某个字段加入set(会去重)。 // 比较一下 $addToSet和push返回的结果
    • push : 将group所有的字段都加入，不去重。
    • avg： 取某个字段的平均值
    • sum: 聚合某个字段的和，
    • max: 返回分组中的最大值。
    • min: 返回分组中的最小值。
    • first： 返回分组中第一个该字段的值
    • last： 返回分组中最后一个该字段的值

    mongoTemplate

    常用类介绍

    1. Aggregation
      封装了mongo aggregate的常见stage，如：project，group，unwind等Operation
    2. AggregationExpression
      主要是实现了聚合的这个操作符，如 add,multiply等，还有各种转成类型操作等。

    aggregate stage Operation

    1. GroupOperation
      GroupOperation提供了基本的group支持的操作符，push，max，sum等
      sql: select Max("copies") as max, sum(copies) as sum from tables group by id;

      { $group: {_id : "$author",max: {$max: "$copies"}, sum:{$sum: "$copies"}}}
GroupOperation groupOperation = new GroupOperation(Fields.fields("author"))
.sum("copies").as("sum")
.max("copies").as("max"); // as 同sql的as，表示输出字段名称

      sql: select sum(copies) as count from table;

        Aggregation.group().sum("copies").as("count"); // group 空字段，即全表扫描。一般不建议，除非在这个stage前有一个match过滤掉大多数的数据。

    1. ProjectionOperation

      Fields fields = Fields.fields("f1","f2"); // 构造多个字段名称   
ProjectionOperation project = new ProjectOperation(Fields fields);   
ProjectionOperation operation = new ProjectionOperation(Fields.fields("foo").and("bar", "foobar"));
new ProjectionOperation().andExclude("foo"); // 排除某个字段
ProjectionOperation operation = Aggregation.project("foo").and("foobar").as("bar").andInclude("inc1", "inc2").andExclude("_id");
      ProjectionOperation operation = Aggregation.project() //
             .and("foo").plus("bar").as("fooPlusBar") //
             .and("foo").minus("bar").as("fooMinusBar") //
             .and("foo").multiply("bar").as("fooMultiplyBar") //
             .and("foo").divide("bar").as("fooDivideBar") //
             .and("foo").mod("bar").as("fooModBar"); // project自带的操作符限制了操作的数目，只能两个数加，
      ProjectionOperation projectionOperation = new ProjectionOperation();
     AggregationExpression aggregationExpression = ArithmeticOperators.Add.valueOf("english")
             .add("math")
             .add("chinese");
     projectionOperation = Aggregation.project().and(aggregationExpression).as("total"); // 处理多个字段相加

    mongoTembplate 实战

    使用springboot 添加mongo依赖，部署一个mongo本地服务器即可运行
    包含mongo的各种常见操作。
    一段操作学会mongoTemplate

    package com.example.springboot;

import lombok.Builder;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.aspectj.lang.annotation.Before;
import org.assertj.core.util.Lists;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.annotation.Id;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.BulkOperations;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.*;
import org.springframework.data.mongodb.core.mapping.Document;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.data.mongodb.core.query.UpdateDefinition;
import org.springframework.data.util.Pair;
import org.springframework.util.StringUtils;

import javax.swing.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static org.springframework.data.mongodb.core.aggregation.Aggregation.newAggregationOptions;

/**
 * @see com.example.springboot.SpringbootApplicationTests#testProject()
 */

@SpringBootTest
class SpringbootApplicationTests {
    @Autowired
    private  MongoTemplate mongoTemplate;
    @Test
    void contextLoads() {
    }

    /**
     *  初始化student数据
     */
    @Test
    public  void setupData(){
        Student student = Student.builder()
                .id("1").name("张三").age(15).chinese(90).maths(95).physics(93).chemistry(100).biology(99).english(98).build();
        Student student1 = Student.builder()
                .id("2").name("李四").age(18).chinese(80).maths(90).physics(93).chemistry(88).biology(93).english(83).build();
        Student student2 = Student.builder()
                .id("3").name("王五").age(16).chinese(72).maths(80).physics(84).chemistry(77).biology(79).english(72).build();
        Student student3 = Student.builder()
                .id("4").name("王小二").age(20).chinese(72).maths(80).physics(84).chemistry(77).biology(79).english(72).build();
        Student student4 = Student.builder()
                .id("5").name("jim").age(20).chinese(72).maths(80).physics(84).chemistry(77).biology(79).english(79).build();
        List<Student> students = Lists.newArrayList(student,student1, student2,student3, student4);
        BulkOperations bulkOperations =
                mongoTemplate.bulkOps(BulkOperations.BulkMode.UNORDERED, Student.COLLECTION);
        bulkOperations.insert(students);
        bulkOperations.execute();
    }

    /**
     *  初始化书籍数据
     */
    @Test
    public void setupBooksData(){
        Book apue = Book.builder().id("1").title("apue").author("stevens").type("computer").copies(8).build();
        Book  mysqlInAction = Book.builder().id("2").title("mysql in action").author("lee").type("computer").copies(10).build();
        Book  redisInAction = Book.builder().id("3").title("redis in action").author("lee").type("computer").copies(5).build();
        Book  englishABC = Book.builder().id("4").title("english A B C").author("li lei").type("english").copies(15).build();
        Book toefl = Book.builder().id("5").title("toefl").author("li lei").type("english").copies(10).build();
        Book chinese = Book.builder().id("6").title("chinese A B C").author("zhang san").type("chinese").copies(12).build();
        List<Book> students = Lists.newArrayList(apue, mysqlInAction, redisInAction, englishABC, toefl, chinese);
        BulkOperations bulkOperations =
                mongoTemplate.bulkOps(BulkOperations.BulkMode.UNORDERED, Book.COLLECTION);
        bulkOperations.insert(students);
        bulkOperations.execute();
    }
    @Test
    public void insertOneStudent(){
        Student student3 = Student.builder()
                .id("7").name("jeff").age(19).chinese(73).maths(80).physics(84).chemistry(77).biology(79).english(72).build();
        mongoTemplate.insert(student3);
    }

    /**
     *  数据中没有则保存，有这更新
     */
    @Test
    public void saveOneStudent(){
        Student student3 = Student.builder()
                .id("7").name("jeff").age(100).chinese(73).maths(80).physics(84).chemistry(77).biology(79).english(72).build();
        mongoTemplate.save(student3);
    }

    @Test
    public void batchSave(){
        // 见数据初始化处
    }

    /**
     *  update 批量更新，
     */
    @Test
    public void testBatchUpdate(){
        List<Pair<Query, Update>> updates = new ArrayList<>();
        for (int i = 0; i < 5; i ++){
            Query query = new Query();
            Criteria criteria = new Criteria().and("_id").is(i+ "");
            query.addCriteria(criteria);
            Update update =  Update.update("age", i);
            updates.add(Pair.of(query, update));
        }
        BulkOperations bulkOperations =
                mongoTemplate.bulkOps(BulkOperations.BulkMode.UNORDERED, "student");
        bulkOperations.updateMulti(updates);
        bulkOperations.execute();
    }

    /**
     *  将查询出来的结果统一修改。
     *   mongoTemplate.upsert 方法，如果不存在，则新建一条记录。存在则更新，非常有用。
     */
    @Test
    public void testUpdate(){
        Query query = new Query();
        Criteria criteria = new Criteria().and("age").is(18);
        query.addCriteria(criteria);
        UpdateDefinition update =  Update.update("english",  99);
        mongoTemplate.updateMulti(query, update, "student");
    }

    /**
     *  find $in
     */
    @Test
    public void testFindByIn(){
        Query query = new Query();
        Criteria criteria = new Criteria().and("age").in(18,19,20);
        query.addCriteria(criteria);
//        query.withHint("age"); // 推荐mongo使用索引
        query.skip(1);
        query.limit(10);
        query.with(Sort.by(Sort.Direction.DESC, "age"));
        String qString = query.getQueryObject().toString();
        System.out.println(qString);
        List<Student> students = mongoTemplate.find(query, Student.class);
        System.out.println(students);
    }

    /**
     *  正则
     *  db.student.find({name : /Jim/i});  都是忽略大小写的
     *  db.student.find({name : {$regex: "Jim", $options: "$i"}});
     */
    @Test
    public void testFindByRegex(){
        String regexStr = "^JI";
//        Criteria criteria = new Criteria().and("name").regex(regexStr, "I");  // I 忽略大小写
        Pattern pattern = Pattern.compile(regexStr, Pattern.CASE_INSENSITIVE);
        Criteria criteria = new Criteria().and("name").regex(pattern);
        Query query = Query.query(criteria);
        List<Student> students = mongoTemplate.find(query, Student.class);
        System.out.println(students);
    }



    /**
     *  group author.
     *  "pipeline" : [{ "$match" : {}}, { "$group" : { "_id" : "$author", "copiesTotal" : { "$sum" : "$copies"}, "copiesMax" : { "$max" : "$copies"}, "avg" : { "$avg" : "$copies"}}}]
     */
    @Test
    public void TestGroup() {
        List<AggregationOperation> operations = Lists.newArrayList();
        Criteria criteria = new Criteria();
        // todo 增加过滤条件
        operations.add(Aggregation.match(criteria));
        GroupOperation groupOperation = new GroupOperation(Fields.fields("author")).
                sum("copies").as("copiesTotal").
                max("copies").as("copiesMax").
                avg("copies").as("avg");

        operations.add(groupOperation);
        Aggregation aggregation = Aggregation.newAggregation(operations)
                .withOptions(newAggregationOptions().allowDiskUse(true).build());
        AggregationResults<Map> aggregationResults =
                mongoTemplate.aggregate(aggregation, "book", Map.class);
        System.out.println(aggregationResults.getMappedResults());
    }

    /**
     *  $group 字段为null时，去所有$match匹配的文档都为一个分组。
     *  $push  将字段加入某个数组，所有group的分组中的字段都加入
     * $addToSet 和push相比会去重
     *
     *  "pipeline" : [{ "$match" : {}}, { "$group" : { "_id" : null, "authurs" : { "$push" : "$author"}}}, { "$project" : { "_id" : 0}}]
     *  "pipeline" : [{ "$match" : {}}, { "$group" : { "_id" : null, "authors" : { "$push" : "$author"}, "uniqueAuthors" : { "$addToSet" : "$author"}}}, { "$project" : { "_id" : 0}}]
     */
    @Test
    public void testPush(){
        List<AggregationOperation> operations = Lists.newArrayList();
        Criteria criteria = new Criteria();
        operations.add(Aggregation.match(criteria));
        GroupOperation groupOperation = Aggregation.group().
                push("author").as("authors")
                .addToSet("author").as("uniqueAuthors");
        operations.add(groupOperation);
        ProjectionOperation projectionOperation = Aggregation.project().andExclude("_id");
        operations.add(projectionOperation);
        Aggregation aggregation = Aggregation.newAggregation(operations)
                .withOptions(newAggregationOptions().allowDiskUse(true).build());
        AggregationResults<Map> aggregationResults =
                mongoTemplate.aggregate(aggregation, "book", Map.class);
        System.out.println(aggregationResults.getMappedResults());
    }

    /**
    * $unwind 依据某个字段数组拆分成单独的文档。
     *  "pipeline" : [{ "$match" : {}}, { "$group" : { "_id" : null, "authors" : { "$push" : "$author"}, "uniqueAuthors" : { "$addToSet" : "$author"}}}, { "$project" : { "_id" : 0}}, { "$unwind" : "$authors"}]
     */
    @Test
    public void testUnwind(){
        List<AggregationOperation> operations = Lists.newArrayList();
        Criteria criteria = new Criteria();
        operations.add(Aggregation.match(criteria));
        GroupOperation groupOperation = Aggregation.group().
                push("author").as("authors")
                .addToSet("author").as("uniqueAuthors");
        operations.add(groupOperation);
        ProjectionOperation projectionOperation = Aggregation.project().andExclude("_id");
        operations.add(projectionOperation);
        operations.add(Aggregation.unwind("authors"));
        Aggregation aggregation = Aggregation.newAggregation(operations)
                .withOptions(newAggregationOptions().allowDiskUse(true).build());
        AggregationResults<Map> aggregationResults =
                mongoTemplate.aggregate(aggregation, "book", Map.class);
        System.out.println(aggregationResults.getMappedResults());
    }

    /**
     *  project处理english和maths之和，并不输出id字段。
     *   "pipeline" : [{ "$match" : {}}, { "$project" : { "ts" : { "$add" : ["$english", "$maths"]}, "_id" : 0}}]
     */
    @Test
    public void testProject(){
        List<AggregationOperation> operations = Lists.newArrayList();
        Criteria criteria = new Criteria();
        operations.add(Aggregation.match(criteria));
        ProjectionOperation projectionOperation = new ProjectionOperation();
        Fields fields =  Fields.fields("student");
        projectionOperation.andInclude(fields);
        projectionOperation = Aggregation.project().and("english").plus("maths").as("ts").andExclude("_id");
        operations.add(projectionOperation);
        Aggregation aggregation = Aggregation.newAggregation(operations)
                .withOptions(newAggregationOptions().allowDiskUse(true).build());
        AggregationResults<Map> aggregationResults =
                mongoTemplate.aggregate(aggregation, "student", Map.class);
        System.out.println(aggregationResults.getMappedResults());
    }

    /**
     * project 输出指定字段，排除其他字段
     * "pipeline" : [{ "$match" : {}}, { "$project" : { "name" : 1, "english" : 1, "maths" : 1, "chinese" : 1, "_id" : 0}}]
     */
    @Test
    public void testProjectField(){
        List<AggregationOperation> operations = Lists.newArrayList();
        Criteria criteria = new Criteria();
        operations.add(Aggregation.match(criteria));
        ProjectionOperation projectionOperation = new ProjectionOperation();
        Fields fields =  Fields.fields("name", "english", "maths", "chinese");
        projectionOperation = projectionOperation.andInclude(fields).andExclude("_id"); //   Aggregation.project(fields).andExclude("_id");
        operations.add(projectionOperation);
        Aggregation aggregation = Aggregation.newAggregation(operations)
                .withOptions(newAggregationOptions().allowDiskUse(true).build());
        AggregationResults<Map> aggregationResults =
                mongoTemplate.aggregate(aggregation, "student", Map.class);
        System.out.println(aggregationResults.getMappedResults());
    }

    /**
     * 统计学生几门课的总分。
     * 存在问题，如果一个学生的某个课程分数为null，或者活没有该字段，则统计的total为null.
     *  "pipeline" : [{ "$match" : {}}, { "$project" : { "total" : { "$add" : ["$english", "$maths", "$chinese"]}}}]
     */

    @Test
    public void testAggregate(){
        List<AggregationOperation> operations = Lists.newArrayList();
        Criteria criteria = new Criteria();
        operations.add(Aggregation.match(criteria));
        ProjectionOperation projectionOperation;
        AggregationExpression aggregationExpression = ArithmeticOperators.Add.valueOf("english")
                .add("maths")
                .add("chinese");
        projectionOperation = Aggregation.project().and(aggregationExpression).as("total");
        operations.add(projectionOperation);
        Aggregation aggregation1 = Aggregation.newAggregation(operations)
                .withOptions(newAggregationOptions().allowDiskUse(true).build());
        AggregationResults<Map> aggregationResults1 =
                mongoTemplate.aggregate(aggregation1, "student", Map.class);
        System.out.println(aggregationResults1.getMappedResults());
    }

    /**
     *  怎么多个$or一起操作。
     *  "pipeline" : [{ "$match" : { "name" : "张三", "$or" : [{ "maths" : { "$gte" : 90}}, { "english" : { "$gte" : 90}}, { "chinese" : { "$gte" : 90}}]}}]
     */
    @Test
    public void testOrOperation(){
        List<AggregationOperation> operations = Lists.newArrayList();
        Criteria criteria = new Criteria();
        criteria = criteria.and("name").is("张三");
        Criteria criteria1 = new Criteria().and("maths").gte(90);
        Criteria criteria2 = new Criteria().and("english").gte(90);
        Criteria criteria3 = new Criteria().and("chinese").gte(90);
        criteria = criteria.orOperator(criteria1, criteria2, criteria3);
        operations.add(Aggregation.match(criteria));
        Aggregation aggregation = Aggregation.newAggregation(operations).withOptions(newAggregationOptions().allowDiskUse(true).build());
        AggregationResults<Student> results = mongoTemplate.aggregate(aggregation, "student", Student.class);
        System.out.println(results.getMappedResults());
    }


    @Setter
    @Getter
    @ToString
    @Document(Student.COLLECTION)
    @Builder
    static class Student{
        public static final String COLLECTION = "student";
        public static final String MATHS = "maths";
        public static final String CHINESE = "chinese";
        public static final String ENGLISH = "english";
        public static final String PHYSICS = "physics";
        public static final String CHEMISTRY = "chemistry";
        public static final String BIOLOGY = "biology";
        @Id
        private String id;
        private String name;
        private int age;
        private int maths;
        private int chinese;
        private int english;
        private int physics;
        private int chemistry;
        private int biology;
    }
    @Setter
    @Getter
    @ToString
    @Document(Book.COLLECTION)
    @Builder
    static class Book {
        public static final String COLLECTION = "book";
        @Id
        private String id;
        private String title;
        private String author;
        private int copies;  // 库存数
        private String type;
    }

}
    mongodbjavaspringboot
    结义
    6 声望0 粉丝
    « 上一篇
    mongo慢日志字段含义
    下一篇 »
    mongo的常见命令和问题分析

    引用和评论

    推荐阅读
    头像
    mongo的数据恢复和迁移

    结义阅读 1.5k

    头像
    Jerry和您聊聊Chrome开发者工具

    注销赞 9阅读 4.4k评论 3

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 9阅读 1.7k

    头像
    Bitmap 和 布隆过滤器傻傻分不清？你这不应该啊

    程序员小富赞 7阅读 1.2k

    头像
    Just for fun——迅速写完快速排序

    ufdf赞 3阅读 2.5k

    头像
    状态码 406， Not Acceptable, (Spring Boot)

    zZ_jie赞 4阅读 653

    头像
    Spring 实现 3 种异步流式接口，干掉接口超时烦恼

    程序员小富赞 4阅读 1.5k

    0 条评论
    得票最新