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:

https://jastrebicdragutin.wordpress.com/2020/12/16/emp-and-dept-table-queries/

)

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=>

https://jastrebicdragutin.wordpress.com/2019/06/30/mongodb-and-employees-departments-part-2/

There are several possibilities in MongoDB to make our DEPT/EMP model.

The first possibility is to make embedded documents like this:

db.dept.insert(

{“dept”: “DALLAS”,
“emp” :{“ename” :”Eric”,”sal”: “5000”,”comm” : “500”}

},

{“dept”: “DALLAS” ,
“emp”:{“ename” :”Michel”,”sal”: “6000”,”comm” : “100”}

}
)

So EMP is embedded inside DEPT.  I put intentionally this white spaces in order to make the commands more clear. If you do copy and paste in your MongoDB environnement, it will work.

Nothing prevents you to write the code in this way:

db.dept.insert({“dept”: “DALLAS”, “emp” :{“ename” :”Eric”,”sal”: “5000”,”comm” : “500”}},{“dept”: “DALLAS” ,”emp”:{“ename” :”Michel”,”sal”: “6000”,”comm” : “100”} }
)

But it is less clear IMHO.

Ok, and now, we’ll do some updates.

For exemple, in SQL we will do the following to increase the salary of one of the employees.

update emp

set sal=sal+100

where empno=1234

The same thing in MongoDB will be done with the following command:

db.emp.update({“empno”:”1234″},{“$inc”:{“sal”:100}})

The $inc operator is used to add the 100 dollars to the salary of the employee with empno 1234.

Note: In MongoDB the command update will change the value of the first document found, not the entire subset returned.  In our case above there is only one document returned so it does not matter, but if we have chosen some other criteria, for example to increase for 100 dollars all the employees that earn 1000 dollars, this will update just the first document found.

db.emp.update({“sal”:”1000″},{“$inc”:{“sal”:100}})

So an additional clause will be necessary to update more documents

db.emp.update({“sal”:”1000″},{“$inc”:{“sal”:100}},true)