Query Operations in MongoDB

1) Create a Collection

If the collection does not currently exist, insert operations will create the collection.

db.inventory.insertOne(
{ item: “canvas”, qty: 100, tags: [“cotton”], size: { h: 28, w: 35.5, uom: “cm” } }
)

2) Get a document from collection

db.inventory.find( { item: "canvas" } )

3) Insert multiple documents

db.inventory.insertMany([
{ item: “journal”, qty: 25, tags: [“blank”, “red”], size: { h: 14, w: 21, uom: “cm” } },
{ item: “mat”, qty: 85, tags: [“gray”], size: { h: 27.9, w: 35.5, uom: “cm” } },
{ item: “mousepad”, qty: 25, tags: [“gel”, “blue”], size: { h: 19, w: 22.85, uom: “cm” } }
])

4) Query on Embedded or Nested Documents

Consider a db that contains the following documents.
{ item: “journal”, qty: 25, size: { h: 14, w: 21, uom: “cm” }, status: “A” },
{ item: “notebook”, qty: 50, size: { h: 8.5, w: 11, uom: “in” }, status: “A” },
{ item: “paper”, qty: 100, size: { h: 8.5, w: 11, uom: “in” }, status: “D” },
{ item: “planner”, qty: 75, size: { h: 22.85, w: 30, uom: “cm” }, status: “D” },
{ item: “postcard”, qty: 45, size: { h: 10, w: 15.25, uom: “cm” }, status: “A” }

To get a document whose size is { h: 14, w: 21, uom: “cm” }

db.inventory.find( { size: { h: 14, w: 21, uom: "cm" } } )

4.1) Query on nested field

To specify a query condition on fields in an embedded/nested document, use dot notation (“field.nestedField”)

db.inventory.find( { “size.uom”: “in” } )

5) Query operators

5.1) $lt —> Less than operator

db.inventory.find( { “size.h”: { $lt: 15 } } )

5.2) $in — > Selects the documents where the value of a field equals any value in the specified array.

db.inventory.find( { qty: { $in: [ 5, 15 ] } } )

This query selects all documents in the inventory collection where the qty field value is either 5 or 15.

5.3) $and — > Performs a logical AND operation on an array of two or more expressions and selects the documents that satisfy all the expressions in the array.

db.inventory.find( { $and: [ { price: { $ne: 1.99 } }, { price: { $exists: true } } ] } )

5.4) $exists — > Matches documents that have the specified field.

5.5) $expr — >Allows the use of aggregation expressions within the query language.
Example:
{ “_id” : 1, “category” : “food”, “budget”: 400, “spent”: 450 }
{ “_id” : 2, “category” : “drinks”, “budget”: 100, “spent”: 150 }
{ “_id” : 3, “category” : “clothes”, “budget”: 100, “spent”: 50 }
{ “_id” : 4, “category” : “misc”, “budget”: 500, “spent”: 300 }
{ “_id” : 5, “category” : “travel”, “budget”: 200, “spent”: 650 }

The following operation uses will find documents where the ‘spent’ amount exceeds the ‘budget’:

db.monthlyBudget.find( { $expr: { $gt: [ "$spent" , "$budget" ] } } )

Using $expr With Conditional Statements

Example:
{ “_id” : 1, “item” : “binder”, “qty”: 100 , “price”: 12 }
{ “_id” : 2, “item” : “notebook”, “qty”: 200 , “price”: 8 }
{ “_id” : 3, “item” : “pencil”, “qty”: 50 , “price”: 6 }
{ “_id” : 4, “item” : “eraser”, “qty”: 150 , “price”: 3 }

Consider a supplies collection with the following documents. If the qty is at least 100, a 50% discount is applied. Otherwise, a 25% discount is applied:
We need to find documents where the price is less than 5 after the applied discounts.

db.supplies.find( {
    $expr: {
       $lt:[ {
          $cond: {
             if: { $gte: ["$qty", 100] },
             then: { $divide: ["$price", 2] },
             else: { $divide: ["$price", 4] }
           }
       },
       5 ] }
} )

