MongoDB复杂查询

禛陌

已于 2024-08-06 16:41:33 修改

阅读量3.2k

点赞数

文章标签： mongodb 数据库 nosql

于 2019-04-10 13:40:52 首次发布

本文链接：https://blog.csdn.net/xuxiannian/article/details/89181033

版权

MongoDB查询高级用法

字符串按下标截取赋给新字段

db.getCollection("cmss.fan_association_setting").find({}).forEach(
    function(e){      
            e.mineCode=e.firstFan.substr(0,8);
             print(e.mineCode);
            db.getCollection("cmss.fan_association_setting").save(e);       
    })

分组查询

按照设备名称去做分组查询，并统计个数，如下图所示：

在这里插入图片描述


查询中用到的语句说明如下所示：
$aggregate:用于聚合用，mongoDB中聚合(aggregate)主要用于处理数据(诸如统计平均值,求和等)，并返回计算后的数据结果。有点类似sql语句中的 count(*)。
$match：用于过滤数据用，相当于where
$group:用于分组，相当于sql中的group by ，可用于分组统计
$project：相当于指定查询的列.
$$replaceRoot: 可以把子文档提升合并
{ $replaceRoot: { newRoot: <replacementDocument> } }

多表关联查询

在这里插入图片描述

查询两个表中所有信息

在这里插入图片描述

用到语句说明如下所示：

$lookup:用于关联表查询用，相当于SQL的left join，具体用法及属性如下：
{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}
注：管道函数中限制了fieldName不能以$开始，所以不能用$id
$addFileds:通过字面含义就是，填加字段
$arrayElemAt: 返回数组中指定下标的元素，用法为：  { $arrayElemAt: [ <array>, <idx> ] }
eg: $arrayElemAt: [ [ 1, 2, 3 ], 0 ] }	 结果返回1

创建索引

db.getCollection("tableNameForZL").createIndex({ "deviceNo": 1, "mineCode": 1, "alarmType": 1 }, { "unique": true, "dropDups": true, "background": true })
db.getCollection("cmss.dev_realtime_value").createIndex({ "deviceNo": 1, "mineCode": 1, "realTime": 1 }, { "name": "index_deviceNo_mineCode_realTime", "unique": true, "dropDups": true, "background": true })
db.getCollection("cmss.dev_realtime_alarm").dropIndex("deviceNo_1_mineCode_1_alarmType_1")

更新字段名称

db.getCollection('test.zl').update({}, {$rename : {"substationCode" : "deviceNo"}}, false, true)
  db.getCollection('cmss.dev_realtime_alarm').update({substationId:{$eg:null}}, {$rename : {"substationId" : "deviceNo"}}, false, true)


参数说明：

criteria：查询条件

objNew：update对象和一些更新操作符

upsert：如果不存在update的记录，是否插入objNew这个新的文档，true为插入，默认为false，不插入。

multi：默认是false，只更新找到的第一条记录。如果为true，把按条件查询出来的记录全部更新。

批量复制字段信息

   db.getCollection("test.zl").find().forEach(
    function(item){
       db.getCollection("test.zl").update({},{$set:{"testMineCode":item.age}},{multi: 1})
    }
)

  db.getCollection("cmss.dev_realtime_alarm").find({status:{$eq:null}}).forEach(
    function(item){
       db.getCollection("cmss.dev_realtime_alarm").update({status:{$eq:null}},{$set:{"status":item.alarmType}},{multi: 1})
    }
)

  db.getCollection("cmss.dev_realtime_alarm").find({alarmType:{$eq:null}}).forEach(
    function(item){
       db.getCollection("cmss.dev_realtime_alarm").update({alarmType:{$eq:null}},{$set:{"alarmType":item.status}},{multi: 1})
    }
)

批量更新字段

