官方文档
相关文档
https://www.likecs.com/show-306815057.html
https://blog.csdn.net/qq_18948359/article/details/88777066
https://www.likecs.com/show-306640279.html
常用操作
简单查询
/** 查询版本 */
db.version();
db.users.find();
select * from users;
db.users.find({"age" : 27}) ;
select * from users where age = 27;
db.users.find({"username" : "joe", "age" : 27});
select * from users where "username" = "joe" and age = 27;
db.users.find({}, {"username" : 1, "email" : 1});
select username, email from users;
/** no case 即时加上了列筛选,id也会返回;必须显式的阻止_id返回 */
db.users.find({}, {"username" : 1, "id" : 0});
db.users.find({"age" : {"$gte" : 18, "$lte" : 30}});
select * from users where age >=18 and age <= 30; /* $lt(<) $lte(<=) $gt(>) $gte(>=) */
db.users.find({"username" : {"$ne" : "joe"}});
select * from users where username <> "joe";
db.users.find({"ticket_no" : {"$in" : [725, 542, 390]}});
select * from users where ticket_no in (725, 542, 390);
db.users.find({"ticket_no" : {"$nin" : [725, 542, 390]}});
select * from users where ticket_no not in (725, 542, 390);
db.users.find({"$or" : [{"ticket_no" : 725}, {"winner" : true}]});
select * form users where ticket_no = 725 or winner = true;
db.users.find({"id_num" : {"$mod" : [5, 1]}});
select * from users where (id_num mod 5) = 1;
db.users.find({"$not": {"age" : 27}});
select * from users where not (age = 27);
db.users.find({"username" : {"$in" : [null], "$exists" : true}});
select * from users where username is null;
/* 如果直接通过find({"username" : null})进行查询,那么连带"没有username"的纪录一并筛选出来 */
db.users.find({"name" : /joey?/i}); /*正则查询,value是符合PCRE的表达式*/
db.food.find({fruit : {$all : ["apple", "banana"]}}); /* 对数组的查询, 字段fruit中,既包含"apple",又包含"banana"的纪录 */
db.food.find({"fruit.2" : "peach"}); /* 对数组的查询, 字段fruit中,第3个(从0开始)元素是peach的纪录 */
db.food.find({"fruit" : {"$size" : 3}}); /* 对数组的查询, 查询数组元素个数是3的记录,$size前面无法和其他的操作符复合使用 */
db.users.findOne(criteria, {"comments" : {"$slice" : 10}}); /* 对数组的查询,只返回数组comments中的前十条,还可以{"$slice" : -10}, {"$slice" : [23, 10]}; 分别返回最后10条,和中间10条 */
复杂查询
db.people.find({"name.first" : "Joe", "name.last" : "Schmoe"}); /* 嵌套查询 */
db.blog.find({"comments" : {"$elemMatch" : {"author" : "joe", "score" : {"$gte" : 5}}}}); /* 嵌套查询,仅当嵌套的元素是数组时使用 */
db.foo.find({"$where" : "this.x + this.y == 10"}) /* 复杂的查询,$where当然是非常方便的,但效率低下。对于复杂查询,考虑的顺序应当是 正则 -> MapReduce -> $where */
db.foo.find({"$where" : "function() { return this.x + this.y == 10; }"}) /* $where可以支持javascript函数作为查询条件 */
db.foo.find().sort({"x" : 1}).limit(1).skip(10); /** 返回第(10, 11]条,按"x"进行排序; 三个limit的顺序是任意的,应该尽量避免skip中使用large-number */
/* 去重查询 */
db.getCollection("sfc_wish_sync_list_task").distinct("message",{"status":1}); /* 第一个参数为要去重的字段,第二个参数为去重的条件 */
/** 多条件去重查询 */
db.getCollection("sfc_shopee_sync_list_task").distinct("site_code",{
"status": 1,"create_time":{$gte:1636070506}
});
/* 嵌套查询 , 第二个参数是 显示字段,第一个参数是 过滤条件 */
db.getCollection("sfc_wish_convert_detail_task").find({"original_data.Order.tracking_confirmed":"True"},{"original_data.Order.tracking_confirmed":1});
/* 查询数量 */
db.getCollection("sfc_wish_convert_detail_task").find({"order_status":"APPROVED"}).count();
/* 嵌套查询字段 */
db.getCollection("sfc_wish_convert_detail_task").find({"original_data.Order.tracking_confirmed":"True"},{"original_data.Order.tracking_confirmed":1});
/* 根据时间查询 */
db.getCollection("sfc_gmarket_convert_detail_task").find({ $and : [{"create_time" : { $gte : 1625018721 }}, {"create_time" : { $lt : 1625065521 }}] })
实战操作
db.getCollection("sfc_gmarket_convert_detail_task").find({"original_data.ReceiverEntryNo":""},{"original_data.ReceiverEntryNo":1,"order_number":1,"order_time":1}).sort({"order_time":1});
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U7W7IrsL-1651887781515)(.\image\image-20210709114114663.png)]
# 多条件查询, $and 开启多条件查询
db.getCollection("sfc_auction_sync_list_task").find({$and:[{"status" : 1},{"create_time":{$gte : 1626748789}}]}).limit(1000).skip(0).sort({"create_time":-1});
# 查询某一字段是否存在
db.getCollection("sfc_yahoo_convert_detail_task").find({"original_data.Detail.SettleAmount":""},{"original_data.Detail":1,"order_number":1,"order_time":1,"create_time":1,"update_time":1}).sort({"order_time":1});
db.getCollection("sfc_shopee_convert_detail_task").find({"original_data.items.item_sku":"53057558"});
db.getCollection("sfc_shopee_convert_detail_task").find({$and:[{"original_data.items.item_sku":"75018869"},{"create_time":{$gte : 1609430400}}]});
db.getCollection("sfc_shopee_convert_detail_task").find({$and:[{"original_data.items.item_sku":"53055635"},{"create_time":{$gte : 1609430400}},{"order_status":"COMPLETED"}]}, {"original_data":1,"order_number":1,"order_time":1,"site_code":1,"order_status":1,"_id":0}).limit(10000).skip(0);
db.getCollection("sfc_shopee_convert_detail_task").find({$and:[{"original_data.items.item_sku":"53055635"},{"create_time":{$gte : 1609430400}},{"order_status":"COMPLETED"}]}, {"original_data":1,"order_number":1,"order_time":1,"site_code":1,"order_status":1});
/** 多条件查询 */
db.getCollection("sfc_shopee_convert_detail_task").find({
$and: [{
"create_time": {
$gte: 1603382400
}
}, {
"order_status": "COMPLETED"
}]
}, {
"original_data": 0,
}).sort({"create_time":1}).limit(2).skip(15510);
/** 多条件查询,字段显示,排序 */
db.getCollection("sfc_allegro_convert_detail_task").find({
$and: [{
"create_time": {
$gte: 1634086452
}
}, {
"original_data.status": "CANCELLED"
}]
}, {
"original_data.status": 1,"order_status":1,"order_time":1,"create_time":1, "order_number":1
}).sort({"create_time":-1});
/** 嵌套条件查询 */
db.getCollection("sfc_mercadolibre_convert_detail_task").find({"site_code" : "mercadolibre.cl10","original_data.shipping.id":40921574844});
普通查询
db.getCollection("sfc_coupang_convert_detail_task").find({
"original_data.shipmentType": "CGF LITE"
}, {
"site_code": 1,
"order_number": 1,
"account": 1
}).sort({
"order_time": 1
}).count();
/** 查询 指定数量*/
db.getCollection("sfc_coupang_convert_detail_task").find({
"original_data.shipmentType": "CGF LITE"
}, {
"site_code": 1,
"order_number": 1,
"account": 1
}).sort({
"order_time": 1
}).limit(1000);
带条件查询后并分组,且排序
/** $match 过滤条件 $group 按什么分组 $sum 计算数量和 $sort 通过什么排序, 1 升序, -1 降序 */
db.getCollection("crm_mail").aggregate({
$match: {
$and: [{
"cm_type": 1
}, {
"cm_status": {
$gt: 0
}
}, {
"cm_send_time": {
$gte: "2022-05-05 00:00:00"
}
}, {
"cm_send_time": {
$lt: "2022-05-06 00:00:00"
}
}, {
"user_id": {
$ne: 0
}
}]
}
},{
$group: {
_id: "$user_id",
count:{$sum :1}
}
},
{
$sort: {"count": -1}
});
查询集合
show collections;
去重带条件查询
格式:db.collection_name.distinct(field,query,options)
db.getCollection("sfc_shopee_sync_list_task").distinct("site_code",{ $and : [{"status" : 1}, {"create_time" : { $gt : 1636385161 }}, {"message" : "error_auth:This shop account has been banned. Permissions for shop authorization and API calls have been suspended until the shop account is restored."}] });
/** 普通去重 */
db.getCollection("sfc_advt_performances").distinct("message",{"status":1});
深度查询
db.getCollection("sfc_allegro_convert_detail_task").find({
$and: [{
"create_time": {
$gte:
1632546113
}
},{
"original_data.lineItems.5.offer.id": '9192543227'
}]
});
多条件查询
db.getCollection("sfc_shopee_sync_list_task").find({
$and: [{
"status": 1
}, {
"create_time": {
$gt: 1637373103
}
}, {
"message": "error_auth:partner and shop has no linked"
}]
}).limit(1000).skip(0);
db.getCollection("sfc_wish_convert_detail_task").find({ $and : [{"site_code" : "wish18.com"}, {"order_time" : { $gt : 1609430400 }}, {"order_time" : { $lte : 1640966400 }}] }).count();
分组前后过滤查询重复数量并排序
db.getCollection("sfc_jumia_convert_detail_task").aggregate(
{
$match: {
$and: [
{
"create_time": {
$gte: 1673840161
}
},
{
"create_time": {
$lte: 1675061403
}
}
]
}
}, {
$group: {
"_id": "$order_number",
"count": {
$sum: 1
}
}
}, {
$sort: {
"count": - 1
}
}, {
$match: {
"count": {
$gt: 2
}
}
});
更新操作
批量更新
db.getCollection("sfc_shopee_sync_list_task").updateMany({$and:[{"status" : 1},{"create_time":{$gte : 1639649721}}]}, { $set: { times: NumberInt("1") } } );
查询优化
db.getCollection("sfc_rakuten_convert_detail_task").find({"create_time":{$gte : 1639615386}}).limit(1000).skip(0).explain('executionStats');
参考文档:https://www.tizi365.com/topic/81.html
https://piaosanlang.gitbooks.io/mongodb/content/03day/explaincha-xun-xing-neng-fen-xi-ff08-2.html
http://caibaojian.com/mongodb/mongodb_analyzing_queries.html