monogdb 实现聚合(多表联查）springBoot 分组， topN

参见:原文链接：https://blog.csdn.net/weixin_44530530/java/article/details/91901631

1.联表查询 

2. 分组去重（案例）

3. 分组

4. topn

依赖:

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-mongodb</artifactId>
 </dependency>

 

 2.聚合后分组去重

案例：

 

     select * from print-history history,document-info docInfo 
      where history.docId=docInfo.id
      and docInfo.metaData.bookingNo="SB-230216-000008"
      and docInfo.metaData.docType="BadPaper"
      and docInfo.docStatus="VALID"  order  by  createDatGMT desc  group by docId

 

1. 两表联查

 

表：print-history

 

{
  "_id": {
    "$oid": "63ec3648ecd2740006e9dbb3"
  },
  "docId": "63ec3644ecd2740006e9dbb2",
  "printerName": "FFF_TEST1",
  "printEmployeeID": "199009",
  "copies": 1,
  "printPerson": "sea",
  "createdDateGMT": {
    "$date": "2023-02-15T01:32:56.000Z"
  }
}

 

表： document-info

{
  "_id": {
    "$oid": "63eb61c6f337380006f852eb"
  },
  "docName": "1672296091835",
  "docFormat": "jpg",
  "docSize": 15691,
  "docStatus": "VALID",
  "createdDateGMT": {
    "$date": "2023-02-14T10:26:14.000Z"
  },
  "lastTimeStamp": "1676370374128",
  "metaData": {
    "systemName": "XX_SS",
    "officeCode": "XXX",
    "bookingNo": "SB-23E214-000377",
    "docType": "AI"
  }
}

 

 

 

 

  /**
     * select * from print-history history,document-info docInfo
     * where history.docId=docInfo.id
     * and docInfo.metaData.bookingNo="X-230216-000008"
     * and docInfo.metaData.docType="HeadPaper"
     * and docInfo.docStatus="VALID"
     */



    @Test
    public void findAggregation() {
        LookupOperation lookupOperation=LookupOperation.newLookup().
                from("document-info"). //关联从表名
                localField("my_docId").     //主表关联字段  String   (关联字段的数据类型要一致，不一致需要转换为一样的数据类型)
                foreignField("_id").//从表关联的字段    ObjectId   ({"_id":{"$oid":"63ec3648ecd2740006e9dbb3"}) , 聚合时需要转换
                as("doc_info");   //查询结果名

        // 追加查询条件 -- 查询条件
        Criteria criteria1 = Criteria.where("doc_info.metaData.bookingNo").is("UBK-230216-000008").and("doc_info.metaData.docType").is("BoardHeadPaper").and("doc_info.docStatus").is("VALID");
        MatchOperation match1 = Aggregation.match(criteria1);
        //排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "createdDateGMT"));
　　　　　// 分组
        GroupOperation group = Aggregation.group("docId").first("docId").as("docId").addToSet("$$ROOT").as("data");
　　　　　//需要的字段
        ProjectionOperation projection = Aggregation.project().andExclude("_id")
 //使用函数格式化日期
.andExpression("{ $dateToString: {date: '$createdDateGMT', format: '%Y-%m-%d %H:%M:%S', timezone: '+00:00'}}").as("createdDateGMT")；

　　　　　// 聚合，需要注意顺序 // 添加一个字段my_docId // 把 docId 转为 ObjectId ， 赋值给my_docId //aoc -> new Document("$addFields",new Document("docId1",new Document("$toObjectId","$docId")) Aggregation aggregation= Aggregation.newAggregation(aoc -> new Document("$addFields",new Document("my_docId",new Document("$toObjectId","$docId"))),
lookupOperation,match1,sortOperation,group,projection);

        List<Map> results = mongoTemplate.aggregate(aggregation,"print-history", Map.class).getMappedResults();
        System.out.println(JSON.toJSONString(results));
    }

 

3 .分组 ， TopN

数据：

 

