mongodb语法实例

打不死的小强lee

已于 2022-05-30 16:32:38 修改

阅读量314

点赞数

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

于 2021-01-12 11:32:38 首次发布

本文链接：https://blog.csdn.net/wenqiangluyao/article/details/112512682

版权

mongodb 专栏收录该内容

5 篇文章 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
$ne
$exists

二、嵌套查询

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