Query Operators

• Query operators allow us additional ways to locate data within database.

• $ in mongodb is used in multiple scenarios when prefixed. These use cases are as follows,

  • Precedes MQL operators and Update Operators.

  • Precedes Aggregation pipeline stages.

  • Accesing field values.

Comparison Operators

• $eq - Equal to operator.

• $ne - Not equal to operator.

• $gt - Greater than operator.

• $lt - Less than operator.

• $gte - Greater than equal to operator.

• $lte - Less than equal to operator.

  • Syntax for operators

      {field: {operator: value}}

  • Example

      db.accounts.find({"age": {"$gte": 18}})

Logic Operators

• $and - Returns result that match all specified query clauses.

• $or - Returns result that match atleast one specified query clauses.

• $nor - Returns result that fail to match specified query clauses.

  • Syntax for $and, $or, $nor operators

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

  • Example

        {$or: [ {"age":18}, {"rank": {"$lt": 100}} ] } 

  • $and is an implicit operator used when an operator is not specified.

  • Example

        db.accounts.find(
        {
            "account_id": {"$gt": 25, "$lt":100}
        })

  • In the above query an implicit $and operator is used to find accounts between 25 and 100 ( (25, 100) ) open range.

  • But when should one use explicit $and operator ?

    • When you need to include the same operator more than once in a query then use the operator explicitly.

    • Example

      db.companies.find(
          { "$and": 
              [ 
                  {"$or": 
                      [ {"founded_year": 2004}, {"founded_month":10}]
                  }, 
                  {"$or":
                      [ {"category_code":"social"}, {"category_code": "web"}] 
                  }
              ]
          })

• $not - Returns result that negates the query requirements.

  • Syntax for $not operator

        {$not: {statement}}

Expressive Operator

• $expr operator allows for more complex queries for comparing fields within a document by using aggregation expression.

• When $ symbol is appended to document field it yields the field value.

• Example

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

  • Note, in the above query we are using aggregation syntax $gt, part of Aggregation operator and not MQL syntax.

Array Operators

• Querying an array using an array returns only documents with queried array, consisting of all the elements specified in the query, in the same order.

• Querying a document with array as querying field (i.e restriction) then, it will return documents which has one value as an array element.

• Example:

      db.listingsAndReviews.find({ "amenities": "Wifi"})

  • In the above example, suppose amenities is an array field in the document and "Wifi" is one of the values. It looks like we are querying a field that has "Wifi" as its value.

• Use the below operators only against an array field.

  • $all: Return all documents containing all the elements specified in the query array, regardless of the order of elements.

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

  • $size: Returns documents containing array field of size specified.

    db.listingsAndReviews.find(
        { "amenities": {
             "$size": 20
        }})
  

  • $elemMatch:

    • This operator matches documents that contain an array field with atleast one element that matches the specified criteria.

    • Example:

        db.grades.find(
            { "scores": 
                { "$elemMatch": 
                    { "type": "extra credit" 
                    } 
                }
            }).pretty()

    • This operator can also be used with projections.

    • Example:

        db.grades.find(
            { "class_id": 431 },    //restriction
            { "scores":             //projection
                { "$elemMatch": 
                    { "score": 
                        { "$gt": 85 } 
                    } 
                }
             }).pretty()

  • . : Dot operator (aka Dot notation)

    • Querying elements in an array which consists of sub document or arrays.

    • To use dot operator in arrays, specify position of element in the array.

    • Example:

        db.companies.find(
            { "relationships.0.person.first_name": "Mark",
              "relationships.0.title": {"$regex": "CEO" } 
            },
            { "name": 1 }).pretty()