Difference between revisions of "MongoDB QueryData"

From mi-linux
Jump to navigationJump to search
 
(18 intermediate revisions by the same user not shown)
Line 7: Line 7:
 
The format is:
 
The format is:
  
−
<pre style="color: blue">
+
<pre style="color: purple">
 
  db.collectionName.find(optional_query_criteria)
 
  db.collectionName.find(optional_query_criteria)
 
</pre>
 
</pre>
Line 13: Line 13:
 
Where the query_criteria follows a pattern:
 
Where the query_criteria follows a pattern:
  
−
<pre style="color: blue">
+
<pre style="color: purple">
−
  db.collectionName.find({fieldName: "value"})
+
  db.collectionName.find({keyField: "value"})
 
</pre>
 
</pre>
  
Line 20: Line 20:
 
* the criteria is enclosed in curly brackets: {}
 
* the criteria is enclosed in curly brackets: {}
 
* the value needs quotes if it is a string or date value
 
* 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
 
* 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 results should look like:
 +
 
 +
<pre style="color: blue">
 +
{ "_id" : 10, "deptno" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
 +
{ "_id" : 20, "deptno" : 20, "dname" : "RESEARCH", "loc" : "DALLAS" }
 +
{ "_id" : 40, "deptno" : 40, "dname" : "OPERATIONS", "loc" : "BOSTON" }
 +
{ "_id" : 50, "deptno" : 40, "dname" : "OPERATIONS V2", "loc" : "BOSTON" }
 +
{ "_id" : 30, "deptno" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
 +
 
 +
</pre>
 +
 
 +
To show the documents in the ''emp'' collection:
 +
 
 +
db.emp.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.emp.find().pretty()
  
 +
A subset of the ''emp'' collection is shown below:
  
−
=== Find One document ===
+
<pre style="color: blue">
 +
> db.emp.find().pretty()
 +
{
 +
        "_id" : ObjectId("5a09e79ac536e890d5a7a666"),
 +
        "empno" : 7782,
 +
        "ename" : "CLARK",
 +
        "job" : "MANAGER",
 +
        "mgr" : 7839,
 +
        "hiredate" : ISODate("1989-06-09T00:00:00Z"),
 +
        "sal" : 2450,
 +
        "deptno" : 10
 +
}
 +
{
 +
        "_id" : ObjectId("5a09e79ac536e890d5a7a667"),
 +
        "empno" : 7839,
 +
        "ename" : "KING",
 +
        "job" : "PRESIDENT",
 +
        "hiredate" : ISODate("1980-11-17T00:00:00Z"),
 +
        "sal" : 5000,
 +
        "deptno" : 10
 +
}
 +
....
 +
 
 +
</pre>
 +
 
 +
Note the object ids are now system generated (and will be different values in your own data).
  
−
To find just one document - department 10:
+
=== Find with query criteria ===
  
−
db.deptCollection.find({deptno:10}).pretty()
 
  
−
Finding an employee means using the array name too:
+
If working with a large collection, you will not want all the documents returned.
−
db.deptCollection.find({"employees.empno":7902}).pretty()
 
  
−
However, this does mean you get back all the employees in the department they were found in!
+
Find all the employees are are clerks:
  
−
Since Version 2.2 MongoDB's new '''$elemMatch''' can be used with arrays to return only the '''first''' element matching the $elemMatch condition:
+
  db.emp.find({job:"CLERK"})
  
−
db.deptCollection.find({deptno:20},
+
For numerical data, the greater than (>) and less than (<) operators are represented by ''$gt'' and ''$lt'' respectively. Note, for these operators, the search criteria must be enclosed in {} brackets.
−
  { _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.
+
Find all employees who earn more than 2400:
 +
db.emp.find({sal: {$gt:2400}})
  
−
This is akin to a SQL query:
+
Find all employees whose commission is less than 1000:
 +
db.emp.find({comm: {$lt:1000}})
  
−
<pre style="color: blue">
+
Working with the date field (hiredate) is more complex, since you have to create a new date for the comparison.
−
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).
+
For example, find all employees who start after the 1st January 2000:
 +
db.emp.find({hiredate: {$lt: new Date("2000-01-01")}})
  
−
Using <b>_id:0</b> suppresses the ''ObjectId'' value, however to see it:
+
Find employees who started on the 16th October 2015:
 +
db.emp.find({hiredate: new Date("2015-10-16")})
  
−
db.deptCollection.find({deptno:20}, 
+
=== Find One document ===
−
  { employees: {$elemMatch: {empno: 7902}}}).pretty()
 
  
−
More about _ids in the next section.
+
To find just one document requires the use of the equivalent of a primary key field. This can be a field that the user takes responsibility to keep unique, such as the ''deptno'':
  
−
== Find with Query Criteria ==
+
db.dept.find({deptno:10})
  
−
The query criteria can be as complex as that found in SQL.
 
  
−
To find all employees earning more than 2000 in department 10:
+
Or the object id can be used, which will be unique:
  
−
  db.deptCollection.find({deptno:10}, 
+
  db.dept.find({_id:10})
−
  { employees: {$elemMatch: {sal: { $gt: 2000}}}}).pretty()
 
  
  
−
Same again for department 20 and the managers:
+
In the ''emp'' collection, the _ids are system generated and generally along the lines of: '5a0727e99ba81dee9b1cc6a3', so less easy to use!
−
db.deptCollection.find({deptno:20},  { employees: {$elemMatch: {sal: { $gt: 2000}, job: "MANAGER"}}}).pretty()
 
  
 +
List all the records in emp:
  
−
'''employees''' is an array, so $elemMatch only returns the first matching value. What if we try this instead:
+
db.emp.find().pretty()
  
−
db.deptCollection.find({ "employees.sal" : { $gt: 2000}}).pretty()
+
and pick an _id from the collection and then try and find one record.  
  
−
Things to note:
+
For example (note, your object id will be different):
−
* 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?
 
  
 +
db.emp.find( {_id : ObjectId("5a0727e99ba81dee9b1cc6a3")}).pretty()
  
−
Find departments with no managers:
 
−
 
−
db.deptCollection.find({ "employees.job" : { $ne: "MANAGER"}}).pretty()
 
  
 +
The function ''ObjectId()'' must be used to convert the value into an object id.
  
 
== Next Step ==
 
== Next Step ==
  
 
[[MongoDB_Aggregate_Pipeline|MongoDB Aggregation]] Pipeline
 
[[MongoDB_Aggregate_Pipeline|MongoDB Aggregation]] Pipeline

Latest revision as of 15:13, 5 March 2018

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 results should look like: 

{ "_id" : 10, "deptno" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
{ "_id" : 20, "deptno" : 20, "dname" : "RESEARCH", "loc" : "DALLAS" }
{ "_id" : 40, "deptno" : 40, "dname" : "OPERATIONS", "loc" : "BOSTON" }
{ "_id" : 50, "deptno" : 40, "dname" : "OPERATIONS V2", "loc" : "BOSTON" }
{ "_id" : 30, "deptno" : 30, "dname" : "SALES", "loc" : "CHICAGO" }

To show the documents in the emp collection: 

db.emp.find()


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

db.emp.find().pretty()

A subset of the emp collection is shown below: 

> db.emp.find().pretty()
{
        "_id" : ObjectId("5a09e79ac536e890d5a7a666"),
        "empno" : 7782,
        "ename" : "CLARK",
        "job" : "MANAGER",
        "mgr" : 7839,
        "hiredate" : ISODate("1989-06-09T00:00:00Z"),
        "sal" : 2450,
        "deptno" : 10
}
{
        "_id" : ObjectId("5a09e79ac536e890d5a7a667"),
        "empno" : 7839,
        "ename" : "KING",
        "job" : "PRESIDENT",
        "hiredate" : ISODate("1980-11-17T00:00:00Z"),
        "sal" : 5000,
        "deptno" : 10
}
....

Note the object ids are now system generated (and will be different values in your own data).

Find with query criteria

If working with a large collection, you will not want all the documents returned.

Find all the employees are are clerks: 

 db.emp.find({job:"CLERK"})

For numerical data, the greater than (>) and less than (<) operators are represented by $gt and $lt respectively. Note, for these operators, the search criteria must be enclosed in {} brackets.

Find all employees who earn more than 2400: 

db.emp.find({sal: {$gt:2400}})

Find all employees whose commission is less than 1000: 

db.emp.find({comm: {$lt:1000}})

Working with the date field (hiredate) is more complex, since you have to create a new date for the comparison.

For example, find all employees who start after the 1st January 2000: 

db.emp.find({hiredate: {$lt: new Date("2000-01-01")}})

Find employees who started on the 16th October 2015: 

db.emp.find({hiredate: new Date("2015-10-16")})

Find One document

To find just one document requires the use of the equivalent of a primary key field. This can be a field that the user takes responsibility to keep unique, such as the deptno: 

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


Or the object id can be used, which will be unique: 

db.dept.find({_id:10})


In the emp collection, the _ids are system generated and generally along the lines of: '5a0727e99ba81dee9b1cc6a3', so less easy to use!

List all the records in emp: 

db.emp.find().pretty()

and pick an _id from the collection and then try and find one record.

For example (note, your object id will be different): 

db.emp.find( {_id : ObjectId("5a0727e99ba81dee9b1cc6a3")}).pretty()


The function ObjectId() must be used to convert the value into an object id.

Next Step

MongoDB Aggregation Pipeline

Retrieved from "https://mi-linux.wlv.ac.uk/wiki/index.php?title=MongoDB_QueryData&oldid=10007"