no way to compare when less than two revisions

Differences

This shows you the differences between two versions of the page.

@@ Line 1: / Line 1: @@
+~~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