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

Alex_z0897

已于 2024-07-23 16:31:18 修改

阅读量5.6k

点赞数 4

分类专栏： java 文章标签： spring mongodb

于 2021-11-11 10:03:39 首次发布

本文链接：https://blog.csdn.net/zoeou/article/details/121261062

版权

java 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

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

统计求和转换字符串为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"));

排序|倒序

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

分页

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

count

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

mongoTemplate.count(new Query(), collectionName)

查询子文档

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

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

先排序后分组

以上是源数据

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());

查询子文档里的数组

方式一

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

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

方式二

// 数组内为非对象情况
query.addCriteria(Criteria.where("request_log_list").elemMatch(Criteria.where("$eq").is(projectId)));

// 数组内为对象情况
query.addCriteria(Criteria.where("request_log_list").elemMatch(Criteria.where("account_name").is(projectId)));

# 数组内为对象情况
db.getCollection('request_log').find({request:{$elemMatch: {account_name:"xxx"}}})

删除操作

$ 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

通过判断_id字段,按时间大小删除数据
参考: https://blog.csdn.net/chenyulancn/article/details/83585860

$ db.getCollection('source_kye').find({'is_close':1}).count()
89238
$ db.getCollection('source_kye').remove({'is_close':1})
Removed 89238record(s) in 919ms

$ db.getCollection('source_kye').find({status_list: null}).count()
$ db.getCollection('source_kye').find({status_list: {$ne:null}}).count()
4384
function objectIdWithTimestamp(timestamp) {
     // Convert string date to Date object (otherwise assume timestamp is a date)
     if (typeof(timestamp) == 'string') {
         timestamp = new Date(timestamp);
     }
     // Convert date object to hex seconds since Unix epoch
     var hexSeconds = Math.floor(timestamp/1000).toString(16);
     // Create an ObjectId with that hex timestamp
     var constructedObjectId = ObjectId(hexSeconds + "0000000000000000");
     return constructedObjectId
 }

$ db.getCollection('source_kye').find({status :null, _id: { $lt: objectIdWithTimestamp('2022/11/01') } }).count()
63086
$ db.getCollection('source_kye').remove({status :null, _id: { $lt: objectIdWithTimestamp('2022/11/01') } })
Removed 63086 record(s) in 919ms

增加认证

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

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

like查询

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

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})

多行取字段合并为一行

db.getCollection('_log').aggregate([
{ "$match" : { "config_type" : 1000}},
  {  
    $group: {  
      _id: null,  
      list: {  
        $push: "$data"
      }  
    }  
  }  
])

db.getCollection('_log').aggregate([
{ "$match" : { "config_type" : 1000}},
  {  
    $group: {  
      _id: null,  
      list: {  
        $addToSet: "$data"  //去重
      }  
    }  
  }  
])

{
    "_id" : null,
    "list" : [ 
        209581, 
        217661, 
        198566, 
        214106
   ]
}

db.getCollection('task_log').aggregate([
{ "$match" : { "config_type" : 1047}},
{ "$project" :  { _id:0, "create_time" : 1, waybill_id: "$data.metadata.waybill_id",order_no: "$data.settle_operation.order_no"}}
])

查看活动连接数
db.serverStatus().connections

{
    "bits" : 64,
    "resident" : 111,
    "virtual" : 1513,
    "supported" : true
}
这些字段代表MongoDB实例当前的内存使用情况：bits表示内存位数；resident表示实际使用的内存量；virtual表示虚拟内存量；supported表示是否支持64位内存。

查看内存使用量
db.serverStatus().mem

查看受支持的参数
db.adminCommand({getParameter: 1, help:true})

wiredTigerConcurrentReadTransactions：WiredTiger引擎允许的最大并发读取事务数。
wiredTigerConcurrentWriteTransactions：WiredTiger引擎允许的最大并发写入事务数。
wiredTigerCursorCacheSize：WiredTiger引擎使用的鼠标缓存大小，以字节为单位。
wiredTigerEngineRuntimeConfig：WiredTiger引擎运行时配置参数。
wiredTigerFileHandleCloseIdleTime：WiredTiger引擎关闭空闲文件句柄的时间，以秒为单位。
wiredTigerFileHandleCloseMinimum：WiredTiger引擎关闭文件句柄的最小数量。
wiredTigerFileHandleCloseScanInterval：WiredTiger引擎每次检查关闭文件句柄的时间间隔，以秒为单位。
wiredTigerMaxCacheOverflowSizeGB：WiredTiger引擎允许的最大缓存溢出大小，以GB为单位。
wiredTigerSessionCloseIdleTimeSecs：WiredTiger引擎关闭空闲会话的时间，以秒为单位。
wiredTigerSkipTableLoggingChecksOnStartup：WiredTiger引擎启动时是否跳过表日志检查。