把分站ID改成deviceNo
db.getCollection('cmss.dev_realtime_alarm').update({deviceNo:{$eq:null}}, {$rename : {"substationId" : "deviceNo"}}, false, true)
查询status为null的
db.getCollection("cmss.dev_realtime_alarm").find({status:{$eq:null}});
填 冲status
 db.getCollection("cmss.dev_realtime_alarm").find({status:{$eq:null}}).forEach(
    function(item){
       db.getCollection("cmss.dev_realtime_alarm").update({status:{$eq:null},"_id":item._id},{$set:{"status":item.alarmType}},{multi: 1})
    }
)
查询alarmType为null的
db.getCollection("cmss.dev_realtime_alarm").find({alarmType:{$eq:null}});
把alarm为NULL的回写status
 db.getCollection("cmss.dev_realtime_alarm").find({alarmType:{$eq:null}}).forEach(
    function(item){
       db.getCollection("cmss.dev_realtime_alarm").update({alarmType:{$eq:null},"_id":item._id},{$set:{"alarmType":item.status}},{multi: 1})
    }
)

创建索引

db.getCollection("cmss.dev_realtime_alarm").createIndex({ "deviceNo": 1, "mineCode": 1, "status": 1 }, { "name": "index_deviceNo_mineCode_status", "unique": true, "dropDups": true, "background": true })

删除重复数据

db.getCollection("cmss.dev_realtime_alarm").aggregate([
    {
        $group: {
            _id: {mineCode: '$mineCode',deviceNo:'$deviceNo',status:'$status'},
            count: {$sum: 1},
            dups: {$addToSet: '$_id'}}
    },
    {
        $match: {count: {$gt: 1}}
    }  
], { allowDiskUse: true }).forEach(function(doc){
    doc.dups.shift();
    db.getCollection("cmss.dev_realtime_alarm").remove({_id:{$in:doc.dups}});
    })
注意：当查询的数据量过大时，Mongo会报一个Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskUse:true to opt in.，这时需要加上{ allowDiskUse: true }，如果量不大可以去掉此属性

字段截取拼接后赋给新的字段


db.getCollection("cmss.device").find({}).forEach(function(e){
    if(e.deviceCategory=="substation"){
        db.getCollection("cmss.device").update({"_id":e._id},{$set:{"substationCode":e.deviceNo}});
    }else{
        var substatioCode=e.deviceNo.substr(0,3)+"F"
      db.getCollection("cmss.device").update({"_id":e._id},{$set:{"substationCode":substatioCode}});

    }
    
})

删除某一列

db.getCollection("cmss.alarm_measure_approval").update({},{$unset:{"_class":''}},false,true)

把一列值赋给新的一列

db.getCollection("zl.test").find().forEach(
    function(item){
        db.getCollection("test").update({alarmStartTime:null},{
            $set:{"startTime":item.faultStartTime}
        })
    }
    )

批量删除集合

use backend
var colls = db.getCollectionNames();
for(var i = 0; i < colls.length; i++){
     if(/cmss\.his\_201907\_*/.test(colls[i])){
         db.runCommand({"drop" : colls[i]});
       }
}

use backend
var colls = db.getCollectionNames();
for(var i = 0; i < colls.length; i++){
   
     if(/cmss\.\d*X{0,1}\_202009\_*/.test(colls[i])){
        print(colls[i])
        db.runCommand({"drop" : colls[i]});

         
       }
}
print('===执行完成====');

修改字段名和转换数据类型

db.getCollection("cmss.dictionary").update({}, 
    {$rename:{"deviceCategory":"category"}}, 
    { multi: true, upsert: false}
)

db.getCollection("cmss.dictionary").find({"type":"areaType"}).forEach( function (x) {
  x.order= NumberInt (x.order);
  db.getCollection("cmss.dictionary").save(x);
});

把字符串转成日期格式