[{"name":"sea","age":1},
{"name":"sea","age":2},
{"name":"sea","age":3},
{"name":"sea1","age":1},
{"name":"sea1","age":2},
{"name":"sea1","age":3},
{"name":"sea2","age":1}]

 

 

    @Test
    public void testGroup() throws Exception
    {
        // 追加查询条件 -- 查询条件
//        Criteria criteria1 = Criteria.where("doc_info.metaData.bookingNo").is("UBK-230216-000008").and("doc_info.metaData.docType").is("BoardHeadPaper").and("doc_info.docStatus").is("VALID");
//        MatchOperation match1 = Aggregation.match(criteria1);
        //排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "age"));
        //分组
        GroupOperation group = Aggregation.group("name").first("name").as("name").addToSet("$$ROOT").as("data");
        //过滤字段
        ProjectionOperation projection = Aggregation.project().andExclude("_id");

        // 添加一个字段my_docId // 把 docId 转为 ObjectId ， 赋值给my_docId
        //aoc -> new Document("$addFields",new Document("docId1",new Document("$toObjectId","$docId"))
        Aggregation aggregation= Aggregation.newAggregation(sortOperation,group,projection);

        List<Map> results = mongoTemplate.aggregate(aggregation,"SeaTest", Map.class).getMappedResults();
        System.out.println(JSON.toJSONString(results));

    }


    /**
     *  根据xx 分组， 获取topN
     * @throws Exception
     */
    @Test
    public void testTOPN() throws Exception
    {
        int topN = 10;
        // 追加查询条件 -- 查询条件
//        Criteria criteria1 = Criteria.where("doc_info.metaData.bookingNo").is("UBK-230216-000008").and("doc_info.metaData.docType").is("BoardHeadPaper").and("doc_info.docStatus").is("VALID");
//        MatchOperation match1 = Aggregation.match(criteria1);
        //排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "age"));
        GroupOperation group = Aggregation.group("name").addToSet("$$ROOT").as("data");
        ProjectionOperation projection = Aggregation.project().andExclude("_id")
        .andExpression("slice(data, "+topN+")").as("data");
        // 添加一个字段my_docId // 把 docId 转为 ObjectId ， 赋值给my_docId
        //aoc -> new Document("$addFields",new Document("docId1",new Document("$toObjectId","$docId"))
        SortOperation lastSortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "data.name"));
        Aggregation aggregation= Aggregation.newAggregation(sortOperation,group,projection,lastSortOperation);
        List<Map> results = mongoTemplate.aggregate(aggregation,"SeaTest", Map.class).getMappedResults();
        System.out.println(JSON.toJSONString(results));
    }

 

分组结果： [{"name":"sea4","data":[{"_id":{"date":1676967172000,"timestamp":1676967172},"name":"sea4","age":4}]},{"name":"sea1","data":[{"_id":{"date":1676967172000,"timestamp":1676967172},"name":"sea1","age":1},{"_id":{"date":1676967322000,"timestamp":1676967322},"name":"sea1","age":3},{"_id":{"date":1676967322000,"timestamp":1676967322},"name":"sea1","age":2},{"_id":{"date":1676967466000,"timestamp":1676967466},"name":"sea1","age":1}]},{"name":"sea3","data":[{"_id":{"date":1676967172000,"timestamp":1676967172},"name":"sea3","age":3}]},{"name":"sea2","data":[{"_id":{"date":1676967172000,"timestamp":1676967172},"name":"sea2","age":2}]},{"name":"sea","data":[{"_id":{"date":1676967307000,"timestamp":1676967307},"name":"sea","age":1},{"_id":{"date":1676967307000,"timestamp":1676967307},"name":"sea","age":2}]},{"name":"sea5","data":[{"_id":{"date":1676967172000,"timestamp":1676967172},"name":"sea5","age":5}]}]

4.数据侧写，扁平化 

 

    /**
     *  根据name  分组，  获取age 最大的topN 条数据 ,然后扁平化， eg:           侧写, 扁平数据    { "name":["sea","sea1"] "age":17}  =>  {"name":"sea","age":17},{"name":"sea1","age":17}
     * @throws Exception
     */
    @Test
    public void testFlatMap() throws Exception
    {
        int topN = 10;
        // 追加查询条件 -- 查询条件
//        Criteria criteria1 = Criteria.where("doc_info.metaData.bookingNo").is("UBK-230216-000008").and("doc_info.metaData.docType").is("BoardHeadPaper").and("doc_info.docStatus").is("VALID");
//        MatchOperation match1 = Aggregation.match(criteria1);
        //排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "age"));
        GroupOperation group = Aggregation.group("name").addToSet("$$ROOT").as("data");
        ProjectionOperation projection = Aggregation.project().andExclude("_id")
        .andExpression("slice(data, "+topN+")").as("data");
        //侧写 flat map
        UnwindOperation unwindOperation = Aggregation.unwind("data");
        ReplaceRootOperation replaceRoot = Aggregation.replaceRoot("data");

        // 添加一个字段my_docId // 把 docId 转为 ObjectId ， 赋值给my_docId
        //aoc -> new Document("$addFields",new Document("docId1",new Document("$toObjectId","$docId"))
        SortOperation lastSortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "data.name"));

        ProjectionOperation lastProjection = Aggregation.project().andExclude("_id");//不显示_id
        Aggregation aggregation= Aggregation.newAggregation(sortOperation,group,projection,lastSortOperation,unwindOperation,replaceRoot,lastProjection);
//        List<Map> results = mongoTemplate.aggregate(match1,aggregation,"SeaTest", Map.class).getMappedResults();
        List<Map> results = mongoTemplate.aggregate(aggregation,"SeaTest", Map.class).getMappedResults();
        System.out.println(JSON.toJSONString(results));
    }

 

 

 

 

 

结果：

[{"name":"sea5","age":5},{"name":"sea4","age":4},{"name":"sea3","age":3},{"name":"sea2","age":2},{"name":"sea1","age":4},{"name":"sea1","age":3},{"name":"sea1","age":2},{"name":"sea1","age":1},{"name":"sea1","age":2},{"name":"sea1","age":3},{"name":"sea1","age":1},{"name":"sea1","age":4},{"name":"sea1","age":4},{"name":"sea1","age":4},{"name":"sea","age":1},{"name":"sea","age":3},{"name":"sea","age":4},{"name":"sea","age":2},{"name":"sea","age":4}]