数据准备
var tags = ["nosql","mongodb","document","developer","popular"];
var types = ["科技","社会","旅游","小说","文学"];
var books=[];
for(var i=0;i<100;i++){
var typeIdx = Math.floor(Math.random()*types.length);
var tagIdx = Math.floor(Math.random()*tags.length);
var tagIdx2 = Math.floor(Math.random()*tags.length);
var favCount = Math.floor(Math.random()*100);
var username = "xx00"+Math.floor(Math.random()*10);
var age = 10 + Math.floor(Math.random()*15);
var book = {
title: "book-"+i,
type: types[typeIdx],
tag: [tags[tagIdx],tags[tagIdx2]],
favCount: favCount,
author: {name:username,age:age}
};
books.push(book)
}
db.book.insertMany(books);
基本查询
过滤
db.book.find({type:"文学"}).pretty()
范围查询
收藏数量在90~95之间
db.book.find({favCount:{"$gt":90,"$lt":95}}).pretty()
嵌套查询
作者姓名=xx007
db.book.find({"author.name":"xx007"}).pretty()
返回指定列
不显示id, 只显示标题、作者姓名
db.book.find({}, {_id:0,"title":1,"author.name":1}).pretty()
排序
按收藏数量降序
db.book.find().sort({favCount:-1})
统计数量
db.book.count({"type":"科技"})
去重
所有书籍类型去重
db.book.distinct("type")
过滤
作者=xx007 且 类型=小说
db.book.find({"author.name":"xx007", type:"小说"}).pretty()
类型=小说 或者 标题=book-48
db.book.find({"$or":[{type:"小说"},{title:"book-48"}]}).pretty()
IN
db.book.find({title:{$in:["book-89","book-99"]}})
模糊查询
标题包含8
db.book.find({"title":{$regex:"book-8"}})
标题以book-9开头
db.book.find({"title":{$regex:"^book-9"}})
标题以9结尾
db.book.find({"title":{$regex:"9$"}})
管道
group
按类型分组,统计每种类型数量
db.book.aggregate([{"$group":{"_id":"$type","count":{"$sum":1}}}])
按作者分组,求平均收藏数量
db.book.aggregate( [ { "$group": { _id:"$author.name", "平均收藏数量": {"$avg":"$favCount"} } }])
match
先过滤再分组
db.book.aggregate(
[ { "$match": {"author.name": "xx000"} }, { "$group": { "_id": "$type","count": {"$sum": 1} } } ] )
unwind
把数组字段拆开,平铺展示,一条记录变多条记录
作者=xx007, 按照tag分别展示
db.book.aggregate({"$match":{"author.name":"xx007"}},{"$unwind":"$tag"})
先按tag拆分,再统计每个作者tag的集合
db.book.aggregate( [ {"$unwind":"$tag"}, { "$group": { _id:"$author.name", "tags": {"$addToSet":"$tag"} } }])
project
列重取别名
db.book.aggregate( [ { "$group": { _id:"$author.name", "cnt": {"$sum":"$favCount"} }}, {"$project": { _id:0, "作者": "$_id", "收藏总数": "$cnt" } }])
联表查询
数据准备
-- 订单表
db.orders.insertMany([
{ "_id" : 1, "sku" : "almonds", "price" : 12, "quantity" : 2 },
{ "_id" : 2, "sku" : "pecans", "price" : 20, "quantity" : 1 }
,{ "_id" : 3 }
]);-- 库存表
db.inventory.insertMany([
{ "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
{ "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
{ "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
{ "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
{ "_id" : 5, "sku": null, description: "Incomplete" },
{ "_id" : 6 }
]);
查订单,同时查对应库存
db.orders.aggregate( [ { "$lookup": { "from": "inventory", "localField": "sku", "foreignField": "sku", "as": "inventory" } }])