mongodb语法实例

我不是女神ヾ 2023-01-05 12:15 149阅读 0赞

# 查询 #

## 基本查询 ##

### 1、最简单查询 ###

关键字：find

input_chance_record_optimization.find({ "chanceId":"16288754"})

相当于

select * from input_chance_record_optimization where chanceId="16288754"

### 2、范围查询 ###

关键字：$gte $lt

db.getCollection('ChanceTurnRound').find({ "userId":11310, "isDistribution":4,"recordTime":{ $gte:"2020-12-30 00:00:00", $lt:"2021-01-04 00:00:00"}})

相当于

select * from ChanceTurnRound where userId=11310 and isDistribution=4 and recordTime>="2020-12-30 00:00:00" and recordTime<"2021-01-04 00:00:00"

### 3、多条件匹配倒序排序limit查询查询 ###

关键字：sort limit

db.getCollection('input_chance_record_optimization').find({ "crmSource":"SYSTEM","doc.index":"wx_c_pthdhqsjh_othersconsult"}).sort({ "createAccurateDate":-1}).limit(10)

相当于

select * from input_chance_record_optimization where crmSource="SYSTEM" and doc.index="wx_c_pthdhqsjh_othersconsult" order by createAccurateDate desc limit 0,10

### 4、只查询显示部分字段 ###

关键字：find(\{\},`{}`)

db.getCollection('input_chance_record_optimization').find({ "createDate":"2021-01-11","doc.app":"997",$or:[{ "errorCode":4005},{ "errorCode":4017}]},{ "doc.mobile":1,"createAccurateDate":1})

相当于

select mobile,createAccurateDate from input_chance_record_optimization where createDate="2021-01-11" and doc.app="997" and (errorCode=4005 or errorCode=4017)

### 5、模糊匹配 ###

关键字：$regex

db.getCollection('input_chance_record_optimization').find({ "createDate":"2021-05-06","crmFlowList.msg":{ $regex:'类型下无销售人员，类型ID:70'}})

相当于

select * from input_chance_record_optimization where createDate="2021-05-06" and crmFlowList.msg like '%类型下无销售人员，类型ID:70%'

### 6、正则表达式 ###

关键字：$regex  
示例：doc.url长度大于700

db.getCollection('input_chance_record_optimization').find({ "createDate":"2021-05-10","doc.url": { "$exists": true, "$regex": /^.{700,}$/}}).limit(50)

相当于

select * from input_chance_record_optimization where createDate="2021-05-10" and 
    LENGTH("doc.url") > 700

### 7、其他查询符 ###

$in  
$and  
$or  
$all

## 二、嵌套查询 ##

### 1、根据嵌套对象中字段查询 ###

关键字：.

db.getCollection('input_chance_record_optimization').find({ "doc.app":"10003","createDate":{ $gte:"2021-01-19"},"doc.cskuTime":{ $gte:1611057120000,$lte:1611820800000},"doc.action":{ $ne:6}})

*  其中`doc.app`、`doc.cskuTime`、`doc.action`中的`app`、`cskuTime`、`action`都是`doc`对象中的字段

### 2、查询嵌套的列表中是否存在某个值 ###

关键字：$elemMatch

db.getCollection('input_chance_record_optimization').find({  "createDate" : "2021-01-22", "crmAllotList" : {  "$elemMatch" : {  "allotCode" : 6022} } }).count()

*  其中`$elemMatch`表示匹配`crmAllotList`列表中的字段`allotCode`是否存在`6022`这个值

## 聚合查询 ##

### 1、count查询 ###

关键字：count

db.getCollection('input_chance_record_optimization').find({ "createDate":"2021-01-11","doc.app":"997",$or:[{ "errorCode":4005},{ "errorCode":4017}]}).count()

相当于

select count(*) from input_chance_record_optimization where createDate="2021-01-11" and doc.app="997" and (errorCode=4005 or errorCode=4017)

### 2、group by count查询 ###

#### （1）方式1 ####

关键字：aggregate $group $sum:1

db.input_chance_record_optimization.aggregate(
        { 
            $match: { 
               "createDate":"2021-01-11","doc.app":"997", "errorCode":4005
            }
        },
        {   
            $group : { 
                 _id :"$doc.skuId",  count: {  $sum: 1}
        
             }  
        }
    )

#### （2）方式2 ####

关键字：group

db.getCollection('input_chance_record_optimization').group({  
      key: { 'doc.skuId': 1},    
      cond: { "createDate":"2021-01-11","doc.app":"997","errorCode":4005}, 
      reduce: function(obj,skuCount) { skuCount.count++},
      initial: {  count:0} 
    })

相当于（不考虑doc.skuId的嵌套格式）

select skuId, count(skuId) from input_chance_record_optimization where createDate="2021-01-11" and app="997" and errorCode=4005
    group by skuId

### 3、查询结果使用别名 ###

关键字：aggregate $project

