mongodb常见sql语法汇总(后续会根据实际业务场景中碰到的继续更新)
1、只返回某些字段
只返回agentOrderType 和agentCode 字段
只返回agentOrderType 和agentCode 字段
db.getCollection("svOrderMo").find({"_id":"202206011053415562"},{"agentOrderType":1,"agentCode":1});
2、聚合:对某个字段进行分组,然后对某个字段进行聚合sum加和
// 对targetValue字段进行聚合统计,key是cId
db.getCollection("channelTargetMo").aggregate([
{$match{"targetDateType":"day"}},
{$group:{_id:"$cId",total:{"$sum":"$targetValue"}}}
]);
这个表示的是,根据cId字段进行分组,然后把相同的cId下的targetValue字段值加起来
3、根据多个字段分组查询,然后过滤掉count>1的数据
db.getCollection('agentPolicyMo').aggregate([
{$match:{"innerStatus":1}},
{$group:{_id:{orderId:"$orderId",svOrderId:"$svOrderId"},count:{"$sum":1}}},
{$match:{count:{"$gt":1}}}
]);
如上所示是根据 orderId和svOrderId两个字段来分组,分组后的重复值记为count, 最终通过$match函数来过滤count>1的数据
4、对聚合后的数据进行count(即看分组后的数据有多少条)
db.getCollection('agentPolicyMo').aggregate([
{$match:{"innerStatus":1}},
{$group:{_id:{orderId:"$orderId",svOrderId:"$svOrderId"},count:{"$sum":1}}},
{$match:{count:{"$gt":1}}},
{$group:{_id:null,total:{"$sum":1}}}
]);
5、在聚合查询时使用 sort, skip,limit关键字
db.getCollection('svOrderRecordMo').aggregate([
{$match:{"actionType":9}},
{$group:{_id:"$svOrderId"}},
{$sort:{"_id":1}},
{$skip:0},
{$limit:3000}
])
如上所示:根据id排序,从0开始查询,每次返回3000条
注意:因为我用的客户端,mongodb每次页面返回的数据只能返回50条,如果为了客户端数据能返回数量>50条,可以在查询条件前面加上
DBQuery.shellBatchSize = 30000
30000表示,设置客户端当前页面的返回数量限制为30000条
6、在查询中使用 字段A>xxxa and 字段B<xxxb ,这时需要使用到$and关键词
db.getCollection("svOrderMo").aggregate(
[
{$match:{"svStatus":{"$ne":100},
"svOrderAttr":3,"svOrderSecondStatus":{"$ne":8},
"$and":[{"matchTime":{"$gte":ISODate("2023-02-27T00:00:00.000+08:00")}}
,{"matchTime":{"$lte":ISODate("2023-02-28T00:00:00.000+08:00")}}],
"agentOrderType":{"$in":["t_plan_give","dac_t_give_welfare",
"dac_t_year_card_welfare"]}}},
{$group:{_id:"$mainChannelName",toatl:{"$sum":1}}}
]
)
如上所示,查询matchTime字段大于2023-02-27到2023-02-28区间段的数据
7、在查询之前,需要将date日期格式转成 yyyyMMdd 字符串格式的
db.getCollection('svRegisterRecordMo').aggregate([
{$match:{"$and":[{"createTime":{"$gte":ISODate("2023-01-01T00:00:00.000+08:00")}}
,{"createTime":{"$lte":ISODate("2023-07-02T00:00:00.000+08:00")}}] }},
{
$project: {
convertedDate: {
$dateToString: {
format: "%Y%m%d %H%m%S",
date: "$createTime"
}
}
}
},
{
$group: {
_id: "$convertedDate",
count: { $sum: 1 }
}
}
])
如上所示,在查询之前将createTime 字段转成字符串并设置别名为convertedDate ,然后根据convertedDate来分组,并统计数量
8、根据某个字段分组后,然后统计两个字段的数值加和
db.getCollection('reportChannelDataMo').aggregate([
{$match:{"cId" : "6350d365bdd3cf3e5eb88d4b", "reportType" : "day"}},
{$group:{_id:"$reportDate",total:{"$sum":{$add:[ "$newConfirmInternetAmount", "$newConfirmLifeCriterionAmount" ]}}}
},
{$match:{total:{"$gt":0}}},
{$sort:{"_id":1}}
])
如上所示:根据reportDate 字段分组后,统计newConfirmInternetAmount 和newConfirmLifeCriterionAmount 字段的加和
9、查询集合中两个字段不相等的数据,这时候需要用到javascript 脚本 ,$where
db.getCollection("policyExceptionRecordMo").find({
"$where":"this.beforePolicyStage!=this.afterPolicyStage"
})
10、$where 关键词后面跟随多个条件查询
db.getCollection("dacMatchAgentWeightMo").find({
"$where":"this.twoWeekNum!=this.thisMonthNum && this.isDeleted==false && this.pastMonth=='2023-07' && this.target=='allocation_num'"
}).sort({"pastMonth":-1})
(注意:使用$where javascript shell脚本这种方式的查询效率会很低)
11、根据字段a分组时,字段b有不同的值,如果只想取第一条数据的字段b的值,用$first关键字
db.getCollection('svOrderMo').aggregate([
{$match:{"agentCode":null,"agentId":{"$ne":null}}},
{$group:{_id:"$agentId",total:{"$sum":1},businessModel:{$first:"$businessModel"}}}
])
如上所示:根据agentId 字段分组时,businessModel 字段有不同的值,只想取第一条数据的businessModel 字段值
12、更新数据操作
db.getCollection("reportChannelDataMo").update({$or:[
{"giveNumDacE":{"$lt":0}},
{"giveNumDacEs":{"$lt":0}},
{"giveNumDac":{"$lt":0}}
]},{"$set":{"giveNumDacEs":0.0,"giveNumDacE":0.0}},{multi:true}
);
{multi:true} 表示是更新符合条件的多条数据