db.getCollection("dev_his_alarm").aggregate(
    {"$match":{"status":{"$in":["005"]}}},
    {"$project":{"mineCode":1,"deviceNo":1,"status":1,"fst":{$dateFromString:{dateString:"$faultStartTime"}},"faultStartTime":1,"faultEndTime":1,"faultTimeDiff":{"$subtract":[{$dateFromString:{dateString:"$faultEndTime"}},{$dateFromString:{dateString:"$faultStartTime"}}]}}},
    {"$group":{"_id":{"mineCode":"$mineCode","deviceNo":"$deviceNo"},"faultCounter":{"$sum":1},"timeDuraton":{"$sum":"$faultTimeDiff"}}}    
)

重命名表名且创建索引与动态拼表名创建索引

db.cmss.device.renameCollection("cmss.device_1");
db.getCollection("cmss.device").createIndex({ "mineCode": 1, "deviceNo": 1}, { "name": "index_mineCode_deviceNo", "unique": true, "dropDups": true, "background": true })

db.cmss.dev_realtime_value.renameCollection("cmss.dev_realtime_value_1");
db.getCollection("cmss.dev_realtime_value").createIndex({ "mineCode": 1,"deviceNo": 1,  "status": 1,"realTime":1 }, { "name": "index_mineCode_deviceNo_status_realTime", "unique": true, "dropDups": true, "background": true })

db.cmss.dev_realtime_alarm.renameCollection("cmss.dev_realtime_alarm_1");
db.getCollection("cmss.dev_realtime_alarm").createIndex({ "mineCode": 1,"deviceNo": 1,  "status": 1,"startTime":1,"endTime":1 }, { "name": "index_mineCode_deviceNo_status_startTime_endTime", "unique": true, "dropDups": true, "background": true })


db.getCollection("cmss.mine").aggregate([{
    $project:{
        code:"$code",
        _id:0
    },
    
}
// ,{
//     $match:{
//         code:"10000179"
//     }
// }
]).forEach(function(e){
    
   db.getCollection("cmss.his_201909_"+e.code).createIndex({ "mineCode": 1,"deviceNo": 1,  "status": 1,"realTime":1 }, { "name": "index_mineCode_deviceNo_status_realTime", "unique": true, "dropDups": true, "background": true })
   db.getCollection("cmss.his_201909_"+e.code+"_analog").createIndex({ "mineCode": 1,"deviceNo": 1, "startTime":1,"endTime":1 }, { "name": "index_mineCode_deviceNo_startTime_endTime", "unique": true, "dropDups": true, "background": true })
   db.getCollection("cmss.his_201909_"+e.code+"_switching").createIndex({ "mineCode": 1,"deviceNo": 1,  "status": 1,"changeTime":1 }, { "name": "index_mineCode_deviceNo_status_changeTime", "unique": true, "dropDups": true, "background": true })
})

分组查询最新的数据

db.getCollection("cmss.dev_realtime_value").aggregate(

	// Pipeline
	[
		// Stage 1
		{
			$group: {
			    "_id":"$mineCode",
			    "maxTime":{$max:"$realTime"}
			}
		},

		// Stage 2
		{
			$sort: {
			    maxTime:-1
			}
		},

	]

	// Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/

);

查询数据库状态信息

mongostat --host=127.0.0.169:27017 -u admin -p C4T2s~BwRE --authenticationDatabase=admin

查找字段类型为Date并将其修改成String

db.getCollection("cmss.device").find({$or:[{startTime:{$type:"date"}},{insertTime:{$type:"date"}}]}).forEach(function(doc) {
db.getCollection('cmss.device').updateOne({_id: doc._id},{$set: {insertTime: new String(doc.insertTime),startTime:new String(doc.startTime)}})})
注：如果修改成Date用以下方式
new IOSDate(doc.startTime);

字符型转成double类型

 db.getCollection("cmss.organization").find({"coordY":{ $type : 2 }}).forEach( function (x) {
  x.coordY= Number (x.coordY);
  db.getCollection("cmss.organization").save(x);
});

判断某个字段是否存在

