MongoDB基本操作

数据准备

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"
    }
  }])