Expressions and operators (arithmetic, comparison, date, string, etc.)

Author：Chuan Chen 阅读数：17482人阅读分类： MongoDB

Expressions and Operators (Arithmetic, Comparison, Date, String, etc.)

MongoDB provides a rich set of expressions and operators for data processing in aggregation pipelines, query conditions, and update operations. These operators cover arithmetic operations, comparison operations, date handling, string manipulation, and more, enabling complex data operations.

Arithmetic Operators

Arithmetic operators are used to perform basic mathematical operations, including addition, subtraction, multiplication, and division. In aggregation pipelines, these operators are commonly used to calculate field values or generate new fields.

// Example: Using $add to calculate total price
db.orders.aggregate([
  {
    $project: {
      totalPrice: {
        $add: ["$price", "$tax"]
      }
    }
  }
])

// Example: Using $multiply to calculate discounted price
db.products.aggregate([
  {
    $project: {
      discountedPrice: {
        $multiply: ["$price", 0.8]
      }
    }
  }
])

Common arithmetic operators include:

$add: Addition
$subtract: Subtraction
$multiply: Multiplication
$divide: Division
$mod: Modulo
$abs: Absolute value
$ceil: Round up
$floor: Round down
$round: Round to nearest integer
$sqrt: Square root
$pow: Exponentiation

Comparison Operators

Comparison operators are used to compare two values and return a Boolean result. These operators are useful in both query conditions and aggregation expressions.

// Example: Query products with price greater than 100
db.products.find({
  price: { $gt: 100 }
})

// Example: Comparing dates in an aggregation pipeline
db.orders.aggregate([
  {
    $match: {
      $expr: {
        $gt: ["$orderDate", "$dueDate"]
      }
    }
  }
])

Key comparison operators include:

$eq: Equal to
$ne: Not equal to
$gt: Greater than
$gte: Greater than or equal to
$lt: Less than
$lte: Less than or equal to
$in: In an array
$nin: Not in an array
$cmp: Compare two values, returning -1, 0, or 1

Date Operators

MongoDB provides a rich set of date operators for handling date fields and performing date calculations.

// Example: Calculating order processing days
db.orders.aggregate([
  {
    $project: {
      processingDays: {
        $dateDiff: {
          startDate: "$orderDate",
          endDate: "$shipDate",
          unit: "day"
        }
      }
    }
  }
])

// Example: Extracting parts of a date
db.events.aggregate([
  {
    $project: {
      year: { $year: "$date" },
      month: { $month: "$date" },
      day: { $dayOfMonth: "$date" },
      hour: { $hour: "$date" }
    }
  }
])

Common date operators:

$dateFromString: Convert string to date
$dateToString: Format date as string
$dayOfYear: Get day of year
$dayOfMonth: Get day of month
$dayOfWeek: Get day of week
$year/$month/$hour etc.: Get parts of date
$dateAdd: Add to a date
$dateSubtract: Subtract from a date
$dateDiff: Calculate difference between dates

String Operators

String operators are used to manipulate text data, including concatenation, substring extraction, and case conversion.

// Example: Concatenating first and last names
db.users.aggregate([
  {
    $project: {
      fullName: {
        $concat: ["$firstName", " ", "$lastName"]
      }
    }
  }
])

// Example: Converting case
db.products.aggregate([
  {
    $project: {
      nameUpper: { $toUpper: "$name" },
      descriptionLower: { $toLower: "$description" }
    }
  }
])

Key string operators:

$concat: Concatenate strings
$substr/$substrBytes/$substrCP: Extract substring
$toLower: Convert to lowercase
$toUpper: Convert to uppercase
$trim: Trim whitespace from both ends
$ltrim: Trim left whitespace
$rtrim: Trim right whitespace
$split: Split string into array
$strLenBytes: Get string length in bytes
$strLenCP: Get string length in code points
$indexOfBytes/$indexOfCP: Find position of substring

Logical Operators

Logical operators are used to combine multiple conditions to build complex logical expressions.

// Example: Combining multiple conditions
db.products.find({
  $and: [
    { price: { $gt: 50 } },
    { price: { $lt: 200 } },
    { stock: { $gt: 0 } }
  ]
})

// Example: Using conditional logic in aggregation
db.orders.aggregate([
  {
    $project: {
      status: {
        $cond: {
          if: { $gte: ["$amount", 1000] },
          then: "VIP",
          else: "Standard"
        }
      }
    }
  }
])

Key logical operators:

$and: Logical AND
$or: Logical OR
$not: Logical NOT
$nor: Logical NOR
$cond: Conditional expression
$ifNull: Return default if null
$switch: Multi-branch condition

Array Operators

Array operators are used to work with array fields, including querying array elements and manipulating array contents.

// Example: Query products with specific tags
db.products.find({
  tags: { $in: ["electronics", "sale"] }
})

// Example: Manipulating arrays in aggregation
db.users.aggregate([
  {
    $project: {
      firstSkill: { $arrayElemAt: ["$skills", 0] },
      skillCount: { $size: "$skills" }
    }
  }
])

