第三节：MongoDB聚合操作和索引详解
一. 聚合操作
(此处是基于.Net驱动的聚合操作，直接操作Mongodb的写法详见 有道笔记mongodb第二节)
0. 相关实体
(1). Book相关
public class Book { /// <summary> /// 自动生成id /// </summary> [BsonId] [BsonRepresentation(BsonType.ObjectId)] public ObjectId id { get; set; } public string title { get; set; } public string type { get; set; } public List<string> tag { get; set; } public int favCount { get; set; } public Author author { get; set; } } public class Author { public string name { get; set; } public int age { get; set; } } public class Book2 { public ObjectId id { get; set; } public string name { get; set; } } public class Book3 { public ObjectId id { get; set; } public string name { get; set; } public string type { get; set; } public Author author { get; set; } } public class GroupData1 { public string authName { get; set; } public int totalCount { get; set; } } public class GroupData2 { public string authName { get; set; } public string bookTitle { get; set; } public int totalCount { get; set; } }
(2). Order相关
public class Customer { public ObjectId id { get; set; } /// <summary> /// 顾客编码--唯一的 /// </summary> public int customerCode { get; set; } public string name { get; set; } public string phone { get; set; } public string address { get; set; } } public class Order { public ObjectId id { get; set; } /// <summary> /// 订单编号--唯一的 /// </summary> public int orderId { get; set; } public string orderCode { get; set; } public int customerCode { get; set; } public decimal price { get; set; } } public class OrderItem { public ObjectId id { get; set; } public int itemId { get; set; } public string productName { get; set; } public int quantity { get; set; } public int orderId { get; set; } } public class Customer2 { public ObjectId id { get; set; } /// <summary> /// 顾客编码--唯一的 /// </summary> public int customerCode { get; set; } public string name { get; set; } public string phone { get; set; } public string address { get; set; } public List<Order> customerOrder { get; set; } } public class Order2 { public ObjectId id { get; set; } /// <summary> /// 订单编号--唯一的 /// </summary> public int orderId { get; set; } public string orderCode { get; set; } public int customerCode { get; set; } public decimal price { get; set; } public List<Customer> curstomer { get; set; } public List<OrderItem> orderItem { get; set; } }
(3). 初始化数据
private readonly IMongoCollection<Book> bookService; private readonly IMongoCollection<Customer> customerCollection; private readonly IMongoCollection<Order> orderCollection; private readonly IMongoCollection<OrderItem> orderItemCollection; public Demo2Controller() { var settings = new MongoClientSettings { Server = new MongoServerAddress("47.xx.xx.xx", 27017), //对应的数据库、账号、密码 (这里的ship1账号仅有ShipDB1的权限) //Credential = MongoCredential.CreateCredential("ShipDB1", "ship1", "123456") //适用于所有数据，数据位置需要写admin Credential = MongoCredential.CreateCredential("admin", "ypf", "ypf0806") }; // 创建MongoClient对象 var client = new MongoClient(settings); // 2. 获取数据库ShipDB 【若没有，则自动创建,插入数据的时候才生效】 var database = client.GetDatabase("ShipDB1"); // 3. 获取表 books 【若没有，则自动创建,插入数据的时候才生效】 bookService = database.GetCollection<Book>("books"); customerCollection = database.GetCollection<Customer>("customer"); orderCollection = database.GetCollection<Order>("order"); orderItemCollection = database.GetCollection<OrderItem>("orderItem"); } /// <summary> /// 初始化Book数据 /// </summary> /// <returns></returns> [HttpPost] public IActionResult InitData() { var tags = new string[] { "nosql", "mongodb", "document", "developer", "popular" }; var types = new string[] { "technology", "sociality", "travel", "novel", "literature" }; var books = new List<Book>(); for (int i = 0; i < 50; i++) { var typeIdx = (int)(new Random().NextDouble() * types.Length); var tagIdx = (int)(new Random().NextDouble() * tags.Length); var tagIdx2 = (int)(new Random().NextDouble() * tags.Length); var favCount = (int)(new Random().NextDouble() * 100); var username = "xx00" + (int)(new Random().NextDouble() * 10); var age = 20 + (int)(new Random().NextDouble() * 15); var book = new Book { title = "book-" + i, type = types[typeIdx], tag = new List<string> { tags[tagIdx], tags[tagIdx2] }, favCount = favCount, author = new Author { name = username, age = age } }; books.Add(book); } bookService.InsertMany(books); return Json(new { status = "ok", msg = "初始化成功" }); } /// <summary> /// 初始化订单数据 /// </summary> /// <returns></returns> [HttpPost] public IActionResult InitData2() { //顾客信息 var customer1 = new Customer { customerCode = 1, name = "customer1", phone = "13112345678", address = "test1" }; var customer2 = new Customer { customerCode = 2, name = "customer2", phone = "13112345679", address = "test2" }; customerCollection.InsertOne(customer1); customerCollection.InsertOne(customer2); //订单信息 var order1 = new Order { orderId = 1, orderCode = "order001", customerCode = 1, price = 200 }; var order2 = new Order { orderId = 2, orderCode = "order002", customerCode = 2, price = 400 }; orderCollection.InsertOne(order1); orderCollection.InsertOne(order2); //订单详情 var orderItem1 = new OrderItem { itemId = 1, productName = "apples", quantity = 2, orderId = 1 }; var orderItem2 = new OrderItem { itemId = 2, productName = "oranges", quantity = 2, orderId = 1 }; var orderItem3 = new OrderItem { itemId = 3, productName = "mangoes", quantity = 2, orderId = 1 }; var orderItem4 = new OrderItem { itemId = 4, productName = "apples", quantity = 2, orderId = 2 }; var orderItem5 = new OrderItem { itemId = 5, productName = "oranges", quantity = 2, orderId = 2 }; var orderItem6 = new OrderItem { itemId = 6, productName = "mangoes", quantity = 2, orderId = 2 }; orderItemCollection.InsertOne(orderItem1); orderItemCollection.InsertOne(orderItem2); orderItemCollection.InsertOne(orderItem3); orderItemCollection.InsertOne(orderItem4); orderItemCollection.InsertOne(orderItem5); orderItemCollection.InsertOne(orderItem6); return Json(new { status = "ok", msg = "初始化成功" }); }
1. $project投影操作
/// <summary>
/// 01-$project投影操作
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult Project()
{
var pipeline = new List<BsonDocument>
{
new ("$project", new BsonDocument("name", "$title"))
};
var result = bookService.Aggregate<Book2>(pipeline).ToList();
return Json(new { status = "ok", msg = "", result });
}
2 $match筛选操作
(1) 单筛选
/// <summary>
/// 02-1-$match筛选操作
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult Match()
{
var pipeline = new List<BsonDocument>
{
new("$match", new BsonDocument("type", "technology"))
};
var result = bookService.Aggregate<Book>(pipeline).ToList();
return Json(new { status = "ok", msg = "获取成功", data = result });
}(2) 筛选+计数
/// <summary>
/// 02-2-$match+$count联合操作
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult Match2()
{
var pipeline = new List<BsonDocument>
{
new ("$match", new BsonDocument("type", "technology")),
new ("$count", "type_count")
};
var result = bookService.Aggregate<BsonDocument>(pipeline).FirstOrDefault();
int type_count = 0;
if (result != null && result.Contains("type_count"))
{
type_count = result["type_count"].ToInt32();
}
return Json(new { status = "ok", msg = "获取成功", type_count });
}
3. $group分组操作
(1). 统计每个作者的book收藏总数
/// <summary>
/// 03-1-$group分组操作1
/// 【统计每个作者的book收藏总数】
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult Group1()
{
var pipeline = new List<BsonDocument>
{
new ("$group", new BsonDocument
{
{ "_id", "$author.name" }, //这个地方前面必须写_id主键
{ "totalCount", new BsonDocument("$sum", "$favCount") }
})
};
var result = bookService.Aggregate<BsonDocument>(pipeline).ToList();
List<GroupData1> groupData = new();
foreach (var item in result)
{
GroupData1 data = new()
{
authName = item["_id"].AsString,
totalCount = item["totalCount"].AsInt32
};
groupData.Add(data);
}
return Json(new { status = "ok", msg = "获取成功", data = groupData });
}
(2). 统计每个作者 每本书 收藏数
/// <summary>
/// 03-2-$group筛选操作2
/// 【统计每个作者 每本书 收藏数】
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult Group2()
{
var pipeline = new List<BsonDocument>
{
new ("$group", new BsonDocument
{
{ "_id", new BsonDocument
{
{ "name", "$author.name" },
{ "title", "$title" }
}
}, //双分组条件，但是最外层必须写_id，表示主键
{ "totalCount", new BsonDocument("$sum", "$favCount") }
})
};
var result = bookService.Aggregate<BsonDocument>(pipeline).ToList();
List<GroupData2> groupData = [];
foreach (var item in result)
{
GroupData2 data = new()
{
authName = item["_id"]["name"].AsString,
bookTitle = item["_id"]["title"].AsString,
totalCount = item["totalCount"].AsInt32
};
groupData.Add(data);
}
return Json(new { status = "ok", msg = "获取成功", data = groupData });
}
4. $lookup关联查询
(1). 查询顾客以及该顾客关联的订单信息
/// <summary>
/// 04-1-关联查询1
/// 【查询顾客以及该顾客关联的订单信息】
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult LookUp1()
{
var pipeline = new List<BsonDocument>
{
new ("$lookup", new BsonDocument
{
{ "from", "order" },
{ "localField", "customerCode" },
{ "foreignField", "customerCode" },
{ "as", "customerOrder" }
})
};
//写法1
var result1 = customerCollection.Aggregate<Customer2>(pipeline).ToList();
return Json(new { status = "ok", msg = "获取成功", data = result1 });
//写法2
//如果你就是不想result1那种单独构建Customer2类的话，那么就转换成json字符串，让前端自己去处理了
//var result2 = customerCollection.Aggregate<BsonDocument>(pipeline).ToList(); //没办法直接返回，会报错
//string jsonData = Newtonsoft.Json.JsonConvert.SerializeObject(result2);
//return Json(new { status = "ok", msg = "获取成功", data = jsonData });
}
(2). 查询订单以及该订单关联的顾客信息和订单详情
/// <summary>
/// 04-2-关联查询2
/// 【查询订单以及该订单关联的顾客信息和订单详情】
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult LookUp2()
{
var pipeline = new List<BsonDocument>
{
new ("$lookup", new BsonDocument
{
{ "from", "customer" },
{ "localField", "customerCode" },
{ "foreignField", "customerCode" },
{ "as", "curstomer" }
}),
new ("$lookup", new BsonDocument
{
{ "from", "orderItem" },
{ "localField", "orderId" },
{ "foreignField", "orderId" },
{ "as", "orderItem" }
})
};
//写法1
var result = orderCollection.Aggregate<Order2>(pipeline).ToList();
return Json(new { status = "ok", msg = "获取成功", data = result });
}
二. 索引详解
这里不再重复了，相见有道笔记中 mongodb的第二节。
!
- 作 者 : Yaopengfei(姚鹏飞)
- 博客地址 : http://www.cnblogs.com/yaopengfei/
- 声 明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
- 声 明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。
