MongoDB-查询及聚合操作

1,145 阅读1分钟

[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 表名称

常用条件操作符:gt(>)gt(>)、lt(<)、gte(>=)gte(>=)、lte(<=)、ne(!=)ne(!=)、eq(=)、add(+)add(+)、subtract(-)、multiply()multiply(*)、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

结果:

// 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;
    }