MongDB 查询小记

在项目过程中，初次使用到了MongoDB,以下是一些查询使用整理，以便回顾。

一.初级查询：

// 1. 原生态
// 根据id查询
db.OrderDetailDb.find({_id:ObjectId("")});

// 根据条件查询
db.OrderDetailDb.find({"field_name1":"filter1","field_name2":"filter2","field_name3":"filter3"});

eg:
db.OrderDetailDb.find({"Name":"张三","Sex":"男","Age":20});

// C# 应用

var filter = Builders<BsonDocument>.Filter;

// 1. var doc = collection.Find(filter.Eq(_id,ObjectId(""))).FirstOrDefault();
// AND查询
// 2. var doc = collection.Find(filter.Eq("Name","张三") && filter.Eq("Sex":"男") && filter.Eq("Age":"20")).FirstOrDefault();
// OR查询
// 3. var doc = collection.Find(filter.Eq("Name":"张三")| filter.Eq("Sex":"男")| filter.Eq("Age":"20")).FirstOrDefault();

 

二.聚合查询：

db.OrderDetailDb.aggregate([
// 查询条件
 {$match: {
     "bill_id": 82,
     "headler_detail.is_remove":{$ne: 1}
 }},
// 分组字段
 {$group: 
    { _id: "$bill_detail.customer_group_code",
        "bill_id":{"$first": "$bill_id"},
        "sub_bill_no":{"$first":"$bill_detail.bill_no"},
        "server_code":{"$first":"$bill_detail.server_code"},
        "customer_group_code":{"$first":"$bill_detail.customer_group_code"},
        "customer_group_name":{"$first":"$bill_detail.customer_group_name"},
        "server_type": {"$first": "$bill_detail.service_bill_type"},
        "order_total_count":{ "$sum": 1} ,
        "shipment_total_fee": {"$sum": "$bill_fee_detail.shipment_fee"},
        "additional_total_fee": {"$sum": "$bill_fee_detail.additional_fee"},
        "errata_total_fee": {"$sum": "$bill_fee_detail.errata_total_fee"}

    }
 },
// 要显示的字段
 {$project:
    {
        _id:0,
       "bill_id":1,
       "sub_bill_no":1 ,
       "server_code":1,
       "customer_group_code":1,
       "customer_group_name":1,
       "server_type":1,
       "order_total_count":1,
       "shipment_total_fee":1,
       "additional_total_fee":1,
       "errata_total_fee":1,
    }
 }
 ])

三.条件操作符：

  1.$eq  匹配等于

db.OrderDetailDb.find({Age:{$eq:20}}); 
等于 
db.OrderDetailDb.find({Age:20})

等于
select * from OrderDetailDb where Age=20;

// C# 应用

var filter = Builders<BsonDocument>.Filter;

1. var doc = Collection.Find(filter.Eq("Age","20")).FirstOrDefault();

2.$gt  匹配大于

db.OrderDetailDb.find(age:{$gt:20})
等于
select * from OrderDetailDb where age>20;

// C# 应用

var filter = Builders<BsonDocument>.Filter;

1. var doc = Collection.Find(filter.Gt("Age","20")).FirstOrDefault();

3.$gte  匹配大于等于

db.OrderDetailDb.find(age:{$gte:20});
等于
select * from OrderDetailDb where age >= 20;

// C#应用
var filter = Builders<BsonDocument>.Filter;

1. var doc = Collection.Find(filter.Gte("Age":"20")).FirstOrDefault();

4.$lt  匹配小于

db.OrderDetailDb.find(age:{$lt:20})
等于
select * from OrderDetailDb where age<20;

// C#应用
var filter = Builders<BsonDocument>.Filter;
1. var doc = Collection.Find(filter.Lt("Age":"20")).FirstOrDefault();

5.$lte  匹配小于等于

db.OrderDetailDb.find(age:{$lte:20})
等于
select * from OrderDetailDb where age<=20;

// C#应用
var filter = Builders<BsonDocument>.Filter;
1. var doc = Collection.Find(filter.Lte("Age":"20")).FirstOrDefault();

6.$ne  匹配不等于

db.OrderDetailDb.find(age:{$ne:20})
等于
select * from OrderDetailDb where age<>20;

// C#应用
var filter = Builders<BsonDocument>.Filter;
1. var doc = Collection.Find(filter.Ne("Age":"20")).FirstOrDefault();

7.$in  匹配数组中任意值

db.OrderDetailDb.find(age:{$in:[20,21,25]});
等于
select * from OrderDetailDb where age in (20,21,25);


db.OrderDetailDb.find(Name:{$in:["张三","李四","王五"]});
等于
select * from OrderDetailDb where Name in ("张三","李四","王五");

// C# 应用
var filter = Builders<BsonDocument>.Filter;
1. var doc = Collection.Find(filter.In(m => m.id , ids)).FirstOrDefault();

8.$nin  不匹配数组中的值

db.OrderDetailDb.find(age:{$nin:[20,21,25]});
等于
select * from OrderDetailDb where age not in (20,21,25);


db.OrderDetailDb.find(Name:{$nin:["张三","李四","王五"]});
等于
select * from OrderDetailDb where Name not in ("张三","李四","王五");

// C# 应用
var filter = Builders<BsonDocument>.Filter;
1. var doc = Collection.Find(filter.Nin(m => m.id , ids)).FirstOrDefault();

 9. Exists 包含

var filter = Builders<BsonDocument>.Filter;
1.var doc = Collection.Find(filter.Exists("张三")).ToList();

 

 

四：逻辑操作符：

暂未完成，后续补充

 

 

 

NoSQLBooster（mongobooster）管理工具

下载地址：https://nosqlbooster.com/downloads