MongoDB QueryData

Querying a collection

The find() function can be used to query the documents.

The format is:

 db.collectionName.find(optional_query_criteria)

Where the query_criteria follows a pattern:

 db.collectionName.find({"fieldName": "value"})

The fieldName must be in quotes, the value needs quotes if it is a string or date value.

For example, show all the data so far in the deptCollection:

db.deptCollection.find()

The data comes back messy. The pretty() function can be used to improve the layout::

db.deptCollection.find().pretty()

To find just one document - department 10:

db.deptCollection.find({"deptno":10}).pretty()

Finding an employee means using the array name too:

db.deptCollection.find({"employees.empno":7902}).pretty()

However, this does mean you get back all the employees in the department they were found in!

Since Version 2.2 MongoDB's new $elemMatch can be used with arrays to return only the first element matching the $elemMatch condition:

db.deptCollection.find({"deptno":20},  
  { _id: 0, employees: {$elemMatch: {empno: 7902}}}).pretty()

$elemMatch limits the contents of the employees array to contain only the first element matching the $elemMatch condition.

This is akin to a SQL query:

 SELECT * FROM Emp WHERE deptno=20 AND empno = 7902

_id is a unique value automatically generated by MongoDB (like a Primary Key, except it is unique for the whole database).

Using _id:0 suppresses the value, however to see it:

db.deptCollection.find({"deptno":20},  
  { employees: {$elemMatch: {empno: 7902}}}).pretty()

More about _ids in the next section.

The query criteria can be as complex as that found in SQL.

To find all employees earning more than 2000 in department 10:

db.deptCollection.find({"deptno":10},   
 { employees: {$elemMatch: {sal: { $gt: 2000}}}}).pretty()

Same again for department 20 and the managers:

db.deptCollection.find({"deptno":20},  { employees: {$elemMatch: {sal: { $gt: 2000}, "job": "MANAGER"}}}).pretty()

employees is an array, so $elemMatch only returns the first matching value. What if we try this instead:

db.deptCollection.find({ "employees.sal" : { $gt: 2000}}).pretty()

Updating the collection