_id value can be a document
Search:
Return documents if a field exists
db.COLLECTION.find( { FIELD: {$exists: TRUE||FALSE} } )
db.COLLECTION.find( { FIELD1: 1 }, {FIELD1:1, FIELD2:1, _id:0} ) // similar to select FIELD1, FIELD2 FROM COLLECTION where FIELD1 = 1;
Return documents that are a certain type:
db.COLLECTION.find( { FIELD: { $type: INT } } ) // LOOK AT DOCUMENTATION FOR INT VALUES
REGEX search
db.COLLECTION.find( { FIELD: { $regex: “SEARCHTERM” } } )
Multi search parameters
db.COLLECTION.find( { FIELD1: { $regex: “SEARCHTERM”}, FIELD2: { $exists: true } } ) // searches for in FIELD1 for SEARCHTERM and if FIELD2 exists
OR
db.COLLECTION.find( { $or : [ { FIELD1: {} }, { FIELD2: {} } ] )
db.users.find( { $or : [ { name: { $regex: “e$” } }, { age: { $exists: true } } ] )
AND
db.COLLECTION.find( { $and : [ { FIELD1: {} }, { FIELD2: {} } ] )
db.product.find( { price: {$gt: 50}}, {price: {$lt: 60}} } ) // trick question, returns ALL less than 60
Aggregation
To do order by
db.COLLECTION.aggregate ( [
{$group : { _id: { “NAME”: “$FIELD”, num_products: {$sum: 1 } } }
] )
equivalent to select FIELD as NAME, count(*) as num_products order by FIELD
db.COLLECTION.aggregate ( [
{$group : { _id: { “NAME”: “$FIELD”, “SUM_TEXT”: {$sum: “$SUM_FIELD” } } }
] )
select FIELD as NAME, sum(*) order by FIELD
Update a field in a document
db.COLLECTION.update( { FIELDTOGETDOCUMENT: "VALUE" }, { $set : { FIELDTOCHANGE: "CHANGEVALUE" } } ) db.products.update( { sku: "abc123" }, { $set: { quantity: 500, instock: true } } )
Index:
db.COLLECTION.ensureIndex( { FIELD: 1 })
db.COLLECTION.ensureIndex( { FIELD1: 1, FIELD2: 1 }) // this is a multikey index only used if find has FIELD or FIELD1 and FIELD2. A find with only FIELD2 will not use index
db.COLLECTION.ensureIndex( { FIELD1: 1, FIELD2: 1, FIELD3: 1 }) // this is a multikey index only used if find has FIELD1 or FIELD1 and FIELD2, or FIELD1, FIELD2, FIELD3. A find with only FIELD2, FIELD2 and Field3, or FIELD3 will not use index
db.COLLECTION.ensureIndex( { FIELD: 1}, {unique: true} ) //ensure only unique indexes { unique: true, dropDups:true } // dangerous because don't know which duplicate document gets dropped
db.COLLECTION.dropIndex( { FIELD: 1 } );
To retrieve what fields are Indexed
db.COLLECTION.getIndexes()
db.system.indexes.find()
To Backup and restore:
backup
mongodump --db DATABASENAME
mongodump –db DATABASENAME –collection COLLECTION
restore
mongorestore FOLDERTODUMPDATA
Delete Document
db.COLLECTION.remove( {FIELD: “”} )
Add collection
db.createCollection(“COLLECTION”)
Delete collection
db.COLLECTION.drop()
Delete database
use DATABASENAME
db.dropDatabase()
Import Data (jsonArray file)
mongoimport --db DATABASE --collection COLLECTION --jsonArray --file FILENAME.json
Performance:
Explain() shows performance and processing detail. Shows # of docs scanned, time to return results, whether it uses index or not
db.COLLECTION.find( {FIELD: 1} ).explain()
When cursor says BtreeCursor that means it is using index
Returns information like size,count of COLLECTION
db.COLLECTION.stats()
Size of Index. ideally size is small enough to stay in memory no on HD
db.COLLECTION.totalIndexSize()
Hint to MongoDB to use
db.COLLECTION.find().hint()
db.COLLECTION.find().hint({})
db.COLLECTION.find().hint( {$natural: 1} )
MongoDB has a log that automatically logs slow queries (> 100ms)
mongod –dbpath /usr/local/var/mongodb
Profiler
system.profile lv 0 – off lv 1 – log slow queries lv 2- log all queries (more for debugging)
mongod –db path –profile 1 –slowms 2
db.getProfilingLevel();
db.getProfilingStatus()
db.setProfilingLevel(1, 4)
db.setProfilingLevel(0) // turn off profiling
db.system.profile.find().pretty()
db.system.profile.find( { ns: /test.foo/} ). sort( { ts:1 } );
db.system.profile.find( { millis: {$gt: 1} } ). sort( { ts:1 } );
MongoTop lets you see usage like linux top command. time spent in collection (like unix iostat command)
mongostat