db.cmss.device.find({insertTime:{$exists:true}}).count()

Mongo不同机器间拷备Data

可解决一个节点一直是recovering的状态从一个数据库复制所有数据文件到其它库
重启一个节点或者如果坏掉了，无法启动，需要把一个好的节点上的data打包，Copy到坏节点上，而且copy是需要停服务备份的，在相互copy文件的机器上，做好服务器之间的互信后，就可以通过scp直接copy了

服务器互信

生成ssh-keygen

机器Client上root用户执行ssh-keygen命令，生成建立安全信任关系的证书

        [root@Client root]# ssh-keygen -b 1024 -t rsa
        Generating public/private rsa key pair.
        Enter file in which to save the key (/root/.ssh/id_rsa): 
        Enter passphrase (empty for no passphrase):                                <-- 直接输入回车
        Enter same passphrase again:                                                        <-- 直接输入回车
        Your identification has been saved in /root/.ssh/id_rsa.
        Your public key has been saved in /root/.ssh/id_rsa.pub.
        The key fingerprint is:
        49:9c:8a:8f:bc:19:5e:8c:c0:10:d3:15:60:a3:32:1c root@Client

注意：在程序提示输入passphrase时直接输入回车，表示无证书密码。
上述命令将生成私钥证书id_rsa和公钥证书id_rsa.pub，存放在/root/.ssh/子目录中。

复制密钥证书

将公钥证书id_rsa.pub复制到机器Server的/root/.ssh/子目录下，
如果Server的/root/.ssh/下没有authorized_keys，则直接将id_rsa.pub重命名为authorized_keys，
如果Server的/root/.ssh/下已经有authorized_keys文件，则将id_rsa.pub的内容添加到authorized_keys文件的尾部。
[可以使用cat  id_dsa.pub >> authorized_keys命令，也可以使用vim打开authorized_keys进行编译。]

现在就可以不输入密码进行文件传输了

scp -r /data/db 192.168.1.163:/data/

删除一个月之前的数据信息

db.getCollection("cmss.exception_data_info").remove({"insertTime":{"$lte":ISODate("2019-12-01T00:00:00.000Z")}}, {justOne: false})

db.getCollection("cmss.receive_error_file").remove({"insertTime":{"$lte":ISODate("2019-12-01T00:00:00.000Z")}}, {justOne: false})

##模糊匹配表名并且统计表中记录求总和

use dbname
var colls = db.getCollectionNames();
var total=0;
for(var i = 0; i < colls.length; i++){
      //下面是正则匹配，可以根据自己的表名去写js正则
     if(/yhqt\.\w*\_202012\_*/.test(colls[i])){
         total+=db[colls[i]].stats().count;
          print(colls[i])
       }
      
}
         print(total)

查询源数据插入到目标源数据


db.cmss.device__his2.find().forEach(function(e){
    db.cmss.device_his_1.insert(e)
})

修改日期字段增加一天

  db.sps.damage_warn_value.find({}).forEach(
   function(item){                
       db.sps.damage_warn_value.update({"_id":item._id},{"$set":{"securityDate":new Date(item.securityDate.getTime() + 1*24*60*60000)}},false,true)
     }
)

关联表查询分组查询最新时间

db.getCollection("zl.realtime_value").aggregate([
    {$match: {mineCode:{$in:[
'9999999','88888888' ]}}},
    {$group:{
        "_id":"$mineCode",
        "maxTime":{$max:"$createDate"}
    }},
    {
        $lookup: {
            from: "zl.organization",
            localField: "_id",
            foreignField: "code",
            as: "test"
        }
    },
    {
        $addFields: {
            "test":{
                $arrayElemAt:["$test",0]
            }
        }
        
    },
    {
      $project: {
          "_id":"$_id",
          "maxTime":"$maxTime",
          "name":"$test.name"
      }  
    },
  {
			$sort: {
			    maxTime:-1
			}
		}
    ])