spring-mongoDB mongoTemplate排序分组|分页|统计

包含一些mongo的常用操作：统计求和，常规排序，分页，查询子文档，查询子文档的数组，排序后分组，按条件删除等

1. 统计求和转换字符串为double

db.getCollection('receipt_info').aggregate([{ "$match" : { "batch_no" : "GXNC2S8D"}} , { "$group" : { "_id" : null, "total" : { "$sum" : {"$toDouble":"$logs_dispatch.bidPrice"} }}}  ])

ConvertOperators.ToDouble.toDouble 就因为这个对象结构,折腾好了一会儿
https://docs.mongodb.com/manual/reference/operator/aggregation/toDouble/

		String filed = "$info.price"
		List<AggregationOperation> operations = new ArrayList<>();
        operations.add(Aggregation.match(criteria));//查询条件
        operations.add(Aggregation.group().sum( ConvertOperators.ToDouble.toDouble(filed)).as("total"));//字段字符串转换double
        Aggregation aggregation = Aggregation.newAggregation(operations);
        AggregationResults<BasicDBObject> obj = mongoTemplate.aggregate(aggregation,collectionName, BasicDBObject.class);
        log.info( obj.getMappedResults().get(0).getDouble("total"));

2. 排序|倒序

db.getCollection('waybill_info').find({"transport.project_id":"1970324836974592"}).sort({"id": -1})

3. 分页

query.skip((pageIndex - 1) * pageSize).limit(pageSize);
mongoTemplate.find(query, JSONObject.class, coll)

4. count

db.getCollection('receipt_info').count()

mongoTemplate.count(new Query(), collectionName)

5. 查询子文档

Query: { "id" : 54, "transport.plate_number" : "889JK"}, Fields: {}, Sort: {}

new Query(Criteria.where("id").is(54) .and("transport.plate_number").is("889JK"))

6. 先排序后分组

   
   
   以上是源数据

db.getCollection('task_log_record').aggregate(
[
  { "$match" : { "configType" : 3000, "businessId" : { '$in': ['155','187']}}}, #筛选条件
  { "$group" : { "_id" : "$businessId", # 分组条件
      "businessId" : { "$first" : "$businessId"}, #要取的字段列表
      "configType" : { "$first" : "$configType"},
      "config" : { "$first" : "$config"},
      "createTime" : { "$first" : "$createTime"},
      "ret" : { "$first" : "$ret"},
      "b_id" : { "$first" : "$_id"},
      "failTimes" : { "$first" : "$failTimes"},
      "createTime" : { "$first" : "$createTime"}
    }
  },
  { "$project" :  { "createTime" : 1, "b_id" : 1, "createTime" : 1, "failTimes" : 1, "config" : 1, "ret" : 1, "businessId" : 1, "configType" : 1  } },
  { "$sort" : { "createTime" : -1}}, #排序
 ]
)

执行之后,得到如下结果:

db.getCollection('task_log_record').aggregate(
[
  { "$match" : { "configType" : 3000, "businessId" : { '$in': ['155','187']}}},
  { "$sort" : { "createTime" : -1}}, #把排序条件上移
  { "$group" : { "_id" : "$businessId",
      "businessId" : { "$first" : "$businessId"},
      "configType" : { "$first" : "$configType"},
      "config" : { "$first" : "$config"},
      "createTime" : { "$first" : "$createTime"},
      "ret" : { "$first" : "$ret"},
      "b_id" : { "$first" : "$_id"},
      "failTimes" : { "$first" : "$failTimes"},
      "createTime" : { "$first" : "$createTime"}
    }
  },
  { "$project" :
    { "createTime" : 1, "b_id" : 1, "createTime" : 1, "failTimes" : 1, "config" : 1, "ret" : 1, "businessId" : 1, "configType" : 1
    }
  },
 ]
)

先排序,再分组,得到如下想要的结果

最后代码

		List<AggregationOperation> aggregationOperationList =  Arrays.asList(
                TypedAggregation.match(Criteria.where("configType").is(3000)
                			.and("businessId").in("155","187")),//筛选条件
                TypedAggregation.sort(Sort.by(Sort.Order.desc("createTime"))),//排序字段
                TypedAggregation.group("businessId")//分组过滤条件，
                        .first("businessId").as("businessId")//first，as里最后包含展示的字段
                        .first("config.type").as("configType")
                        .first("config").as("config")
                        .first("createTime").as("createTime")
                        .first("ret").as("ret")
                        .first("_id").as("b_id")
                        .first("failTimes").as("failTimes"),
                TypedAggregation.project("b_id","createTime","failTimes","config","ret","businessId","configType")//在first里挑选需要字段
        );
        TypedAggregation<JSONObject> tagg = TypedAggregation.newAggregation(JSONObject.class,aggregationOperationList);
        AggregationResults<JSONObject> result = mongoTemplate.aggregate(tagg,"task_log_record", JSONObject.class);
        List<JSONObject> rd =  result.getMappedResults();
        System.out.println(rd.size());

7. 查询子文档里的数组

//拆分内嵌文档(将数组中的每一个元素转为每一条文档)
//在aggregationOperationList 加入数组字段名
TypedAggregation.unwind("record");

参考文档 https://imicrocode.com/mongo-aggregation/

8. 删除操作

$ db.getCollection('request_log').find({'response':{$ne:200}}).count() //删除之前,查询条件是否正确(重要重要)
$ db.getCollection('request_log').remove({'response':{$ne:200}})  //删除response != 200

$ db.getCollection('request_log').find({'create_time':{$gt: new Date("2022-03-15 08:38:31.093Z"), $lt: new Date('2022-06-01 04:16:48.894Z')}}).count()
11336
$ db.getCollection('request_log').remove({'create_time':{$gt: new Date("2022-03-15 08:38:31.093Z"), $lt: new Date('2022-06-01 04:16:48.894Z')}})
Removed 11336 record(s) in 192ms
$ db.getCollection('business_log').remove({"order":null})  //删除order=null

9. 增加认证

   
   报错command find requires authentication
   加yml里加个"权限域"的字段authenticationDatabase

https://blog.csdn.net/kzc5335475/article/details/118346614

10. like查询

db.getCollection('request_log').find({response: /武xx/}).sort({'_id':-1})

11. update操作

    
    $in: in查找
    $set : 修改对应的值
    NumberInt(200): 数值会被mongo自动识别为double,这里转换为int
    multi : 同时修改多条

db.getCollection('request_log').update({"order_no":{"$in":[
"T2208031444108610852",
"T2208031444117814447",
    ]}},{$set:{"result":NumberInt(200)}},{multi:true})

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