Home 4. Advanced CRUD Operations
Post
Cancel

4. Advanced CRUD Operations

Posted 2022-06-15 Updated 2022-06-16 7 min read

MQL Operators

  • Update Operators: Example: $inc, $set, $unset These update operators enable us to modify data in the database.

  • Query Operators: Query Operators provides additional ways to locate data within the database. What Query Operators have in common with all kinds of operators is the $ sign that precedes the operator.

    Note: $ has multiple uses in MongoDB like:

    • It precedes MQL operators
    • It precedes aggregation pipeline stages.
    • Allows to access field values.
  • Comparison Operators:
    • $eq = EQual to : Allows to compare if the two values are equal
    • $ne = Not Equal to : Allows us to compare if the two values are not equal
    • $gt = Greater Than
    • $gte = Greater Than or Equal
    • $lt = Less Than
    • $lte = Less Than or Equal

    The Syntax for the Comparision Operators is:

    Syntax:

    1
2
3
4
5

          {
          <field> : {
              <operator> : <value>
          }
      }

    Example-1:

    1
2
3
4
5
6
7
8
9
10
11

          db.trips.find(
                      { 
                          "tripduration": { 
                                              "$lte" : 70 
                                          },
                          "usertype": { 
                                          "$ne": "Subscriber"
                                      } 
                      }
                  ).pretty()

    The above query finds all the documents in the trips collections where the tripduration was less than or equal to 70 seconds and the usertype was not Subscriber

    Example-2:

    1
2
3
4
5
6
7
8
9
10

          db.trips.find(
                      { 
                          "tripduration": { 
                                              "$lte" : 70 
                                          },
                          "usertype": { 
                                          "$eq": "Customer" 
                                      }
                      }
                  ).pretty()

    The above query finds all the documents where the tripduration was less than or equal to 70 and the usertype was Customer using a redundant equality operator.

    Example-3:

    1
2
3
4
5
6
7
8

          db.trips.find(
                      { 
                          "tripduration": { 
                                              "$lte" : 70 
                                          },
                          "usertype": "Customer" 
                      }
                  ).pretty()

    The above query finds all the documents where the tripduration was less than or equal to 70 seconds and the usertype was Customer using the implicit equality operator.

  • Logic Operators In MQL, we have the standard set of foru logical operators which are given below:-
    • $and: Returns the documents that meets all of the specified query clauses.
    • $or: Returns the documents as long as at least one of the query clauses is matched.
    • $nor: Returns all documents that fails to match both clauses.
    • $not: Negates the query requirements and therefore returns all the documents that do not match the query.

    The and, or and nor have the similar syntax where the operator precedes an array of clauses that it will operate o.

    Syntax for and, or and nor operators:

    1
2
3

          {
          <operator>: [{statement1}, {statement2},...]
      }

    Syntax for not operator:

    1
2
3

          {
          $not: {statement}
      }

    Example-1:

    1
2
3
4
5
6
7
8
9
10
11
12
13

          db.inspections.find(
          {
              '$nor': [{
                  'result': 'No violation'
              }, 
              {
                  'result': 'Pass'
              },
              {
                  'result': 'Fail'
              }]
          }
      )

    The above query ensures that every document with the result set to No violation or Pass or Fail will not be part of the result.

    Example-2:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14

      db.grades.find({
          '$and': [
              {
                  'student_id': {
                      '$gt': 25
                  }
              }, 
              {
                  'student_id': {
                      '$lt': 100
                  }
              }
          ]
      })

    The above query finds all the students where the student_id is greater than 25 and less than 100 in the sample_training.grades ccollections.

    But we could also simplify it significantly as we’re querying on the same field, we can get rid of the implied $and. Then, we can also combine both conditions in a single statement like:

    1
