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/