MongoDB and employees & departments

I know that lot of you made their first Oracle steps with the emp/dept repository. And lot of DBA/developers came  back often to these examples to test more complex queries.

Even if you never played with Oracle emp/dept examples, I keep these examples enough simple.

(Anyway, you can find here the exemples to play with:

Oracle employees and department table queries in sql

)

So let’s make the same steps with MongoDB and JSON documents.

Since MongoDB does not anything about schemas, it will implicitly create one when you ask it to use it.

use scott

Mongo has its collections, which corresponds to RDBMS tables, and its documents, which are rows.

All rows does not need to have the same structure in the JSON document, we will see it later.

And you don’t need to explicitly create a collection, it will be implicitly created when you insert a first document in it.

db.emp.insert({empno:1,ename: “Bob”, sal: 100000})
WriteResult({ “nInserted” : 1 })

To check the result of our insertion:

db.emp.find()
{ “_id” : ObjectId(“583981276543762b35a7a6a1”), “empno” : 1, “ename” : “Bob”, “sal” : 100000 }

We see that MongoDB has associated the key (ObjectId)

We can insert another employee, who has the commission:

db.emp.insert({empno:1,ename: “John”, sal: 9000, comm:100})
WriteResult({ “nInserted” : 1 })

And even another one, who has an additional field, that does not have the same type, let’s call it remark:

db.emp.insert({empno:1,ename: “Tom”, sal: 9000, remark:”half time”})
WriteResult({ “nInserted” : 1 })

This is related to what we previously said, all rows do not need to have the same structure in one JSON document.

Now, let’s make some basic queries.

We want to find employees that earn 100000 or more:

db.emp.find({“sal”: {$gte:100000}})
{ “_id” : ObjectId(“583981276543762b35a7a6a1”), “empno” : 1, “ename” : “Bob”, “sal” : 100000 }

I’ll let you imagine (and test) other exemples, with additional keywords:

$gt,$lt,$gte,$lte

Can you guess what they mean ? I think they are self-explanatory.
(For people not familiar with unix shell commands, this means: greater than, lower than,greater or equal than, lower or equal than)

Now, I showed previously that the rows (in fact, the documents) do not need to have the same structure.

So this is quite possible:

db.emp.insert({empno:2,ename: “Eric”, sal: 5000,dname: “DALLAS”})
WriteResult({ “nInserted” : 1 })
> db.emp.insert({empno:2,ename: “Michel”, dname: “DALLAS”, comm: 90})
WriteResult({ “nInserted” : 1 })

If we ask all employees who work in Dallas department, we get:

db.emp.find({“dname”: “DALLAS”})
{ “_id” : ObjectId(“583ab7626543762b35a7a6a9”), “empno” : 2, “ename” : “Eric”, “sal” : 5000, “dname” : “DALLAS” }
{ “_id” : ObjectId(“583ab77a6543762b35a7a6aa”), “empno” : 2, “ename” : “Michel”, “dname” : “DALLAS”, “comm” : 90 }

Again we see, the DNAME field of the document does not need to be at the same position for all documents in the collection, which is mandatory in the case of rows and tables in the RDBMS world.

Remember:

There is no “CREATE USER”  (or “CREATE DATABASE”)  command, since USE dbname is enough to create a new database.

No need to do “CREATE COLLECTION”  (DDL CREATE TABLE in RDBMS ) command, since the INSERT command creates a collection, if it does not exist, and inserts the first document.

However, the command db.CreateCollection exists, and is used to created capped collections, that have fixed size.

OK,OK …  but in fact all this is just about the table EMP, but how do we model the DEPT table in MongoDB and especially the relationship between the two?

See about this in the next post=>

MongoDB and employees & departments, part 2

www.lyticsware.com