2
3
4
5
6

          db.grades.find({
          'student_it': {
              '$gt': 25,
              '$lt':100
          }
      })

    The above query does the same as the one above it but only simpler.

    Example-3:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

          db.routes.find(
                      { "$and": [ 
                                  { "$or" :[ 
                                              { "dst_airport": "KZN" },
                                              { "src_airport": "KZN" }
                                          ] 
                                  },
                                  { "$or" :[ 
                                              { "airplane": "CR2" },
                                              { "airplane": "A81" } 
                                          ] 
                                  }
                              ]
                      }
                  ).pretty()

    The above query finds all documents where airplanesCR2 or A81 left or landed in the KZN airport.

  • Expressive Query Operator The $expr is an expressive query operator meaning it can do more than one simple operation. It allows the use of aggregation expressions within the query language and it uses this syntax. It allows for more complex queries and for comparing fields within a document.

    Syntax:

    1
2
3
4
5

          {
          $expr: {
              <expression>
          }
      }

    The $expr also allows us to use variables and conditional statements.

    Example-1:

    1
2
3
4
5
6
7
8
9

          db.trips.find(
                      { "$expr": { 
                          "$eq": [ 
                                      "$end station id", 
                                      "$start station id"
                                  ] 
                                  }
                      }
                  ).count()

    The above query allows us to find all the documents where the trip started and ended at the same station.

    Example-2:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

          db.trips.find(
                      {
                          '$expr': {
                              '$and': [
                                  {
                                      '$gt': [
                                          '$tripduration', 1200
                                      ]
                                  },
                                  {
                                      '$eq': [
                                          '$end station id', '$start station id'
                                      ]
                                  }
                              ]
                          }
                      }
                  ).count()
  • Array Operators and Projection

    • $push: It allows us to add an element to an array. It also allows us to turn a field into an array field if it was previously a different type.

    • $sall: It returns a cursor with all docuements in which the specified array field contains all the given elements, regardless of their order in the array.

    • $size: It returns all documents whree the specified array field is exactly the given length.

    Example:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

          db.listingsAndReviews.find(
                                  { "amenities": {
                                                  "$size": 20,
                                                  "$all": [ 
                                                              "Internet", 
                                                              "Wifi", 
                                                              "Kitchen",
                                                              "Heating",
                                                              "Family/kid friendly",
                                                              "Washer", 
                                                              "Dryer", 
                                                              "Essentials",
                                                              "Shampoo", 
                                                              "Hangers",
                                                              "Hair dryer", 
                                                              "Iron",
                                                              "Laptop friendly workspace"
                                                          ]
                                                  }
                                  }
                              ).pretty()

    The above query finds all the documetns with exactly 20 amenities which includes all the amenities listed in the query array.

    When we look at the sample_airbnb dataset, we see documents with lots of fields that often don’t fit on the screen. To mitigate this, we can add a projection to our Find queries and only look at fiends that we’re interested in at the moment.

    Example:

    1
2
3
4
5
6
7
8
9
10
11
12

          db.listingsAndReviews.find(
          {
              'amenities': {
                  '$size': 20, 
                  '$all': ['Internet', 'Wifi', 'Kitchen', 'Heating']
              }
          }, 
          {
              'price': 1,
              'address': 1
          }
      ).pretty()

    The first part of the find() query describes the content that we’re looking for. The second is a projection, describing specifically which fields we’re looking for. This way, the cursor doesn’t have to include every single field in the result set.

    Note: When using projection, you can specify which fields you do or do not want to see in the resulting cursor. Use 1 to specify the fields that you want to see, and 0 to specify the fields that you don’t want to see. You cannot mix zeros and ones in a single projection.

    • $elemMatch: An Array Operator that can be used both in query and projection part of the find command. It matches the documents that contain an array field with at least one element that matches the specified query criteria.

    Syntax:

    1
2
3
4
5
6
7

          {
          <field>: {
              '$elemMatch': {
                  <field> : <value>
              }
          }
      }

    Example-1:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14

          db.grades.find(
          {
              'class_id': 431
          },
          {
              'scores': {
                  '$elemMatch': {
                      'score': {
                          '$gt': 85
                      }
                  }
              }
          }
      ).pretty()

    Example-2:

    1
2
3
4
5
6
7
8
9

          db.grades.find(
          {
              'scores': {
                  '$elemMatch': {
                      'type': 'extra credit'
                  }
              }
          }
      ).pretty()
MongoDB, MongoDB Basics
mongodb
This post is licensed under CC BY 4.0 by the author.
Recently Updated
Contents