Back Home

Back Home

Overview

Mongo and Sql databases use similar if different terms:

SQL MongoDb
database database
table collection
row document
column field
primary key primary key

Queries

Some of the basics

Query Meaning
myCollection.find() return the list of of all values in the collection as a json document
myCollection.find().fetch() return the results as an array
myCollection.find({ name: “David” }).fetch() find a node with an individual field value
myCollection.find().count(); the number of matching nodes (in this case in the collection

Find based on the default object id

Query Meaning
db.companies.find({ _id : ObjectId(“5586cfc5a60bdfbdba6c37b5”) }) find the matching id
  • Mongo now also provides a dsl query class

Using MongoDB QueryBuilder Dsl queries look like

DBObject query = new QueryBuilder()
.start()
.and(new QueryBuilder().start().put("lname").is("Ford").get(),
new QueryBuilder().start().put("marks.english")
.greaterThan(35).get()).get();

Monitoring

media.tumblr.com_d6f42f1b21558dbc19d15c3b2ba6f5b7_tumblr_nf0sgszj4k1qavt6co1_1280.jpg

Installing on Linux

64 bit

cd /tmp
wget http://fastdl.mongodb.org/linux/mongodb-linux-x86_64-1.8.1.tgz
sudo tar -zxf /tmp/mongodb-linux-x86_64-1.8.1.tgz -C /usr/local

32 Bit

wget http://fastdl.mongodb.org/linux/mongodb-linux-i686-2.2.1.tgz
sudo tar -zxf http://fastdl.mongodb.org/linux/mongodb-linux-i686-2.2.1.tgz

Setup some symbolic links.

sudo ln -s /usr/local/mongodb-linux-x86_64-1.8.1 /usr/local/mongodb
sudo ln -s /usr/local/mongodb/bin/bsondump /usr/local/bin/bsondump
sudo ln -s /usr/local/mongodb/bin/mongo /usr/local/bin/mongo
sudo ln -s /usr/local/mongodb/bin/mongod /usr/local/bin/mongod
sudo ln -s /usr/local/mongodb/bin/mongodump /usr/local/bin/mongodump
sudo ln -s /usr/local/mongodb/bin/mongoexport /usr/local/bin/mongoexport
sudo ln -s /usr/local/mongodb/bin/mongofiles /usr/local/bin/mongofiles
sudo ln -s /usr/local/mongodb/bin/mongoimport /usr/local/bin/mongoimport
sudo ln -s /usr/local/mongodb/bin/mongorestore /usr/local/bin/mongorestore
sudo ln -s /usr/local/mongodb/bin/mongos /usr/local/bin/mongos
sudo ln -s /usr/local/mongodb/bin/mongosniff /usr/local/bin/mongosniff
sudo ln -s /usr/local/mongodb/bin/mongostat /usr/local/bin/mongostat

The first “ln -s” above sets up a handy symbolic link between the versioned mongodb folder and its unversioned counterpart. When 10Gen release updates, say version 1.8.2, all you need to do is download, unzip, and link the ’1.8.2 mongodb folder’ to the unversioned folder and ‘hey presto’ everything should just work.

To get an init script working cleanly with this setup, download mine from my Github ‘dotfiles’ repo. Please note – my init script enables journaling and the REST interface (on line 51).

wget https://github.com/ijonas/dotfiles/raw/master/etc/init.d/mongod
sudo mv mongod /etc/init.d/mongod
sudo chmod +x /etc/init.d/mongod

You’ll need to add a mongodb user and prep some folders

sudo /usr/sbin/useradd mongodb
sudo mkdir -p /var/lib/mongodb
sudo mkdir -p /var/log/mongodb
sudo chown mongodb.mongodb /var/lib/mongodb
sudo chown mongodb.mongodb /var/log/mongodb

Also, you need to activate your MongoDB service’s init script by adding it to your system’s run-level configuration. That way the service will startup during the boot sequence and stop nicely during the OS’ shutdown procedure.

sudo update-rc.d mongod defaults

Lastly to launch MongoDB

/etc/init.d/mongod start

Reduce the Mongodb Footprint - Especially for Development

MongoDB comes configured out of the box for maximum performance and reliability on production databases. But it can be a bit of a disk hog, and if you’re using a development environment with an SSD like me (which I highly recommend), disk space might be scarce. After doing a little research, I found configuration settings that significantly reduce MongoDB’s disk usage.

Edit your MongoDB configuration file (/etc/mongod.conf) and add some/all of the following:

smallfiles = true

Uses smaller data file sizes — starting at 16MB instead of 128MB — and create fewer files initially. This can save almost 200MB on small collections (each!).

oplogsize = 100 (MB)

If you’re using an oplog for replication (or just for update notifications), you can set the size smaller than the default of “5% of all disk space”.

nojournal = true

MongoDB 2.0 introduced journaling, which is great for production environments, but not very useful in development. You can disable it and save several GB

Mongo Queries

Comparing MongoDB versus SQL syntax

MongoDB Sql Syntax
SELECT * FROM books db.books.find()
SELECT * FROM books WHERE id = 3; dbbooks.find( { id : 3 } )
SELECT * FROM books WHERE name LIKE 'Oliver%'
db.books.find( { name : /^Oliver/ } )  
SELECT * FROM books WHERE name like'%Oliver%'
db.books.find( { name : /Oliver/ } )  
SELECT * FROM books WHERE publisher= 'Dover Publications' AND published_date = “2011-8-01” db.books.find( { publisher : “Dover Publications”, published_date : ISODate(“2011-8-01”) } )
SELECT * FROM books WHERE published_date > “2011-8-01” db.books.find ( { published_date : { $gt : ISODate(“2011-8-01”) } } )
SELECT * FROM books WHERE published_date > “2011-8-01” db.books.find ( { published_date : { $gt : ISODate(“2011-8-01”) } } )
SELECT name FROM books ORDER BY published_date db.books.find( {}, { name : 1 } ).sort( { published_date : 1 } )
SELECT name FROM books ORDER BY published_date DESC db.books.find( {}, { name : 1 } ).sort( { published_date : -1 } )
SELECT votes.name from books JOIN votes where votes.book_id = books.id db.books.find( { votes : { $exists : 1 } }, { votes.name : 1 } )
SELECT * from books WHERE author like upper('harry') regular expression

Insert

INSERT INTO users(user_id, age, status) VALUES (“bcd001”,45,“A”) db.users.insert({ user_id: “bcd001”, age: 45, status: “A” } )

Update

UPDATE users SET status = “C” WHERE age > 25 db.users.update({ age: { $gt: 25 } }, { $set: { status: “C” } },{ multi: true } )

Delete

DELETE FROM users WHERE status = “D” db.users.remove( { status: “D” } )

Upsert

Mongo has additional compound queries, to insert or update depending whether the query exists

db.inventory.update(
   { item: "TBD1" },
   {
     item: "TBD1",
     details: { "model" : "14Q4", "manufacturer" : "ABC Company" },
     stock: [ { "size" : "S", "qty" : 25 } ],
     category: "houseware"
   },
   { upsert: true }
)

Date Queries

Some api's (eg. java) Date fields will be save in Mongo as Long

"{Timestamp" : NumberLong("20130919161948")}"

This then allows one to compare 2 documents by Timestamp by simply checking > or < for the yyyyMMddHHmmss format.

Basic Sort

db.getCollection('distributionDeal').find().sort( { "PhysicalReleaseSchedule.LastUpdated" : 1})

Aggregation Sort Sort and Order by

Mongo from Java

Java operations on Mongo

  • find
BasicDBObject query = new BasicDBObject();
BasicDBObject field = new BasicDBObject();
field.put("HomeTown", 1);
DBCursor cursor = db.getCollection(collectionName).find(query,field);
while (cursor.hasNext()) {
    BasicDBObject obj = (BasicDBObject) cursor.next();
    result.add(obj.getString("HomeTown"));
}

Mongo Administration Console

login to the mongo instance from the local shell with mongo

use admin // switch to admin 
db.auth("admin", "*****")  // switch to the admin authorised user 
 
mongodb.txt · Last modified: 2017/08/20 06:59 by root
 
RSS - 200 © CrosswireDigitialMedia Ltd