MongoDB admin

To view result pretty 

db.yourcollection.find().pretty()

To view the commands sent to mongodb

For example, the query is sent by mongoose and in the code the query contains variable, how to view the exact query executed on MongoDB?
db.setLogLevel(3, 'command')
db.setLogLevel(3, 'query')
https://docs.mongodb.com/manual/reference/method/db.setLogLevel/

MongoDB query practice

Here is the data

{"id":1, "priority":10}

{"id":2, "priority":9}

{"id":3, "priority":11} 

Now, the input parameter is id=3, and I want to get the records which id is 3 and the record which priority is just before the record which id=3. 

{"id":3, "priority":11}

{"id":1, "priority":10}

if the input parameter id=2, it shall return 

{"id":2, "priority":9}

If the input parameter id=1, it shall return 

{"id":1, "priority":10}

{"id":2, "priority":9}

Can it be done in one query?

delete records

db.users.deleteMany({ status : "A" })

updateMany

this is like the sql update with where clause, which update a bunch of documents satisfy the conditions.

db.restaurant.updateMany(
      { violations: { $gt: 4 } },
      { $set: { "Review" : true } }
   )

count and sort by count result

db.quote.aggregate([{$group: {_id:"$Symbol", quoteCount:{$sum:1}}, {$sort:{quoteCount:-1}}  }])

contains

db.users.findOne({"username" : {$regex : ".*son.*"}});

sort and get last N result

db.collection.find({}).sort("_id", -1).limit(N)
be careful, you cannot put in sort({"id": -1}), that will give you the error.

find documents which don't contain one field

db.prediction.find({isCorrect:{$exists:false}})

create unique index

db.quote.createIndex({"Symbol":1,"Date":1}, {unique:true})

allowDiskUse

db.users.aggregate( 
 [  
  { $group : { _id : "$key", number : { $sum : 1 } } },
  ...
 ], {
  allowDiskUse:true
 }
); 

Update field name

db.prediction.update({}, {$rename:{"date":"Date"}}, false, true)

distinct value

db.prediction.distinct("Prediction", {"Date":"2017-07-11"})

query pipeline

db.prediction.aggregate(
 // Pipeline
 [
  // Stage 1
  {
   $match: {
    $and: [ { "Date": { $gte: "2017-08-01" } }, { "Date": { $lte: "2017-08-15" } } ]
   }
  },
  // Stage 2
  {
   $group: {
     _id:"$Symbol", count:{$sum:1}
   }
  },
  // Stage 3
  {
   $match: {
    count: {$lt:10}
   }
  },
  // Stage 4
  {
   $group: {
    _id:'all', total_count:{$sum:1}
   }
  },
 ]
);

$lookup to join collections

db.quote.aggregate(
 // Pipeline
 [
  // Stage 1
  {
   $match: {
    Date:'2017-08-14'
   }
  },
  // Stage 2
  {
   $match: {
    Symbol:'KO'
   }
  },
  // Stage 3
  {
   $lookup: {
       "from" : "prediction",
       "localField" : "Symbol",
       "foreignField" : "Symbol",
       "as" : "Prediction_Doc"
   }
  }
 ]
);

$unwind to denormalize result

db.quote.aggregate(
 // Pipeline
 [
  // Stage 1
  {
   $match: {
    Date:'2017-08-14'
   }
  },
  // Stage 2
  {
   $match: {
    Symbol:'KO'
   }
  },
  // Stage 3
  {
   $lookup: {
       "from" : "prediction",
       "localField" : "Symbol",
       "foreignField" : "Symbol",
       "as" : "Prediction_Doc"
   }
  },
  // Stage 4
  {
   $unwind: "$Prediction_Doc"
  },
 ]
);

https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/

Join on multiple fields

Join on one field first, then use redact eq on another field. It could take a long time if both collections are big.

db.quote.aggregate(
 // Pipeline
 [
  // Stage 1
  {
   $match: {
    Date:'2017-08-11'
   }
  },
  // Stage 2
  {
   $lookup: {
       "from" : "prediction",
       "localField" : "Symbol",
       "foreignField" : "Symbol",
       "as" : "Prediction_Doc"
   }
  },
  // Stage 3
  {
   $unwind: "$Prediction_Doc"
  },
  // Stage 4
  {
   $redact: {
       "$cond": [
               { "$eq": [ "$Date", "$Prediction_Doc.Date" ] }, 
               "$$KEEP", 
               "$$PRUNE"
           ]
   }
  },

 ]
);

https://stackoverflow.com/questions/37086387/join-two-collections-on-mutiple-field-using-lookup

Match on sub document field

continue the last example, we can add another match at the end of the pipe.

   $match: {
    'Prediction_Doc.Mode':'mode1'
   }

);

$redact to restrict the contents of the documents 

Restricts the contents of the documents based on information stored in the documents themselves.
https://docs.mongodb.com/manual/reference/operator/aggregation/redact/#pipe._S_redact

MongoDB 聚合操作
http://www.cnblogs.com/ljhdo/p/5019837.html
https://docs.mongodb.com/manual/aggregation/

SQL to Aggregation Mapping Chart
https://docs.mongodb.com/manual/reference/sql-aggregation-comparison/

MongoDb trim field and update it