The Khangaonkar Report: MongoDb Query tutorial and cheatsheet

Mongodb querying is easy and very powerful. But it is handy to have a cheatsheet around when digging for data. In this tutorial, we list and describe some simple useful MongoDB queries.

If you are new to Mongodb, you can read my mongodb introduction.

At the bottom of this page, there is some example json representing some customers.

Copy that to a file say customer.json.

Import into your mongodb database using the command

mongoimport --db yourtestdb --collection customer --file customer.json

1. Find all documents in a collection

> db.customer.find()

{ "_id" : ObjectId("5a22eae84427950fd314ccca"), "firstname" : "Dana", "lastname" : "Dealer", "age" : 60, "sex" : "F", "status" : "Y", "address" : { "city" : "Seattle", "state" : "WA" }, "favorites" : [ "yellow", "orange" ], "recent" : [ { "product" : "p5", "price" : 110 }, { "product" : "p2", "price" : 66 } ] }

{ "_id" : ObjectId("5a22eae84427950fd314cccb"), "firstname" : "Dan", "lastname" : "RunsFra", "age" : 23, "sex" : "M", "status" : "N", "address" : { "city" : "LOS Angeles", "state" : "CA" }, "favorites" : [ "red", "organge" ], "recent" : [ { "product" : "p1", "price" : 85 }, { "product" : "p4", "price" : 8 } ] }

{ "_id" : ObjectId("5a22eae84427950fd314cccc"), "firstname" : "Mike", "lastname" : "North", "age" : 45, "sex" : "M", "status" : "Y", "address" : { "city" : "burlingame", "state" : "CA" }, "favorites" : [ "red", "blue" ], "recent" : [ { "product" : "p1", "price" : 85 }, { "product" : "p2", "price" : 66 } ] }

2. Find all documents based on 1 field equality

> db.customer.find({"lastname":"Dealer"})

3. Find all documents based on multiple fields AND

AND is implicit

> db.customer.find({"firstname":"Dana","lastname":"Dealer"})

Same query with explicit $and operator

> db.customer.find({$and : [{"firstname":"Dana"},{"lastname":"Dealer"}]})

4. Multiple fields OR

db.customer.find({$or : [{"sex":"F"},{status:"N"}]})

5. Comparison operator

db.customer.find({"age":{$lt:30}} )

db.customer.find({"age":{$gt:50}} )

6. Embedded document nested field

db.customer.find({"address.state":"CA"})

{ "_id" : ObjectId("5a22eae84427950fd314cccb"), "firstname" : "Dan", "lastname" : "RunsFra", "age" : 23, "sex" : "M", "status" : "N", "address" : { "city" : "LOS Angeles", "state" : "CA" }, "favorites" : [ "red", "orange" ], "recent" : [ { "product" : "p1", "price" : 85 }, { "product" : "p4", "price" : 8 } ] }

7. Array element

db.customer.find({"favorites":"blue"})

8. Array of embedded docs

db.customer.find({"recent.price":{$gt:90}})

9. Project only certain fields - such as only lastname

db.customer.find({},{"lastname":1})

{ "_id" : ObjectId("5a22eae84427950fd314ccca"), "lastname" : "Dealer" }

{ "_id" : ObjectId("5a22eae84427950fd314cccb"), "lastname" : "RunsFra" }

{ "_id" : ObjectId("5a22eae84427950fd314cccc"), "lastname" : "North" }

10. Sort

Ascending by age

db.customer.find({}).sort({"age":1})

Descending by age

db.customer.find({}).sort({"age":-1})

Appendix 1 : Sample data

{
"firstname": "Mike",
"lastname": "North",
"age": 45,
"sex": "M",
"status": "Y",
"address": {
"city": "burlingame",
"state": "CA"
},
"favorites": ["red", "blue"],
"recent": [{
"product": "p1",
"price": 85
}, {
"product": "p2",
"price": 66
}]
}
{
"firstname": "Dan",
"lastname": "RunsFra",
"age": 23,
"sex": "M",
"status": "N",
"address": {
"city": "LOS Angeles",
"state": "CA"
},
"favorites": ["red", "orange"],
"recent": [{
"product": "p1",
"price": 85
}, {
"product": "p4",
"price": 8
}]
}
{
"firstname": "Dana",
"lastname": "Dealer",
"age": 60,
"sex": "F",
"status": "Y",
"address": {
"city": "Seattle",
"state": "WA"
},
"favorites": ["yellow", "orange"],
"recent": [{
"product": "p5",
"price": 110
}, {
"product": "p2",
"price": 66
}]
}

Related Blogs :

1. Mongo DB Introduction

The Khangaonkar Report

Sunday, December 3, 2017

MongoDb Query tutorial and cheatsheet