db.input_chance_record_optimization.aggregate([
    	{ $match:{ "createDate":"2021-05-06","crmFlowList.msg":{ $regex:'类型下无销售人员，类型ID:70'}}},
    	{ $project: { mobile:"$doc.mobile","_id":0}}
    ])

相当于

select doc.mobile as mobile from input_chance_record_optimization where createDate="2021-05-06" and crmFlowList.msg like '%类型下无销售人员，类型ID:70%'

### 4、查询列表字段的长度 ###

关键字：aggregate $project $match $size

db.input_chance_record_optimization.aggregate([
        {
            "$match": {
                "crmFlowList": { $exists: true },
                "_id":ObjectId("60d02a2994280fdce8173ce4")
            }
        },
        {
            "$project": {
                datasNum: { $size: "$crmFlowList" }
            }   
        }
    ])

spring-mongo操作：

String mongoId = "5bfd12fd49d7ac000157e83e";
    List<AggregationOperation> operations = new ArrayList<>();
    MatchOperation match = Aggregation.match(Criteria.where("_id").is(new ObjectId(mongoId)));
    operations.add(match);
    ProjectionOperation as = Aggregation.project()
    		.and("crmAllotList")
    		.size()
    		.as("datasNum");
    operations.add(as);
    Aggregation aggregation = Aggregation.newAggregation(operations);
    
    AggregationResults<Map> results = template.aggregate(aggregation, "input_chance_record_optimization", Map.class);
    int datasNum = (int)results.getUniqueMappedResult().get("datasNum");
    System.out.println(datasNum);

### 5、查询列表字段的长度，并按其倒序排序 ###

关键字：aggregate $project $match $size $sort $limit

db.input_chance_record_optimization.aggregate([
        {
            "$match": {
                "crmAllotList": { $exists: true },
                "createDate": {$gte: "2021-06-20"}
            }
        },
        {
            "$project": {
                datasNum: { $size: "$crmAllotList" }
            }   
        },
        { "$sort" : { datasNum : -1 } },
        {
       "$limit":10}
    ])

spring-mongo操作：

List<AggregationOperation> operations = new ArrayList<>();
    			MatchOperation match = Aggregation.match(Criteria.where("createDate").gte("2021-06-20").and("crmAllotList").exists(true));
    			operations.add(match);
    			ProjectionOperation as = Aggregation.project()
    					.and("crmAllotList")
    					.size()
    					.as("datasNum");
    			operations.add(as);
    			SortOperation sort = Aggregation.sort(Sort.Direction.DESC, "datasNum");
    			operations.add(sort);
    			LimitOperation limit = Aggregation.limit(10);
    			operations.add(limit);
    			Aggregation aggregation = Aggregation.newAggregation(operations);
    
    			AggregationResults<Map> resultsss = template.aggregate(aggregation, "input_chance_record_optimization", Map.class);
    			resultsss.getMappedResults().forEach(
    					r -> { 
    						Map<String, Object> r1 = (Map<String, Object>) r;
    						System.out.println(r1.get("_id") + " : " + r1.get("datasNum"));
    					}
    			);

# 更新 #

## 1、基本更新 ##

关键字：update $set

db.getCollection('input_chance_record_optimization').update({
       "createDate":{$gte:"2021-05-06"},"errorCode":4003,"retryCount":3},{$set:{
       "retryCount":NumberInt(0)}},{
       "multi":true})

## 2、$push追加 + $set ##

关键字：update $push $set

db.input_chance_record_optimization.update({_id: ObjectId("5bfd12fd49d7ac000157e83e")}, {$push: {crmAllotList:{
                "ip" : "10.30.169.141",
                "msg" : "机会分配时类型下无人员;typeId:66",
                "date" : "2019-01-25 15:00:11",
                "allotCode" : 6005
            }}, $set: {
       "allotCode":NumberInt(6005)}});

spring-mongo操作：

String id = "5bfd12fd49d7ac000157e83e";
    	CrmAllotFlow crmAllotFlow = new CrmAllotFlow(ErrorEnum.ChanceRecord.C_6005, "typeId:" + 66);
    	Query query = new Query();
    	query.addCriteria(Criteria.where("_id").is(new ObjectId(id)));
    	Update update = new Update().push("crmAllotList", crmAllotFlow);
    	update.set("allotCode", crmAllotFlow.getAllotCode());
    	template.updateFirst(query, update, "input_chance_record_optimization");

## 3、清空某一字段 ##

关键字：$unset

db.input_chance_record_optimization.update(
       {_id: ObjectId("5bfd12fd49d7ac000157e83e")},
       { $unset: {crmAllotList: null} }
    )

spring-mongo操作：

String mongoId = "5bfd12fd49d7ac000157e83e";
    Query query = Query.query(Criteria.where("_id").is(new ObjectId(mongoId)));
    Update update = new Update();
    update.unset("crmAllotList");
    template.updateFirst(query, update, "input_chance_record_optimization");