MongoDB Query Language (MQL) Operators
MongoDB Query Language (MQL) operators enable powerful filtering, comparison, and data manipulation within queries. Mastering these operators helps you write expressive queries to efficiently retrieve and update documents in your collections.
$avg
Computes the average of numeric values for documents in a group, bucket, or window.
$bottom
The $bottom operator returns the last document from the query's result set sorted by one or more fields
$bottomN
The $bottomN operator returns the last N documents from the result sorted by one or more fields
$count
The `$count` operator is used to count the number of documents that match a query filtering criteria.
$first
The $first operator returns the first value in a group according to the group's sorting order.
$firstN
The $firstN operator sorts documents on one or more fields specified by the query and returns the first N document matching the filtering criteria
$last
The $last operator returns the last document from the result sorted by one or more fields
$lastN
The $lastN accumulator operator returns the last N values in a group of documents.
$max
The $max operator returns the maximum value from a set of input values.
$maxN
Retrieves the top N values based on a specified filtering criteria
$median
The $median operator calculates the median value of a numeric field in a group of documents.
$min
Retrieves the minimum value for a specified field
$minN
Retrieves the bottom N values based on a specified filtering criteria
$percentile
The $percentile operator calculates the percentile of numerical values that match a filtering criteria
$stddevpop
The $stddevpop operator calculates the standard deviation of the specified values
$stddevsamp
The $stddevsamp operator calculates the standard deviation of a specified sample of values and not the entire population
$sum
The $sum operator calculates the sum of the values of a field based on a filtering criteria
$top
The $top operator returns the first document from the result set sorted by one or more fields
$topN
The $topN operator returns the first N documents from the result sorted by one or more fields
$addFields
The $addFields stage in the aggregation pipeline is used to add new fields to documents.
$bucket
Groups input documents into buckets based on specified boundaries.
$changeStream
The $changeStream stage opens a change stream cursor to track data changes in real-time.
$collStats
The $collStats stage in the aggregation pipeline is used to return statistics about a collection.
$convert
The $convert operator converts an expression into the specified type
$densify
Adds missing data points in a sequence of values within an array or collection.
$documents
The $documents stage creates a pipeline from a set of provided documents.
$facet
The $facet allows for multiple parallel aggregations to be executed within a single pipeline stage.
$fill
The $fill stage allows filling missing values in documents based on specified methods and criteria.
$geoNear
The $geoNear operator finds and sorts documents by their proximity to a geospatial point, returning distance information for each document.
$group
The $group stage groups documents by specified identifier expressions and applies accumulator expressions.
$indexStats
The $indexStats stage returns usage statistics for each index in the collection.
$isNumber
The $isNumber operator checks if a specified expression is a numerical type
$lookup
The $lookup stage in the Aggregation Framework is used to perform left outer joins with other collections.
$match
The $match stage in the aggregation pipeline is used to filter documents that match a specified condition.
$merge
The $merge stage in an aggregation pipeline writes the results of the aggregation to a specified collection.
$out
The `$out` stage in an aggregation pipeline writes the resulting documents to a specified collection.
$redact
Filters the content of the documents based on access rights.
$replaceWith
The $replaceWith operator in DocumentDB returns a document after replacing a document with the specified document
$sample
The $sample operator in DocumentDB returns a randomly selected number of documents
$set
The $set operator in DocumentDB updates or creates a new field with a specified value
$skip
The $skip stage in the aggregation pipeline is used to skip a specified number of documents from the input and pass the remaining documents to the next stage in the pipeline.
$sort
The $sort stage in the aggregation pipeline is used to order the documents in the pipeline by a specified field or fields.
$sortByCount
The $sortByCount stage in the aggregation pipeline is used to group documents by a specified expression and then sort the count of documents in each group in descending order.
$toBool
The $toBool operator converts an expression into a Boolean type
$toDate
The $toDate operator converts supported types to a proper Date object.
$toDecimal
The $toDecimal operator converts an expression into a Decimal type
$toDouble
The $toDouble operator converts an expression into a Double value
$toInt
The $toInt operator converts an expression into an Integer
$toLong
The $toLong operator converts an expression into a Long value
$toObjectId
The $toObjectId operator converts an expression into an ObjectId
$toString
The $toString operator converts an expression into a String
$unset
The $unset stage in the aggregation pipeline is used to remove specified fields from documents.
$unwind
The $unwind stage in the aggregation framework is used to deconstruct an array field from the input documents to output a document for each element.
$abs
The $abs operator returns the absolute value of a number.
$add
The $add operator returns the sum of two numbers or the sum of a date and numbers.
$ceil
The $ceil operator returns the smallest integer greater than or equal to the specified number.
$divide
The $divide operator divides two numbers and returns the quotient.
$exp
The $exp operator raises e to the specified exponent and returns the result
$floor
The $floor operator returns the largest integer less than or equal to the specified number
$ln
The $ln operator calculates the natural logarithm of the input
$log
The $log operator calculates the logarithm of a number in the specified base
$log10
The $log10 operator calculates the log of a specified number in base 10
$multiply
The $multiply operator multiplies the input numerical values
$pow
The `$pow` operator calculates the value of a numerical value raised to the power of a specified exponent.
$round
The $round operator rounds a number to a specified decimal place.
$sqrt
The $sqrt operator calculates and returns the square root of an input number
$subtract
The $subtract operator subtracts two numbers and returns the result.
$trunc
The $trunc operator truncates a number to a specified decimal place.
$arrayElemAt
The $arrayElemAt returns the element at the specified array index.
$arrayToObject
The $arrayToObject allows converting an array into a single document.
$concatArrays
The $concatArrays is used to combine multiple arrays into a single array.
$filter
The $filter operator filters for elements from an array based on a specified condition.
$indexOfArray
The $indexOfArray operator is used to search for an element in an array and return the index of the first occurrence of the element.
$isArray
The $isArray operator is used to determine if a specified value is an array.
$map
The $map operator allows applying an expression to each element in an array.
$range
The $range operator allows generating an array of sequential integers.
$reduce
The $reduce operator applies an expression to each element in an array & accumulate result as single value.
$reverseArray
The $reverseArray operator is used to reverse the order of elements in an array.
$slice
The $slice operator returns a subset of an array from any element onwards in the array.
$sortArray
The $sortArray operator helps in sorting the elements in an array.
$zip
The $zip operator allows merging two or more arrays element-wise into a single array or arrays.
$all
The $all operator helps finding array documents matching all the elements.
$elemMatch
The $elemmatch operator returns complete array, qualifying criteria with at least one matching array element.
$size
The $size operator is used to query documents where an array field has a specified number of elements.
$
The $ positional operator identifies an element in an array to update without explicitly specifying the position of the element in the array.
$addToSet
The addToSet operator adds elements to an array if they don't already exist, while ensuring uniqueness of elements within the set.
$each
The $each operator is used within an `$addToSet` or `$push` operation to add multiple elements to an array field in a single update operation.
$pop
Removes the first or last element of an array.
$[]
The $[] operator is used to update all elements in an array that match the query condition.
$[identifier]
The $[] operator is used to update all elements using a specific identifier in an array that match the query condition.
$position
The $position is used to specify the position in the array where a new element should be inserted.
$pull
Removes all instances of a value from an array.
$pullAll
The $pullAll operator is used to remove all instances of the specified values from an array.
$push
The $push operator adds a specified value to an array within a document.
$bitAnd
The $bitAnd operator performs a bitwise AND operation on integer values and returns the result as an integer.
$bitNot
The $bitNot operator performs a bitwise NOT operation on integer values and returns the result as an integer.
$bitOr
The $bitOr operator performs a bitwise OR operation on integer values and returns the result as an integer.
$bitXor
The $bitXor operator performs a bitwise XOR operation on integer values.
$bitsAllClear
The $bitsAllClear operator is used to match documents where all the bit positions specified in a bitmask are clear.
$bitsAllSet
The bitsAllSet command is used to match documents where all the specified bit positions are set.
$bitsAnyClear
The $bitsAnyClear operator matches documents where any of the specified bit positions in a bitmask are clear.
$bitsAnySet
The $bitsAnySet operator returns documents where any of the specified bit positions are set to 1.
$cmp
The $cmp operator compares two values
$eq
The $eq query operator compares the value of a field to a specified value
$gt
The $gt query operator retrieves documents where the value of a field is greater than a specified value
$gte
The $gte operator retrieves documents where the value of a field is greater than or equal to a specified value
$in
The $in operator matches value of a field against an array of specified values
$lt
The $lt operator retrieves documents where the value of field is less than a specified value
$lte
The $lte operator retrieves documents where the value of a field is less than or equal to a specified value
$ne
The $ne operator retrieves documents where the value of a field doesn't equal a specified value
$nin
The $nin operator retrieves documents where the value of a field doesn't match a list of values
$cond
The $cond operator is used to evaluate a condition and return one of two expressions based on the result.
$ifNull
The $ifNull operator is used to evaluate an expression and return a specified value if the expression resolves to null.
$switch
The $switch operator is used to evaluate a series of conditions and return a value based on the first condition that evaluates to true.
$dateAdd
The $dateAdd operator adds a specified number of time units (day, hour, month etc) to a date.
$dateDiff
The $dateDiff operator calculates the difference between two dates in various units such as years, months, days, etc.
$dateFromParts
The $dateFromParts operator constructs a date from individual components.
$dateFromString
The $dateDiff operator converts a date/time string to a date object.
$dateSubtract
The $dateSubtract operator subtracts a specified amount of time from a date.
$dateToParts
The $dateToParts operator decomposes a date into its individual parts such as year, month, day, and more.
$dateToString
The $dateToString operator converts a date object into a formatted string.
$dateTrunc
The $dateTrunc operator truncates a date to a specified unit.
$dayOfMonth
The $dayOfMonth operator extracts the day of the month from a date.
$dayOfWeek
The $dayOfWeek operator extracts the day of the week from a date.
$dayOfYear
The $dayOfYear operator extracts the day of the year from a date.
$hour
The $hour operator returns the hour portion of a date as a number between 0 and 23.
$isoDayOfWeek
The $isoDayOfWeek operator returns the weekday number in ISO 8601 format, ranging from 1 (Monday) to 7 (Sunday).
$isoWeek
The $isoWeek operator returns the week number of the year in ISO 8601 format, ranging from 1 to 53.
$isoWeekYear
The $isoWeekYear operator returns the year number in ISO 8601 format, which can differ from the calendar year for dates at the beginning or end of the year.
$millisecond
The $millisecond operator extracts the milliseconds portion from a date value.
$minute
The $minute operator extracts the minute portion from a date value.
$month
The $month operator extracts the month portion from a date value.
$second
The $second operator extracts the seconds portion from a date value.
$week
The $week operator returns the week number for a date as a value between 0 and 53.
$year
The $year operator returns the year for a date as a four-digit number.
$expr
The $expr operator allows the use of aggregation expressions within the query language, enabling complex field comparisons and calculations.
$jsonSchema
The $jsonSchema operator validates documents against a JSON Schema definition for data validation and structure enforcement. Discover supported features and limitations.
$mod
The $mod operator performs a modulo operation on the value of a field and selects documents with a specified result.
$regex
The $regex operator provides regular expression capabilities for pattern matching in queries, allowing flexible string matching and searching.
$text
The $text operator performs text search on the content of indexed string fields, enabling full-text search capabilities.
$currentDate
The $currentDate operator sets the value of a field to the current date, either as a Date or a timestamp.
$inc
The $inc operator increments the value of a field by a specified amount.
$mul
The $mul operator multiplies the value of a field by a specified number.
$rename
The $rename operator allows renaming fields in documents during update operations.
$setOnInsert
The $setOnInsert operator sets field values only when an upsert operation results in an insert of a new document.
$box
The $box operator defines a rectangular area for geospatial queries using coordinate pairs.
$center
The $center operator specifies a circle using legacy coordinate pairs for $geoWithin queries.
$centerSphere
The $centerSphere operator specifies a circle using spherical geometry for $geoWithin queries.
$geoIntersects
The $geoIntersects operator selects documents whose location field intersects with a specified GeoJSON object.
$geometry
The $geometry operator specifies a GeoJSON geometry for geospatial queries.
$geoWithin
The $geoWithin operator selects documents whose location field is completely within a specified geometry.
$maxDistance
The $maxDistance operator specifies the maximum distance that can exist between two points in a geospatial query.
$minDistance
The $minDistance operator specifies the minimum distance that must exist between two points in a geospatial query.
$near
The $near operator returns documents with location fields that are near a specified point, sorted by distance.
$nearSphere
The $nearSphere operator returns documents whose location fields are near a specified point on a sphere, sorted by distance on a spherical surface.
$polygon
The $polygon operator defines a polygon for geospatial queries, allowing you to find locations within an irregular shape.
$and
The $and operator joins multiple query clauses and returns documents that match all specified conditions.
$nor
The $nor operator performs a logical NOR on an array of expressions and retrieves documents that fail all the conditions.
$not
The $not operator performs a logical NOT operation on a specified expression, selecting documents that don't match the expression.
$or
The $or operator joins query clauses with a logical OR and returns documents that match at least one of the specified conditions.
$comment
The $comment operator adds a comment to a query to help identify the query in logs and profiler output.
$natural
The $natural operator forces the query to use the natural order of documents in a collection, providing control over document ordering and retrieval.
$rand
The $rand operator generates a random float value between 0 and 1.
$allElementsTrue
The $allElementsTrue operator returns true if all elements in an array evaluate to true.
$anyElementTrue
The $anyElementTrue operator returns true if any element in an array evaluates to a value of true.
$setDifference
The $setDifference operator returns a set with elements that exist in one set but not in a second set.
$setEquals
The $setEquals operator returns true if two sets have the same distinct elements.
$setIntersection
The $setIntersection operator returns the common elements that appear in all input arrays.
$setIsSubset
The $setIsSubset operator determines if one array is a subset of a second array.
$setUnion
The $setUnion operator returns an array that contains all the unique elements from the input arrays.
$covariancePop
The $covariancePop operator returns the covariance of two numerical expressions
$covarianceSamp
The $covarianceSamp operator returns the covariance of a sample of two numerical expressions
$denseRank
The $denseRank operator assigns and returns a positional ranking for each document within a partition based on a specified sort order
$derivative
The $derivative operator calculates the average rate of change of the value of a field within a specified window.
$documentNumber
The $documentNumber operator assigns and returns a position for each document within a partition based on a specified sort order
$expMovingAvg
The $expMovingAvg operator calculates the moving average of a field based on the specified number of documents to hold the highest weight
$integral
The $integral operator calculates the area under a curve with the specified range of documents forming the adjacent documents for the calculation.
$linearFill
The $linearFill operator interpolates missing values in a sequence of documents using linear interpolation.
$locf
The $locf operator propagates the last observed non-null value forward within a partition in a windowed query.
$rank
The $rank operator ranks documents within a partition based on a specified sort order.
$shift usage on DocumentDB
A window operator that shifts values within a partition and returns the shifted value.