MongoDB QueryNestedData

Main Page >> MongoDB >>MongoDB Workbook >> Querying Collections with nested data

Querying a collection

The find() function can be used as before 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"})

Note:

the criteria is enclosed in curly brackets: {}
the value needs quotes if it is a string or date value
all names and values are case sensitive
quotes are optional for the fieldName, so long as they do not contain spaces
if the fieldName refers to a nested document, the name must be in matching single or double quotes

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 ObjectId 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()

Things to note:

This time employees.sal must be enclosed in matching single or double quotes.
Comment on what the above query returns.
If you examine the data carefully:
- either find() means if one element of an array is found to be true, then all the elements are returned, even if they do not satisfy the query criteria
- or including $elemMatch means only the first element matching the query criteria is returned.
Is this good practice?

Find departments with no managers:

db.deptCollection.find({ "employees.job" : { $ne: "MANAGER"}}).pretty()

MongoDB Aggregation Pipeline and nested data