[MongoDB-查询及聚合操作]
查询关键字
并列查询$and[#]
# 条件都成立才可以查询到结果
db.stutent.find({$and:[{name:"小漩涡"},{age:30}]})
或查询$or[#]
# 有一个条件成立就可以查询到结果
db.stu.find({$or:[{name:"绿绿"},{name:"小黑"}]})
子查询$all[#]
all后面列表中的元素部分顺序,只要在test_list中存在就可以查询到所有结果
> db.stutent.find({"test_list":{$all:[1,"五"]}})
{ "_id" : ObjectId("5d2eee1314ff51d814e40365"), "name" : "小漩涡", "age" : 30, "test_list" : [ 1, 2, 3, 4, "五", 1000 ], "hobby" : [ "烫头" ] }
范围查询$in[#]
["绿绿","黑黑","红红","小黑"]
# 只要符合列表中的名字全部查找出来
db.stu.find({name:{$in:["绿绿","黑黑","小红","小黑"]}})
排序/选取/跳过
排序:sort
db.stu.find().sort({ age:1 }) 1正序 -1倒序
选取:limit
db.stu.find().limit(2) 选取两条数据
跳过:skip
db.stu.find().skip(2) 跳过前两条数据
选择中间两条 or 跳过前N条
db.stu.find().skip(0).limit(2).sort({ age:-1 })
优先级:先排序 - 跳过 - 选取
# 分页
var page = 1
var num = 2
var sk = (page-1) * num
db.stu.find().skip(sk).limit(num).sort({ age:-1 })
mongo常用查询、更新、删除等语句
注意:文中的所有 collection 代表 mongo 表名称
常用条件操作符:lt(<)、lte(<=)、eq(=)、subtract(-)、divide(/)
插入:
_id字段默认添加到文档,作为文档的主键。没个MongoDB文档都需要一个_id
db.users.insert({username:"smith"})
查询:
1、基础条件查询
db.collection.find({“type” : “test”);
2、区间查询
db.collection.find({"type":"test","addTime":{$lte:ISODate("2019-06-11T59:59:00.492+08:00"),$gte:ISODate("2019-06-12T00:00:00.492+08:00")}});
3、数组列表in查询
db.collection.find({“type”:“test”,“ids”:{$in: [1,2,3]}});
4、分页排序查询,倒序(-1),正序(1)
db.collection.find({“type”:“test”}).sort({“addTime”:-1}).skip(0).limit(2);
5、分组查询,统计type类型的age总和
db.collection.aggregate([{$group:{_id:"$type",total:{$sum:"$age"}}}]);
6、带条件的分组查询,统计name非空,type类型的age总和,这里必须使用*$group*
db.collection.aggregate([{$match:{"name":{$ne:null}}},{$group:{_id:"$type",total:{$sum:"$age"}}}]);
7、带条件的分组查询,统计type类型是test,remark不是手动生成任务的重复订单号数量大于1的列表,这里必须使用*$group*
db.collection.aggregate([{$match:{"type" : "test","remark" : {$ne:"手动生成任务"}}},{$group:{_id:"$orderNo",total:{$sum:1}}},{ $match: { total: { $gt : 1}}}]);
8、使用aggregate聚合计算查询,查询type类型是test的(age1 / age2)表达式的值,这里必须使用*$project*
db.collection.aggregate([{$match:{"type" : "test"}},{$project:{_id:"$id",sub:{{ $divide: [ "$age1", "$age2" ]}}}}]);
9、使用aggregate聚合计算查询,查询type类型是test的((age1 + age2) * (year1 - year2))表达式的值,这里必须使用*$project*
db.collection.aggregate([{$match:{"type" : "test"}},{$project:{_id:"$id",total:{$multiply:[{ $add: [ "$age1", "$age2" ]},{ $subtract: [ "$year1", "$year2" ]}]}}}]);
更新:
1、更新字段,其中属性1是条件,2是更新field,3是如果记录不存在,是否插入记录,默认是false,4是是否更新查找到的全部记录,默认是false(只更新找到的第一条数据)
db.collection.update({"_id": ObjectId('123456')},{$set:{"type":"test"}},false,true);
2、替换更新,文档被替换为只包含country字段的文档。username字段被删除,因为他只是用来匹配文档,第二个参数用来更新替换。
db.users.update({username:"smith"},{country:"Canada"})
3、使用$unset操作符删除
db.users.update({username:"smith"},{$unset:{country:1}})
4、高级更新,使用$push或者$addToSet(唯一的,阻止重复数据)给数组添加数据,实现任意喜欢Casablanca的用户也喜欢电影The Maltese Falcon
db.users.update({"favorites.movies":"Casablanca"}),
{$addToSet:{"favorites.movies":"The Maltese Falcon"}},
false,
true)
第一个参数是查询条件,匹配电影列表中包含Casablanca的用户。第二个参数使用$addToSet添加The Maltese Falcon到列表中。
{
username:"smith",
favorites:{
cities:["Chicago","Cheyenne"],
movies:["Casablanca","For a Few Dollars More","The String","The Maltese Falcon"]
}
}
5、增加索引,倒序(-1),正序(1)
db.collection.createIndex({type:1})
删除:
1、整个表删除
db.collection.drop()
2、删除某些条件的数据,删除type类型是test的数据
db.collection.remove({"type" : "test"})
索引:
explain(),一个调优和优化查询的好工具。
db.numbers.find({num:{"$gt":1995}}).explain("executionStats")
1、未使用索引的典型的explain("executionStats")输出结果
{
"queryPlanner": {
"plannerVersion": NumberInt("1"),
"namespace": "test.numbers",
"indexFilterSet": false,
"parsedQuery": {
"num": {
"$gt": 1995
}
},
"winningPlan": {
"stage": "COLLSCAN",
"filter": {
"num": {
"$gt": 1995
}
},
"direction": "forward"
},
"rejectedPlans": [ ]
},
"executionStats": {
"executionSuccess": true,
"nReturned": NumberInt("4"),
"executionTimeMillis": NumberInt("20"),
"totalKeysExamined": NumberInt("0"),
"totalDocsExamined": NumberInt("2000"),
"executionStages": {
"stage": "COLLSCAN",
"filter": {
"num": {
"$gt": 1995
}
},
"nReturned": NumberInt("4"),
"executionTimeMillisEstimate": NumberInt("11"),
"works": NumberInt("2002"),
"advanced": NumberInt("4"),
"needTime": NumberInt("1997"),
"needYield": NumberInt("0"),
"saveState": NumberInt("16"),
"restoreState": NumberInt("16"),
"isEOF": NumberInt("1"),
"direction": "forward",
"docsExamined": NumberInt("2000")
}
},
"serverInfo": {
"host": "5007451YFBPC1",
"port": NumberInt("27017"),
"version": "4.2.4",
"gitVersion": "b444815b69ab088a808162bdb4676af2ce00ff2c"
},
"ok": 1
}
totalKeysExamined字段显示了整个扫描的索引数量,他的值为0,扫描文档数totalDocsExamined为2000
2、使用createIndex()方法为num键创建索引。
>db.numbers.createIndex({num:1})
{
"createdCollectionAutomatically": false,
"numIndexesBefore": NumberInt("1"),
"numIndexesAfter": NumberInt("2"),
"ok": 1
}
通过getIndexes()方法检索索引是否创建成功:
>db.numbers.getIndexes()
[
{
"v": NumberInt("2"),
"key": {
"_id": NumberInt("1")
},
"name": "_id_",
"ns": "test.numbers"
},
{
"v": NumberInt("2"),
"key": {
"num": 1
},
"name": "num_1",
"ns": "test.numbers"
}
]
集合现有两个索引,第一个是标准的_id索引,自动为没个集合创建的;第二个是我们自己创建的num索引。这些索引的名字分别叫_id_和num_1。
用explain()进行查询,可以看到应答时间有很大变化。
>db.numbers.find({num:{"$gt":1995}}).explain("executionStats")
{
"queryPlanner": {
"plannerVersion": NumberInt("1"),
"namespace": "test.numbers",
"indexFilterSet": false,
"parsedQuery": {
"num": {
"$gt": 1995
}
},
"winningPlan": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"num": 1
},
"indexName": "num_1",
"isMultiKey": false,
"multiKeyPaths": {
"num": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"num": [
"(1995.0, inf.0]"
]
}
}
},
"rejectedPlans": [ ]
},
"executionStats": {
"executionSuccess": true,
"nReturned": NumberInt("4"),
"executionTimeMillis": NumberInt("14"),
"totalKeysExamined": NumberInt("4"),
"totalDocsExamined": NumberInt("4"),
"executionStages": {
"stage": "FETCH",
"nReturned": NumberInt("4"),
"executionTimeMillisEstimate": NumberInt("2"),
"works": NumberInt("5"),
"advanced": NumberInt("4"),
"needTime": NumberInt("0"),
"needYield": NumberInt("0"),
"saveState": NumberInt("0"),
"restoreState": NumberInt("0"),
"isEOF": NumberInt("1"),
"docsExamined": NumberInt("4"),
"alreadyHasObj": NumberInt("0"),
"inputStage": {
"stage": "IXSCAN",
"nReturned": NumberInt("4"),
"executionTimeMillisEstimate": NumberInt("2"),
"works": NumberInt("5"),
"advanced": NumberInt("4"),
"needTime": NumberInt("0"),
"needYield": NumberInt("0"),
"saveState": NumberInt("0"),
"restoreState": NumberInt("0"),
"isEOF": NumberInt("1"),
"keyPattern": {
"num": 1
},
"indexName": "num_1",
"isMultiKey": false,
"multiKeyPaths": {
"num": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": NumberInt("2"),
"direction": "forward",
"indexBounds": {
"num": [
"(1995.0, inf.0]"
]
},
"keysExamined": NumberInt("4"),
"seeks": NumberInt("1"),
"dupsTested": NumberInt("0"),
"dupsDropped": NumberInt("0")
}
}
},
"serverInfo": {
"host": "5007451YFBPC1",
"port": NumberInt("27017"),
"version": "4.2.4",
"gitVersion": "b444815b69ab088a808162bdb4676af2ce00ff2c"
},
"ok": 1
}
使用索引后只扫描了与查询有关的4个文档。索引会占用空间吗,并会让插入成本提升。
基本管理
1、获取所有数据库列表信息
show dbs
2、展示当前数据库里所有的集合
show collections
3、在数据库对象上执行stats();
>db.stats()
{
"db": "test",
"collections": NumberInt("14"),
"views": NumberInt("0"),
"objects": NumberInt("7665046"),
"avgObjSize": 469.136041192708,
"dataSize": 3595949336,
"storageSize": 796413952,
"numExtents": NumberInt("0"),
"indexes": NumberInt("16"),
"indexSize": 161972224,
"fsUsedSize": 24373190656,
"fsTotalSize": 53660876800,
"ok": 1
}
在单个集合上执行stats();
>db.event_log.stats()
{
"ns": "test.event_log",
"size": NumberInt("268546353"),
"count": NumberInt("326524"),
"avgObjSize": NumberInt("822"),
"storageSize": NumberInt("76910592"),
"capped": false,
"wiredTiger": {
"metadata": {
"formatVersion": NumberInt("1")
},
"creationString": "access_pattern_hint=none,allocation_size=4KB,app_metadata=(formatVersion=1),assert=(commit_timestamp=none,read_timestamp=none),block_allocation=best,block_compressor=snappy,cache_resident=false,checksum=on,colgroups=,collator=,columns=,dictionary=0,encryption=(keyid=,name=),exclusive=false,extractor=,format=btree,huffman_key=,huffman_value=,ignore_in_memory_cache_size=false,immutable=false,internal_item_max=0,internal_key_max=0,internal_key_truncate=true,internal_page_max=4KB,key_format=q,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=32KB,leaf_value_max=64MB,log=(enabled=true),lsm=(auto_throttle=true,bloom=true,bloom_bit_count=16,bloom_config=,bloom_hash_count=8,bloom_oldest=false,chunk_count_limit=0,chunk_max=5GB,chunk_size=10MB,merge_custom=(prefix=,start_generation=0,suffix=),merge_max=15,merge_min=0),memory_page_image_max=0,memory_page_max=10m,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=false,prefix_compression_min=4,source=,split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,type=file,value_format=u",
"type": "file",
"uri": "statistics:table:collection-543233-3575391902029516587",
"LSM": {
"bloom filter false positives": NumberInt("0"),
"bloom filter hits": NumberInt("0"),
"bloom filter misses": NumberInt("0"),
"bloom filter pages evicted from cache": NumberInt("0"),
"bloom filter pages read into cache": NumberInt("0"),
"bloom filters in the LSM tree": NumberInt("0"),
"chunks in the LSM tree": NumberInt("0"),
"highest merge generation in the LSM tree": NumberInt("0"),
"queries that could have benefited from a Bloom filter that did not exist": NumberInt("0"),
"sleep for LSM checkpoint throttle": NumberInt("0"),
"sleep for LSM merge throttle": NumberInt("0"),
"total size of bloom filters": NumberInt("0")
},
"block-manager": {
"allocations requiring file extension": NumberInt("69"),
"blocks allocated": NumberInt("459"),
"blocks freed": NumberInt("181"),
"checkpoint size": NumberInt("76869632"),
"file allocation unit size": NumberInt("4096"),
"file bytes available for reuse": NumberInt("24576"),
"file magic number": NumberInt("120897"),
"file major version number": NumberInt("1"),
"file size in bytes": NumberInt("76910592"),
"minor version number": NumberInt("0")
},
"btree": {
"btree checkpoint generation": NumberInt("10231"),
"column-store fixed-size leaf pages": NumberInt("0"),
"column-store internal pages": NumberInt("0"),
"column-store variable-size RLE encoded values": NumberInt("0"),
"column-store variable-size deleted values": NumberInt("0"),
"column-store variable-size leaf pages": NumberInt("0"),
"fixed-record size": NumberInt("0"),
"maximum internal page key size": NumberInt("368"),
"maximum internal page size": NumberInt("4096"),
"maximum leaf page key size": NumberInt("2867"),
"maximum leaf page size": NumberInt("32768"),
"maximum leaf page value size": NumberInt("67108864"),
"maximum tree depth": NumberInt("4"),
"number of key/value pairs": NumberInt("0"),
"overflow pages": NumberInt("0"),
"pages rewritten by compaction": NumberInt("0"),
"row-store internal pages": NumberInt("0"),
"row-store leaf pages": NumberInt("0")
},
"cache": {
"bytes currently in the cache": NumberInt("297695535"),
"bytes dirty in the cache cumulative": NumberInt("23372697"),
"bytes read into cache": 4177634496,
"bytes written from cache": NumberInt("3092655"),
"checkpoint blocked page eviction": NumberInt("0"),
"data source pages selected for eviction unable to be evicted": NumberInt("0"),
"eviction walk passes of a file": NumberInt("10173"),
"eviction walk target pages histogram - 0-9": NumberInt("1350"),
"eviction walk target pages histogram - 10-31": NumberInt("6604"),
"eviction walk target pages histogram - 128 and higher": NumberInt("0"),
"eviction walk target pages histogram - 32-63": NumberInt("2219"),
"eviction walk target pages histogram - 64-128": NumberInt("0"),
"eviction walks abandoned": NumberInt("630"),
"eviction walks gave up because they restarted their walk twice": NumberInt("0"),
"eviction walks gave up because they saw too many pages and found no candidates": NumberInt("2"),
"eviction walks gave up because they saw too many pages and found too few candidates": NumberInt("1"),
"eviction walks reached end of tree": NumberInt("101"),
"eviction walks started from root of tree": NumberInt("633"),
"eviction walks started from saved location in tree": NumberInt("9540"),
"hazard pointer blocked page eviction": NumberInt("0"),
"in-memory page passed criteria to be split": NumberInt("0"),
"in-memory page splits": NumberInt("0"),
"internal pages evicted": NumberInt("0"),
"internal pages split during eviction": NumberInt("0"),
"leaf pages split during eviction": NumberInt("3"),
"modified pages evicted": NumberInt("3"),
"overflow pages read into cache": NumberInt("0"),
"page split during eviction deepened the tree": NumberInt("0"),
"page written requiring cache overflow records": NumberInt("0"),
"pages read into cache": NumberInt("148821"),
"pages read into cache after truncate": NumberInt("0"),
"pages read into cache after truncate in prepare state": NumberInt("0"),
"pages read into cache requiring cache overflow entries": NumberInt("0"),
"pages requested from the cache": NumberInt("152960052"),
"pages seen by eviction walk": NumberInt("212384"),
"pages written from cache": NumberInt("287"),
"pages written requiring in-memory restoration": NumberInt("0"),
"tracked dirty bytes in the cache": NumberInt("0"),
"unmodified pages evicted": NumberInt("138859")
},
"cache_walk": {
"Average difference between current eviction generation when the page was last considered": NumberInt("0"),
"Average on-disk page image size seen": NumberInt("0"),
"Average time in cache for pages that have been visited by the eviction server": NumberInt("0"),
"Average time in cache for pages that have not been visited by the eviction server": NumberInt("0"),
"Clean pages currently in cache": NumberInt("0"),
"Current eviction generation": NumberInt("0"),
"Dirty pages currently in cache": NumberInt("0"),
"Entries in the root page": NumberInt("0"),
"Internal pages currently in cache": NumberInt("0"),
"Leaf pages currently in cache": NumberInt("0"),
"Maximum difference between current eviction generation when the page was last considered": NumberInt("0"),
"Maximum page size seen": NumberInt("0"),
"Minimum on-disk page image size seen": NumberInt("0"),
"Number of pages never visited by eviction server": NumberInt("0"),
"On-disk page image sizes smaller than a single allocation unit": NumberInt("0"),
"Pages created in memory and never written": NumberInt("0"),
"Pages currently queued for eviction": NumberInt("0"),
"Pages that could not be queued for eviction": NumberInt("0"),
"Refs skipped during cache traversal": NumberInt("0"),
"Size of the root page": NumberInt("0"),
"Total number of pages currently in cache": NumberInt("0")
},
"compression": {
"compressed pages read": NumberInt("148779"),
"compressed pages written": NumberInt("115"),
"page written failed to compress": NumberInt("0"),
"page written was too small to compress": NumberInt("172")
},
"cursor": {
"bulk-loaded cursor-insert calls": NumberInt("0"),
"close calls that result in cache": NumberInt("0"),
"create calls": NumberInt("374"),
"cursor operation restarted": NumberInt("0"),
"cursor-insert key and value bytes inserted": NumberInt("519499"),
"cursor-remove key bytes removed": NumberInt("0"),
"cursor-update value bytes updated": NumberInt("0"),
"cursors reused from cache": NumberInt("10731"),
"insert calls": NumberInt("425"),
"modify calls": NumberInt("0"),
"next calls": 3346962122,
"open cursor count": NumberInt("1"),
"prev calls": NumberInt("1"),
"remove calls": NumberInt("0"),
"reserve calls": NumberInt("0"),
"reset calls": NumberInt("26448080"),
"search calls": NumberInt("317775"),
"search near calls": NumberInt("26423409"),
"truncate calls": NumberInt("0"),
"update calls": NumberInt("0")
},
"re
命令如何执行
使用runCommand帮助方法可以更简单
db.stats()
等效与db.runCommand({dbstats:1})
db.runCommand()
示例
mongodb数据库:
alarm_log表信息:
{
"_id": ObjectId("5f3bbe31ef227a0001c50740"),
"product_key": "6af4d3af657",
"device_name": "381b9964b9b3438",
"create_time": NumberLong("1597750833151"),
"log_content": {
"id": NumberInt("373"),
"params": {
"outputData": {
"PumpFaultNum": "0,2,10",
"PumpFault": "0",
"SlotID": NumberInt("6"),
"HighestLevel": "High",
"Content": "无外部电源",
"WarningType": "分发告警",
"Time": NumberLong("1597738273825"),
"Level": "Low",
"PumpDeviceSN": "sn00005"
},
"identifier": "AlarmLog"
},
"version": "1.0.0",
"timestamp": NumberLong("1597049274321")
},
"_class": "com.medcaptain.parsedata.entity.mongodb.AlarmLog"
}
{
"_id": ObjectId("5f3f6dceef227a0001c9fa61"),
"product_key": "6af4d3af657",
"device_name": "381b9964b9b3438",
"create_time": NumberLong("1597992398229"),
"log_content": {
"id": NumberInt("92372004"),
"params": {
"outputData": {
"PumpFaultNum": "10,11,12",
"PumpFault": "12",
"SlotID": "1",
"HighestLevel": "High",
"Content": "输注阻塞",
"WarningType": "分发告警",
"Time": NumberInt("3881"),
"Level": "High",
"PumpDeviceSN": "sn0001"
},
"identifier": "AlarmLog"
},
"version": "1.0.0",
"timestamp": NumberLong("1597992372004")
},
"_class": "com.medcaptain.parsedata.entity.mongodb.AlarmLog"
}
event_log表信息:
{
"_id": ObjectId("5f3f7643ef227a000173b143"),
"product_key": "6af4d3af657",
"device_name": "381b9964b9b3438",
"create_time": "Fri Aug 21 15:22:43 CST 2020",
"log_content": {
"id": NumberInt("94559007"),
"params": {
"outputData": {
"PumpPressureUnit": "3",
"PumpInfusionConcentration": NumberInt("34564355"),
"PumpInfusionSpeed": "bcfd6f0aeddd41f296077415d0c1e339",
"WorkstationDeviceSN": "20200812",
"PumpInfusionDoseRateUnit": "35",
"Department": "782ebf4544f846cf932390f3d27d5161",
"PumpInfusionDrugName": "63d84690e71e4f46b04524e7e4bcb80b",
"PumpInfusionRemainTime": "c307e9247bbb4324b6b3979776011e76",
"PumpSyringSize": "ce2cabb68a914b139b728cb74d05e2e0",
"PumpDeviceType": NumberInt("2"),
"PumpStatus": "3",
"Room": "a551d6ead01f47f2aff734ffaa364323",
"WorkMode": "0",
"BedNumber": "4636567568704b6fa24755aefab6a9c4",
"PumpInfusionRemain": "4169.5",
"PumpDeviceModel": NumberInt("2"),
"PumpDeviceSN": "sn0001",
"PumpFaultNum": "10,11,12",
"PatientWeight": "78ef5bd1aa62453fb6d48cef95fb10a3",
"PumpDlotID": "1",
"PumpInfusionConcentrationUnit": "11",
"PumpInfusionTime": "9ba6557ade6e4ec78be40634e7db9675",
"PumpInfusionSum": "4f6d902bb226489a81ec6eeb7bf29bf4",
"PumpPressureVal": "255",
"PumpInfusionDrugID": "d82ca7c01f624c31b525f1a896a4b066",
"PumpInfusionDoseRate": "d9c0e510a90341a5b4e3879c6040d0bb",
"PumpInfusionTotal": "4443.3"
},
"identifier": "Pump"
},
"version": "1.0.0",
"timestamp": NumberLong("1597994559007")
},
"organization_id": "",
"department_id": "",
"_class": "com.medcaptain.parsedata.entity.mongodb.EventLog"
}
关联查询:根据produce_key,device_name查询故障泵列表(包括故障泵SN,故障泵Model)
db.alarm_log.aggregate([
{$match:{"product_key": "6af4d3af657","device_name": "381b9964b9b3438","log_content.params.outputData.WarningType":"分发告警"}},
{$group:{_id: "$log_content.params.outputData.PumpDeviceSN",time:{$max:"$log_content.timestamp"}}},
{$lookup:{
from: "event_log",
localField: "_id",
foreignField: "log_content.params.outputData.PumpDeviceSN",
as: "inventory_docs"
}
},
{$unwind:"$inventory_docs"},
{$match:{"inventory_docs.log_content.params.identifier":"Pump"}},
{$project:{"_id":1,"inventory_docs.log_content.params.outputData.PumpDeviceModel":1,"time":1}},
{$group:{_id:"$_id",time:{$max:"$time"},PumpDeviceModel:{$first:"$inventory_docs.log_content.params.outputData.PumpDeviceModel"}}},
{$sort:{"time":-1}}
])
解释:
- 在
alarm_log
表中 $lookup
命令- 在外表
alarm_log
表中 - 查本地字段 _id 等于 外表字段
log_content.params.outputData.PumpDeviceSN
(alarm_log._id == event_log.log_content.params.outputData.PumpDeviceSN
)的 document - 作为
inventory_docs
对象加入 stage (这个 inventory_docs 是个数组)
- 在外表
$match
: 匹配所有 stage 内中的 document (可以删除此行)$unwind
:由于inventory_docs
对象存的是数组(这里是唯一匹配,所以一定只有一个元素),将它展开出来。(此时 inventory_docs 本来没有元素的 document 会消失)$project
:重组此查询的字段名- 格式为:
showField: $originalField
showField: 0
表示按字段名隐藏,1 为显示(此时字段不重命名)。
- 格式为:
$group
命令- 按 document 内字段
_id
进行分组命名为_id
字段,将 doc 内time
字段的最大值max放到time
内
- 按 document 内字段
结果:
// 1
{
"_id": "sn0006",
"time": NumberLong("1597994563007"),
"PumpDeviceModel": NumberInt("2")
}
// 2
{
"_id": "sn0001",
"time": NumberLong("1597994562006"),
"PumpDeviceModel": NumberInt("12")
}
// 3
{
"_id": "sn00005",
"time": NumberLong("1597811155691"),
"PumpDeviceModel": "Hp-60-test"
}
对应的java代码:
public AggregationResults<JSONObject> findPumpDeviceSNList(ParamEntity paramEntity) {
Criteria criteria = CommonAlarmLog(paramEntity);
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.match(criteria), //第一次筛选alarm_log中信息
Aggregation.group("log_content.params.outputData.PumpDeviceSN").max("log_content.timestamp").as("time"), //去重
Aggregation.lookup("event_log","_id","log_content.params.outputData.PumpDeviceSN","inventory_docs"),//关联event_log表
Aggregation.unwind("inventory_docs"), //表示为非数组结构
Aggregation.match(Criteria.where("inventory_docs.log_content.params.identifier").is("Pump")), //筛选event_log中identifier为Pump的信息
Aggregation.project("_id","inventory_docs.log_content.params.outputData.PumpDeviceModel","time"), //只显示SN和Model
Aggregation.group("_id").first("log_content.params.outputData.PumpDeviceModel").as("PumpDeviceModel").max("time").as("time"),//再次去重
Aggregation.sort(Sort.Direction.DESC,"time")
);
AggregationResults<JSONObject> alarmLogEntity = mongoTemplate.aggregate(aggregation, "alarm_log", JSONObject.class);
return alarmLogEntity;
}
private Criteria CommonAlarmLog(ParamEntity paramEntity) {
//查询标识符限定
if (paramEntity == null) {
return null;
}
Criteria criteria;
if (!StringUtils.isEmpty(paramEntity.getIdentifier())) {
criteria = Criteria.where("log_content.params.identifier").is(paramEntity.getIdentifier());
} else {
return null;
}
if (!StringUtils.isEmpty(paramEntity.getProductKey())) {
criteria = criteria.and("product_key").is(paramEntity.getProductKey());
}
if (!StringUtils.isEmpty(paramEntity.getDeviceName())) {
criteria = criteria.and("device_name").is(paramEntity.getDeviceName());
}
if (paramEntity.getParamKV() != null) {
for (Map.Entry<String, Object> param : paramEntity.getParamKV().entrySet()) {
if (!StringUtils.isEmpty(param.getKey())) {
criteria = criteria.and("log_content.params.outputData." + param.getKey()).is(param.getValue());
}
}
}
//差集
if (paramEntity.getParamNameAnd() != null) {
for (String param : paramEntity.getParamNameAnd()) {
if (!StringUtils.isEmpty(param)) {
criteria = criteria.and("log_content.params.outputData." + param).exists(true);
}
}
}
//查询时间限定
if (paramEntity.getStartTime() != null && paramEntity.getEndTime() != null) {
criteria.andOperator(Criteria.where("log_content.timestamp").lt(paramEntity.getEndTime()).gt(paramEntity.getStartTime()));
}
return criteria;
}