Difference between revisions of "MongoDB QueryData"

From mi-linux
Jump to navigationJump to search
Line 14: Line 14:
  
 
<pre style="color: blue">
 
<pre style="color: blue">
  db.collectionName.find({fieldName: "value"})
+
  db.collectionName.find({keyField: "value"})
 
</pre>
 
</pre>
  
Line 22: Line 22:
 
* all names and values are case sensitive
 
* all names and values are case sensitive
 
* quotes are optional for the fieldName, so long as they do not contain spaces
 
* 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
 
  
  
 
=== Find all documents ===
 
=== Find all documents ===
  
For example, show all the data so far in the deptCollection:
+
For example, show all the data so far in the dept collection:
  db.deptCollection.find()
+
  db.dept.find()
  
 
The data comes back messy. The pretty() function can be used to improve the layout::
 
The data comes back messy. The pretty() function can be used to improve the layout::
  
  db.deptCollection.find().pretty()
+
  db.dept.find().pretty()
  
  
Line 39: Line 38:
 
To find just one document - department 10:
 
To find just one document - department 10:
  
  db.deptCollection.find({deptno:10}).pretty()
+
  db.dept.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:
 
 
 
<pre style="color: blue">
 
SELECT * FROM Emp WHERE deptno=20 AND empno = 7902
 
</pre>
 
 
 
_id is a unique value automatically generated by MongoDB (like a Primary Key, except it is unique for the whole database).
 
 
 
Using <b>_id:0</b> 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.
 
  
 
== Find with Query Criteria ==
 
== Find with Query Criteria ==

Revision as of 10:34, 11 November 2017

Main Page >> MongoDB >>MongoDB Workbook >> Querying Collections

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({keyField: "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


Find all documents

For example, show all the data so far in the dept collection:

db.dept.find()

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

db.dept.find().pretty()


Find One document

To find just one document - department 10:

db.dept.find({deptno:10}).pretty()

Find with Query Criteria

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


Next Step

MongoDB Aggregation Pipeline