db.serverStatus().wiredTiger
获取有关WiredTiger引擎的信息

附: springboot+mongo

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

启动时报错如下:

{"c":"ASIO",     "id":22601,   "ctx":"main","msg":"No TransportLayer configured during NetworkInterface startup"}
{"c":"REPL",     "id":5123008, "ctx":"main","msg":"Successfully registered PrimaryOnlyService","attr":{"service":"TenantMigrationDonorService","ns":"config.tenantMigrationDonors"}}
{"c":"REPL",     "id":5123008, "ctx":"main","msg":"Successfully registered PrimaryOnlyService","attr":{"service":"TenantMigrationRecipientService","ns":"config.tenantMigrationRecipients"}}
{"c":"CONTROL",  "id":5945603, "ctx":"main","msg":"Multi threading initialized"}
{"c":"CONTROL",  "id":4615611, "ctx":"initandlisten","msg":"MongoDB starting","attr":{"pid":1,"port":27017,"dbPath":"/data/db","architecture":"64-bit","host":"1e55e63ea84c"}}
{"c":"CONTROL",  "id":23403,   "ctx":"initandlisten","msg":"Build Info","attr":{"buildInfo":{"version":"5.0.9","gitVersion":"6f7dae919422dcd7f4892c10ff20cdc721ad00e6","openSSLVersion":"OpenSSL 1.1.1f  31 Mar 2020","modules":[],"allocator":"tcmalloc","environment":{"distmod":"ubuntu2004","distarch":"x86_64","target_arch":"x86_64"}}}}
{"c":"CONTROL",  "id":51765,   "ctx":"initandlisten","msg":"Operating System","attr":{"os":{"name":"Ubuntu","version":"20.04"}}}
{"c":"CONTROL",  "id":21951,   "ctx":"initandlisten","msg":"Options set by command line","attr":{"options":{"net":{"bindIp":"172.16.0.11,127.0.0.1"}}}}
{"c":"CONTROL",  "id":20568,   "ctx":"initandlisten","msg":"Error setting up listener","attr":{"error":{"code":9001,"codeName":"SocketException","errmsg":"Cannot assign requested address"}}}
{"c":"REPL",     "id":4784900, "ctx":"initandlisten","msg":"Stepping down the ReplicationCoordinator for shutdown","attr":{"waitTimeMillis":15000}}
{"c":"COMMAND",  "id":4784901, "ctx":"initandlisten","msg":"Shutting down the MirrorMaestro"}
{"c":"SHARDING", "id":4784902, "ctx":"initandlisten","msg":"Shutting down the WaitForMajorityService"}
{"c":"NETWORK",  "id":4784905, "ctx":"initandlisten","msg":"Shutting down the global connection pool"}
{"c":"NETWORK",  "id":4784918, "ctx":"initandlisten","msg":"Shutting down the ReplicaSetMonitor"}
{"c":"SHARDING", "id":4784921, "ctx":"initandlisten","msg":"Shutting down the MigrationUtilExecutor"}
{"c":"ASIO",     "id":22582,   "ctx":"MigrationUtil-TaskExecutor","msg":"Killing all outstanding egress activity."}
{"c":"COMMAND",  "id":4784923, "ctx":"initandlisten","msg":"Shutting down the ServiceEntryPoint"}
{"c":"CONTROL",  "id":4784925, "ctx":"initandlisten","msg":"Shutting down free monitoring"}
{"c":"CONTROL",  "id":4784927, "ctx":"initandlisten","msg":"Shutting down the HealthLog"}
{"c":"CONTROL",  "id":4784928, "ctx":"initandlisten","msg":"Shutting down the TTL monitor"}
{"c":"CONTROL",  "id":4784929, "ctx":"initandlisten","msg":"Acquiring the global lock for shutdown"}
{"c":"-",        "id":4784931, "ctx":"initandlisten","msg":"Dropping the scope cache for shutdown"}
{"c":"FTDC",     "id":4784926, "ctx":"initandlisten","msg":"Shutting down full-time data capture"}
{"c":"CONTROL",  "id":20565,   "ctx":"initandlisten","msg":"Now exiting"}
{"c":"CONTROL",  "id":23138,   "ctx":"initandlisten","msg":"Shutting down","attr":{"exitCode":48}}
{"c":"NETWORK",  "id":4915701, "ctx":"-","msg":"Initialized wire specification","attr":{"spec":{"incomingExternalClient":{"minWireVersion":0,"maxWireVersion":13},"incomingInternalClient":{"minWireVersion":0,"maxWireVersion":13},"outgoing":{"minWireVersion":0,"maxWireVersion":13},"isInternalClient":true}}}
{"c":"CONTROL",  "id":23285,   "ctx":"main","msg":"Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'"}
{"c":"ASIO",     "id":22601,   "ctx":"main","msg":"No TransportLayer configured during NetworkInterface startup"}
{"c":"NETWORK",  "id":4648601, "ctx":"main","msg":"Implicit TCP FastOpen unavailable. If TCP FastOpen is required, set tcpFastOpenServer, tcpFastOpenClient, and tcpFastOpenQueueSize."}
{"c":"ASIO",     "id":22601,   "ctx":"main","msg":"No TransportLayer configured during NetworkInterface startup"}
{"c":"REPL",     "id":5123008, "ctx":"main","msg":"Successfully registered PrimaryOnlyService","attr":{"service":"TenantMigrationDonorService","ns":"config.tenantMigrationDonors"}}
{"c":"REPL",     "id":5123008, "ctx":"main","msg":"Successfully registered PrimaryOnlyService","attr":{"service":"TenantMigrationRecipientService","ns":"config.tenantMigrationRecipients"}}
{"c":"CONTROL",  "id":5945603, "ctx":"main","msg":"Multi threading initialized"}
{"c":"CONTROL",  "id":4615611, "ctx":"initandlisten","msg":"MongoDB starting","attr":{"pid":1,"port":27017,"dbPath":"/data/db","architecture":"64-bit","host":"1e55e63ea84c"}}
{"c":"CONTROL",  "id":23403,   "ctx":"initandlisten","msg":"Build Info","attr":{"buildInfo":{"version":"5.0.9","gitVersion":"6f7dae919422dcd7f4892c10ff20cdc721ad00e6","openSSLVersion":"OpenSSL 1.1.1f  31 Mar 2020","modules":[],"allocator":"tcmalloc","environment":{"distmod":"ubuntu2004","distarch":"x86_64","target_arch":"x86_64"}}}}
{"c":"CONTROL",  "id":51765,   "ctx":"initandlisten","msg":"Operating System","attr":{"os":{"name":"Ubuntu","version":"20.04"}}}
{"c":"CONTROL",  "id":21951,   "ctx":"initandlisten","msg":"Options set by command line","attr":{"options":{"net":{"bindIp":"172.16.0.11,127.0.0.1"}}}}
{"c":"CONTROL",  "id":20568,   "ctx":"initandlisten","msg":"Error setting up listener","attr":{"error":{"code":9001,"codeName":"SocketException","errmsg":"Cannot assign requested address"}}}
{"c":"REPL",     "id":4784900, "ctx":"initandlisten","msg":"Stepping down the ReplicationCoordinator for shutdown","attr":{"waitTimeMillis":15000}}
{"c":"COMMAND",  "id":4784901, "ctx":"initandlisten","msg":"Shutting down the MirrorMaestro"}
{"c":"SHARDING", "id":4784902, "ctx":"initandlisten","msg":"Shutting down the WaitForMajorityService"}
{"c":"NETWORK",  "id":4784905, "ctx":"initandlisten","msg":"Shutting down the global connection pool"}
{"c":"NETWORK",  "id":4784918, "ctx":"initandlisten","msg":"Shutting down the ReplicaSetMonitor"}
{"c":"SHARDING", "id":4784921, "ctx":"initandlisten","msg":"Shutting down the MigrationUtilExecutor"}
{"c":"ASIO",     "id":22582,   "ctx":"MigrationUtil-TaskExecutor","msg":"Killing all outstanding egress activity."}
{"c":"COMMAND",  "id":4784923, "ctx":"initandlisten","msg":"Shutting down the ServiceEntryPoint"}
{"c":"CONTROL",  "id":4784925, "ctx":"initandlisten","msg":"Shutting down free monitoring"}
{"c":"CONTROL",  "id":4784927, "ctx":"initandlisten","msg":"Shutting down the HealthLog"}
{"c":"CONTROL",  "id":4784928, "ctx":"initandlisten","msg":"Shutting down the TTL monitor"}
{"c":"CONTROL",  "id":4784929, "ctx":"initandlisten","msg":"Acquiring the global lock for shutdown"}
{"c":"-",        "id":4784931, "ctx":"initandlisten","msg":"Dropping the scope cache for shutdown"}
{"c":"FTDC",     "id":4784926, "ctx":"initandlisten","msg":"Shutting down full-time data capture"}
{"c":"CONTROL",  "id":20565,   "ctx":"initandlisten","msg":"Now exiting"}
{"c":"CONTROL",  "id":23138,   "ctx":"initandlisten","msg":"Shutting down","attr":{"exitCode":48}}

解决: 去掉ip地址绑定

    restart: always^M
    volumes:^M
      - ${PWD}/mongo:/data/db^M
      - /etc/localtime:/etc/localtime^M
        #command: mongod --bind_ip 172.16.0.11,127.0.0.1