5.6) $regex — > Provides regular expression capabilities for pattern matching strings in queries. MongoDB uses Perl compatible regular expressions (i.e. “PCRE” ) version 8.41 with UTF-8 support.
Use one of the following syntax:
{ <field>: { $regex: /pattern/, $options: ‘<options>’ } }
{ <field>: { $regex: ‘pattern’, $options: ‘<options>’ } }
{ <field>: { $regex: /pattern/<options> } }

Example:
{ name: { $in: [ /^acme/i, /^ack/ ] } }
{ name: { $regex: /acme.*corp/, $options: ‘i’, $nin: [ ‘acmeblahcorp’ ] } }

5.7) $text — > refer document: https://docs.mongodb.com/manual/reference/operator/query/text/

5.8) $where — >By passing either a string containing a JavaScript expression or a full JavaScript function to the query system. It provides greater flexibility, but requires that the database processes the JavaScript expression or function for each document in the collection. Reference the document in the JavaScript expression or function using either this or obj.

6) Array Query Operators

6.1) $all — > Selects the documents where the value of a field is an array that contains all the specified elements.
Example:
{
_id: ObjectId(“5234cc89687ea597eabee675”),
code: “xyz”,
tags: [ “school”, “book”, “bag”, “headphone”, “appliance” ],
qty: [
{ size: “S”, num: 10, color: “blue” },
{ size: “M”, num: 45, color: “blue” },
{ size: “L”, num: 100, color: “green” }
]
}

{ tags: { $all: [ "school" , "bag" ] } }

5.2) $elemMatch — >Matches documents that contain an array field with at least one element that matches all the specified query criteria.

6) Project Fields to Return from Query

6.1)Return the Specified Fields and the _id Field Only
db.inventory.find( { status: “A” }, { item: 1, status: 1 } )

6.2) Suppress _id Field
db.inventory.find( { status: “A” }, { item: 1, status: 1, _id: 0 } )

6.3) Return All But the Excluded Fields
db.inventory.find( { status: “A” }, { status: 0, instock: 0 } )

6.4) Return Specific Fields in Embedded Documents
db.inventory.find({ status: “A” },{ item: 1, status: 1, “size.uom”: 1 })

Complex Queries:

Data:
[
{
“productId”: “123”,
“rating”: 20,
“customerId”: “”,
“customerReviewText”: “”
},
{
“productId”: “123”,
“rating”: 2,
“customerId”: “”,
“customerReviewText”: “”
},
{
“productId”: “123”,
“rating”: 3,
“customerId”: “”,
“customerReviewText”: “”
},
{
“productId”: “123”,
“rating”: 1,
“customerId”: “”,
“customerReviewText”: “”
},
{
“productId”: “432”,
“rating”: 3,
“customerId”: “”,
“customerReviewText”: “”
},
{
“productId”: “432”,
“rating”: 3,
“customerId”: “”,
“customerReviewText”: “”
}
]

Find average of rating of a product:

db.collection.aggregate([
{
$match: {
productId: “123”
}
},
{
“$group”: {
“_id”: null,
“avgRating”: {
“$avg”: “$rating”
}
}
},
{
$project: {
avgRating: 1,
_id: 0
}
}
])

Explanation:

1. In the match stage, it gives all the docs that has productId = “123”
2. In the group stage, we have provide value of _id=null. If you specify an _id value of null, or any other constant value, the $group stage calculates accumulated values for all the input documents as a whole.
   The $group stage has the following prototype form:

{
  $group:
    {
      _id: <expression>, // Group By Expression
      <dynamic field>: { <accumulator1> : <expression1> },
      ...
    }
 }

3) Project stage selects/hides the attributes from the result.

2) Using the same data, find the count of individual rating

db.collection.aggregate([
{
$match: {
productId: “123”
}
},
{
“$group”: {
_id: “$rating”,
count: {
$sum: 1
}
}
}
])

Output:

[
{
“_id”: 1,
“count”: 1
},
{
“_id”: 20,
“count”: 1
},
{
“_id”: 3,
“count”: 1
},
{
“_id”: 2,
“count”: 1
}
]

Explanation:

$sum:1 expression will keep on adding 1 while going through each document that belongs to a particular group.

If you specify $sum:10, then each time 10 will be added. So if rating:2 has count 4, then final count will be 40.