~~NOTOC~~
<WRAP noprint>
|<100% 25% - >|
^  \\ DATA ANALYTICS REFERENCE DOCUMENT\\ \\   ^^
^  Document Title:|The HDIP Data Analytics MongoDB quick reference sheet|
^  Document No.:|1552663033|
^  Author(s):|Gerhard van der Linde, Rita Raher|
^  Contributor(s):|  |



**REVISION HISTORY**
|< 100% 10% - - 10% 17% 10% >|
^  \\  Revision\\  \\  ^\\ Details of Modification(s)^\\ Reason for modification^  \\ Date  ^  \\ By  ^
|  [[:doku.php?id=help:mongodb:cheatsheet&do=revisions|0]]  |Draft release|Drafting the The HDIP Data Analytics MongoDB cheat sheet|  2019/03/15 15:17  |Gerhard van der Linde, Rita Raher|


----
</WRAP>
====== MongoDB Quickref Sheet ======

<WRAP group>

<WRAP third column>
**Admin Commands**
<code javascript>
help
db.help()

show dbs
show databases
use <db>
show collections
</code>

**aggregate $ keywords** ((https://docs.mongodb.com/manual/reference/operator/aggregation/group/#pipe._S_group)) ((https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/))

<code mongo>
$group
  $avg
  $min
  $max
  $sum
  $first
  $last
$lookup
$project
</code>

</WRAP>
<WRAP third column>

**DB Commands**
<code javascript>
db.collection.save()
db.collection.find()
db.colle....find().pretty()
db.collection.findOne()

db.collection.insert()
db.collection.update()
</code>

**Sorting** ((https://docs.mongodb.com/manual/reference/method/cursor.sort/))
<code mongo>
cursor.sort()
</code>

</WRAP>
<WRAP third column>

**find/update $ Keywords**
<code javascript>
$and: [{},{}]
$or: [{},{}]
$in: [1,2,3,7]
$set:{{},{}}
$unset:{field:1}
$rename:{"old":"new"}

$exists:true/false
$gt:1
$gte:1
$lt:1
$lte:1
</code>
</WRAP>
</WRAP>

===== Import/Export data =====


<code javascript>
// import csv into empty collection
mongoimport --db userdb --collection user --type csv --headerline --file C:\Use...\users.csv
</code>

<code javascript>
// add csv to existing collection
mongoimport --db userdb --collection users --type csv --headerline --mode merge --file C:\Use...\sex.csv
</code>

<code javascript>
// import json into empty collection
mongoimport --db proj --collection docs --type json --file C:\Users\121988\Documents\52553\mongo.json
</code>

<code javascript>
//export an exixting collection
mongoexport.exe /db:userdb /collection:users /jsonArray /pretty /out:users.json
</code>
===== Find Examples =====

find(<color #ffaec9>query</color>)
<code javascript>
db.user.find({age:{$gt:19}})
</code>

<code javascript>
db.user.find({$and: [{age:{$gt:19}}, {carReg: {$exists:true}}]})
</code>

find(<color #ffaec9>query</color>, <color #00a2e8>projection</color>)
<code javascript>
db.User.find({age: {$gt: 20}}, {email:1})
</code>

<code javascript>
//Return only the first_name and surname attributes of all documents 
db.User.find({}, {_id:false, first_name:1, surname:1})
</code>

<code mongo>
db.student.find({address:{$exists: true}}, {_id:0, "address.county":1})                 
</code>
===== Update Examples =====

**Note the use of <color #ed1c24>$set</color> and <color #ed1c24>$unset</color>**
<code javascript>
db.users.update({_id:105},{$set:{"carReg":"161-MO-4"}})
</code>

<code javascript>
db.users.update({$and: [{Sex:"M"},{age:{$gt:20}}]},{$set:{"title":"Mr."}},{multi:true})
</code>

<code javascript>
db.users.update({_id:{$in:[101,103,107]}},{$set:{carReg:""}},{multi:true})
</code>

<code javascript>
db.users.update({_id:{$in:[101,103,107]}},{$unset:{carReg:1}},{multi:true})
</code>
<code javascript>
db.users.updateMany({},{$rename:{"fname":"Name"}})
</code>

===== Aggregate Examples =====

db.collection.aggregate ((https://docs.mongodb.com/manual/reference/method/db.collection.aggregate/)) ([<color #ffaec9>$match</color>, <color #00a2e8>$group</color>, <color #ffc90e>$sort</color>])

<code mongo>
db.collection.aggregate([{$match:{status:"A"}},{$group:{_id:"$cust_id",total:{$sum:"$amount"}}},{$sort:{total:-1}}])
</code>

==== $group ====

<color #ed1c24>$group</color> same as Group by in **MySQL** 
<code mongo>
//Get the average gpa for all students 
db.users.aggregate([{$group:{_id:null, Average:{$avg:"$gpa"}}}])
</code>
<code mongo>
//Get the Maximum GPA per age group 
db.march8.aggregate([{$group:{_id:"$age", "Max GPA per Age":{$max:"$gpa"}}}])
</code>
<code mongo>
//To sort: $sort
db.march8.aggregate([{$group:{_id:"$age", "Max GPA per Age":{$max:"$gpa"}}}, {$sort:{_id:1}}])
</code>
==== $lookup====

**$lookup**: {<color #ffaec9>from</color>, <color #00a2e8>localField</color>, <color #ffc90e>foreignField</color>, <color #c8bfe7>as</color>}

^  from: |<collection to join>|
^  localField: |<field from the input documents>|
^  foreignField: |<field from the documents of the "from" collection>|
^  as: |<output array field>|

<code mongo>
// .aggregate([{$lookup}])
db.docs.aggregate([{$lookup:{from:"docs", localField:"modules", foreignField: "_id", as:"Details"}}])
</code>
<code mongo>
// .aggregate([{$match},{$lookup}])
db.cityinfo.aggregate([{$match:{name:"Tom"}},{$lookup:{from:"cityinfo", localField:"addresses", foreignField: "_id", as:"Details"}}])
</code>
<code mongo>
// .aggregate([{$match},{$lookup},{$project}]) 
db.cityinfo.aggregate([{$match:{city:"CHESTERFIELD"}},{$lookup:{from:"cityinfo", localField:"state", foreignField:"_id", as:"StateInfo"}},{$project:{capital:0}}])
</code>
===== Query, Update, Options =====
 
db.collection.update ((https://docs.mongodb.com/manual/reference/method/db.collection.update/#db.collection.update)) (<color #ffaec9>query</color>, <color #ff7f27>update</color>, <color #00a2e8>options</color>)

<code mongodb>
db.collection.update(
   <query>,
   <update>,
   {
     upsert: <boolean>,
     multi: <boolean>,
     writeConcern: <document>,
     collation: <document>,
     arrayFilters: [ <filterdocument1>, ... ]
   }
)
</code>

===== Indexing =====
<code mongo>
db.collection.getIndexes()
</code>
<code mongo>
db.user.createIndex({age:1})
</code>
<code mongo>
db.collection.dropIndex({age:1})
</code>
**Note:** The index on **_id** cannot be dropped