Common array operators:

$arrayElemAt: Get element at array position
$concatArrays: Concatenate arrays
$filter: Filter array elements
$in: Check if value is in array
$indexOfArray: Find position of element in array
$isArray: Check if value is an array
$map: Map over array
$range: Generate numeric range array
$reduce: Reduce array
$reverseArray: Reverse array
$size: Get array length
$slice: Get array segment
$zip: Merge arrays

Conditional Expressions

Conditional expressions allow returning different values based on conditions, similar to if-else statements in programming languages.

// Example: Returning grades based on scores
db.students.aggregate([
  {
    $project: {
      grade: {
        $switch: {
          branches: [
            { case: { $gte: ["$score", 90] }, then: "A" },
            { case: { $gte: ["$score", 80] }, then: "B" },
            { case: { $gte: ["$score", 70] }, then: "C" },
            { case: { $gte: ["$score", 60] }, then: "D" }
          ],
          default: "F"
        }
      }
    }
  }
])

Key conditional expressions:

$cond: Ternary conditional
$ifNull: Handle null values
$switch: Multi-branch condition

Type Conversion Operators

Type conversion operators are used to convert between different data types.

// Example: Converting string to number
db.products.aggregate([
  {
    $project: {
      priceNumeric: {
        $toDouble: "$price"
      }
    }
  }
])

// Example: Checking field type
db.data.aggregate([
  {
    $project: {
      fieldType: {
        $type: "$value"
      }
    }
  }
])

Key type conversion operators:

$convert: General type conversion
$toBool: Convert to boolean
$toDate: Convert to date
$toDecimal: Convert to Decimal128
$toDouble: Convert to double
$toInt: Convert to integer
$toLong: Convert to long
$toObjectId: Convert to ObjectId
$toString: Convert to string
$type: Get BSON type of value

Variables and Expressions

MongoDB allows using variables and complex expressions in aggregation pipelines.

// Example: Using variables in calculation
db.sales.aggregate([
  {
    $project: {
      total: {
        $let: {
          vars: {
            subtotal: { $multiply: ["$price", "$quantity"] }
          },
          in: {
            $subtract: [
              "$$subtotal",
              { $multiply: ["$$subtotal", "$discount"] }
            ]
          }
        }
      }
    }
  }
])

Related operators:

$let: Define local variables
$literal: Return literal value without parsing as expression

Aggregation Accumulators

Aggregation accumulators are used to compute summary values in group operations.

// Example: Calculating sales statistics
db.sales.aggregate([
  {
    $group: {
      _id: "$productId",
      totalSales: { $sum: "$amount" },
      averagePrice: { $avg: "$price" },
      count: { $sum: 1 },
      maxQuantity: { $max: "$quantity" },
      minQuantity: { $min: "$quantity" }
    }
  }
])

Key aggregation accumulators:

$sum: Sum
$avg: Average
$first: Get first document's field value in group
$last: Get last document's field value in group
$max: Maximum value
$min: Minimum value
$push: Add value to array
$addToSet: Add unique value to set
$stdDevPop: Population standard deviation
$stdDevSamp: Sample standard deviation

Geospatial Operators

MongoDB provides specialized geospatial operators for working with geospatial data.

// Example: Querying nearby points
db.places.find({
  location: {
    $near: {
      $geometry: {
        type: "Point",
        coordinates: [longitude, latitude]
      },
      $maxDistance: 1000
    }
  }
})

Key geospatial operators:

$geoWithin: Query documents completely within specified geometry
$geoIntersects: Query documents intersecting specified geometry
$near: Query documents near a point
$nearSphere: Query documents near a point on a sphere
$geometry: Specify GeoJSON geometry
$center/$centerSphere: Specify circular region
$box/$polygon: Specify polygonal region

Bitwise Operators

Bitwise operators perform bit-level operations on integer values.

// Example: Checking permission bits
db.users.aggregate([
  {
    $project: {
      hasWritePermission: {
        $gt: [
          { $bitAnd: ["$permissions", 2] },
          0
        ]
      }
    }
  }
])

Key bitwise operators:

$bitAnd: Bitwise AND
$bitOr: Bitwise OR
$bitXor: Bitwise XOR
$bitNot: Bitwise NOT

Text Search Operators

MongoDB provides full-text search capabilities with text indexing and search.

// Example: Text search
db.articles.find({
  $text: {
    $search: "mongodb tutorial",
    $language: "en",
    $caseSensitive: false
  }
})

Related operators:

$text: Perform text search
$meta: Get text search relevance score
$search: Specify search string
$language: Specify language
$caseSensitive: Case sensitivity
$diacriticSensitive: Diacritic sensitivity

Other Special Operators

MongoDB also provides some special-purpose operators.

// Example: Using $rand to generate random numbers
db.users.aggregate([
  {
    $project: {
      randomScore: {
        $add: ["$score", { $multiply: [{ $rand: {} }, 10] }]
      }
    }
  }
])

Other special operators: