Aggregation Expression Operations

Arithmetic Operations

You can perform an arithmetic operation on a value of type MqlInteger or MqlNumber using the methods described in this section.

Suppose you have weather data for a specific year that includes the precipitation measurement (in inches) for each day. You want to find the average precipitation, in millimeters, for each month.

The multiply() operator multiplies the precipitation field by 25.4 to convert the field value to millimeters. The avg() accumulator method returns the average as the avgPrecipMM field. The group() method groups the values by month given in each document's date field.

The following code shows the pipeline for this aggregation:

val month = current().getDate("date").month(of("UTC"))
val precip = current().getInteger("precipitation")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.group(
            month,
            Accumulators.avg("avgPrecipMM", precip.multiply(25.4))
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $group: {
    _id: { $month: "$date" },
    avgPrecipMM: {
        $avg: { $multiply: ["$precipitation", 25.4] } }
} } ]

Array Operations

You can perform an array operation on a value of type MqlArray using the methods described in this section.

Suppose you have a collection of movies, each of which contains an array of nested documents for upcoming showtimes. Each nested document contains an array that represents the total number of seats in the theater, where the first array entry is the number of premium seats and the second entry is the number of regular seats. Each nested document also contains the number of tickets that have already been bought for the showtime. A document in this collection might resemble the following:

{
  "_id": ...,
  "movie": "Hamlet",
  "showtimes": [
    {
      "date": "May 14, 2023, 12:00 PM",
      "seats": [ 20, 80 ],
      "ticketsBought": 100
    },
    {
      "date": "May 20, 2023, 08:00 PM",
      "seats": [ 10, 40 ],
      "ticketsBought": 34
    }]
}

The filter() method displays only the results matching the provided predicate. In this case, the predicate uses sum() to calculate the total number of seats and compares that value to the number of ticketsBought by using the lt() method. The project() method stores these filtered results as a new availableShowtimes array field.

The following code shows the pipeline for this aggregation:

val showtimes = current().getArray<MqlDocument>("showtimes")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.project(
            Projections.fields(
                Projections.computed("availableShowtimes", showtimes
                    .filter { showtime ->
                        val seats = showtime.getArray<MqlInteger>("seats")
                        val totalSeats = seats.sum { n -> n }
                        val ticketsBought = showtime.getInteger("ticketsBought")
                        val isAvailable = ticketsBought.lt(totalSeats)
                        isAvailable
                    })
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $project: {
    availableShowtimes: {
        $filter: {
            input: "$showtimes",
            as: "showtime",
            cond: { $lt: [ "$$showtime.ticketsBought", { $sum: "$$showtime.seats" } ] }
    } }
} } ]

Boolean Operations

You can perform a boolean operation on a value of type MqlBoolean using the methods described in this section.

Suppose you want to classify very low or high weather temperature readings (in degrees Fahrenheit) as extreme.

The or() operator checks to see if temperatures are extreme by comparing the temperature field to predefined values by using the lt() and gt() methods. The project() method records this result in the extremeTemp field.

The following code shows the pipeline for this aggregation:

val temperature = current().getInteger("temperature")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.project(
            Projections.fields(
                Projections.computed(
                    "extremeTemp", temperature
                        .lt(of(10))
                        .or(temperature.gt(of(95)))
                )
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $project: {
    extremeTemp: { $or: [ { $lt: ["$temperature", 10] },
                          { $gt: ["$temperature", 95] } ] }
} } ]

Comparison Operations

You can perform a comparison operation on a value of type MqlValue using the methods described in this section.

The following example shows a pipeline that matches all the documents where the location field has the value "California":

val location = current().getString("location")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.match(
            Filters.expr(location.eq(of("California")))
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $match: { location: { $eq: "California" } } } ]

Conditional Operations

You can perform a conditional operation using the methods described in this section.

Suppose you have a collection of customers with their membership information. Originally, customers were either members or not. Over time, membership levels were introduced and used the same field. The information stored in this field can be one of a few different types, and you want to create a standardized value indicating their membership level.

The switchOn() method checks each clause in order. If the value matches the type indicated by the clause, then the clause determines the string value corresponding to the membership level. If the original value is a string, it represents the membership level and that value is used. If the data type is a boolean, it returns either Gold or Guest for the membership level. If the data type is an array, it returns the most recent string in the array which matches the most recent membership level. If the member field is an unknown type, the switchOn() method provides a default value of Guest.

The following code shows the pipeline for this aggregation:

val member = current().getField("member")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.project(
            Projections.fields(
                Projections.computed("membershipLevel",
                    member.switchOn { field ->
                        field
                            .isString { s -> s }
                            .isBoolean { b -> b.cond(of("Gold"), of("Guest")) }
                            .isArray { a -> a.last() }
                            .defaults { d -> of("Guest") }
                    })
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $project: {
    membershipLevel: {
        $switch: {
          branches: [
            { case: { $eq: [ { $type: "$member" }, "string" ] }, then: "$member" },
            { case: { $eq: [ { $type: "$member" }, "bool" ] }, then: { $cond: {
                                                                           if: "$member",
                                                                           then: "Gold",
                                                                           else: "Guest" } } },
            { case: { $eq: [ { $type: "$member" }, "array" ] }, then: { $last: "$member" } }
          ],
          default: "Guest" } }
} } ]

Convenience Operations

You can apply custom functions to values of type MqlValue using the methods described in this section.

To improve readability and allow for code reuse, you can move redundant code into static methods. However, you cannot directly chain static methods in Kotlin. The passTo() method lets you chain values into custom static methods.

Suppose you want to determine how a class is performing against some benchmarks. You want to find the average final grade for each class and compare it against the benchmark values.

The following custom method gradeAverage() takes an array of documents and the name of an integer field shared across those documents. It calculates the average of that field across all the documents in the provided array and determines the average of that field across all the elements in the provided array. The evaluate() method compares a provided value to two provided range limits and generates a response string based on how the values compare:

fun gradeAverage(students: MqlArray<MqlDocument>, fieldName: String): MqlNumber {
    val sum = students.sum { student -> student.getInteger(fieldName) }
    val avg = sum.divide(students.size())
    return avg
}
fun evaluate(grade: MqlNumber, cutoff1: MqlNumber, cutoff2: MqlNumber): MqlString {
    val message = grade.switchOn { on ->
        on
            .lte(cutoff1) { g -> of("Needs improvement") }
            .lte(cutoff2) { g -> of("Meets expectations") }
            .defaults { g -> of("Exceeds expectations") }
    }
    return message
}

The passArrayTo() method takes an array of all students and calculates the average score by using the gradeAverage() method. Then, the passNumberTo() method uses the evaluate() method to determine how the classes are performing. This example stores the result as the evaluation field using the project() method.

The following code shows the pipeline for this aggregation:

val students = current().getArray<MqlDocument>("students")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.project(
            Projections.fields(
                Projections.computed("evaluation", students
                    .passArrayTo { s -> gradeAverage(s, "finalGrade") }
                    .passNumberTo { grade -> evaluate(grade, of(70), of(85)) })
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $project: {
    evaluation: { $switch: {
        branches: [
            { case: { $lte: [ { $avg: "$students.finalGrade" }, 70 ] },
              then: "Needs improvement"
            },
            { case: { $lte: [ { $avg: "$students.finalGrade" }, 85 ] },
              then: "Meets expectations"
            }
        ],
        default: "Exceeds expectations" } }
} } ]

Conversion Operations

You can perform a conversion operation to convert between certain MqlValue types using the methods described in this section.

Suppose you want to have a collection of student data that includes their graduation years, which are stored as strings. You want to calculate the year of their five-year reunion and store this value in a new field.

The parseInteger() method converts the graduationYear to an integer so that add() can calculate the reunion year. The addFields() method stores this result as a new reunionYear field.

The following code shows the pipeline for this aggregation:

val students = current().getArray<MqlDocument>("students")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.project(
            Projections.fields(
                Projections.computed("evaluation", students
                    .passArrayTo { s -> gradeAverage(s, "finalGrade") }
                    .passNumberTo { grade -> evaluate(grade, of(70), of(85)) })
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $addFields: {
    reunionYear: {
        $add: [ { $toInt: "$graduationYear" }, 5 ] }
} } ]

Date Operations

You can perform a date operation on a value of type MqlDate using the methods described in this section.

Suppose you have data about package deliveries and want to match deliveries that occurred on any Monday in the "America/New_York" time zone.

If the deliveryDate field contains any string values representing valid dates, such as "2018-01-15T16:00:00Z" or "Jan 15, 2018, 12:00 PM EST", you can use the parseDate() method to convert the strings into date types.

The dayOfWeek() method determines which day of the week that a date is, then converts it to a number. The number assignment uses 0 to mean Sunday when using the "America/New_York" timezone. The eq() method compares this value to 2, or Monday.

The following code shows the pipeline for this aggregation:

val deliveryDate = current().getString("deliveryDate")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.match(
            Filters.expr(
                deliveryDate
                    .parseDate()
                    .dayOfWeek(of("America/New_York"))
                    .eq(of(2))
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $match: {
    $expr: {
        $eq: [ {
            $dayOfWeek: {
                date: { $dateFromString: { dateString: "$deliveryDate" } },
                timezone: "America/New_York" }},
            2
        ] }
} } ]

Document Operations

You can perform a document operation on a value of type MqlDocument using the methods described in this section.

Suppose you have a collection of legacy customer data which includes addresses as child documents under the mailing.address field. You want to find all the customers who live in Washington state. A document in this collection might resemble the following:

{
  "_id": ...,
  "customer.name": "Mary Kenneth Keller",
  "mailing.address":
  {
    "street": "601 Mongo Drive",
    "city": "Vasqueztown",
    "state": "CO",
    "zip": 27017
  }
}

The getDocument() method retrieves the mailing.address field as a document so the nested state field can be retrieved with the getString() method. The eq() method checks if the value of the state field is "WA".

The following code shows the pipeline for this aggregation:

val address = current().getDocument("mailing.address")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.match(
            Filters.expr(
                address
                    .getString("state")
                    .eq(of("WA"))
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[
    { $match: {
        $expr: {
            $eq: [{
                $getField: {
                    input: { $getField: { input: "$$CURRENT", field: "mailing.address"}},
                    field: "state" }},
                "WA" ]
}}}]

Map Operations

You can perform a map operation on a value of either type MqlMap or MqlEntry using the methods described in this section.

Suppose you have a collection of inventory data where each document represents an individual item you're responsible for supplying. Each document contains a field that is a map of all your warehouses and how many copies they have in their inventory of the item. You want to determine the total number of copies of items you have across all warehouses. A document in this collection might resemble the following:

{
  "_id": ...,
  "item": "notebook"
  "warehouses": [
    { "Atlanta", 50 },
    { "Chicago", 0 },
    { "Portland", 120 },
    { "Dallas", 6 }
  ]
}

The entries() method returns the map entries in the warehouses field as an array. The sum() method calculates the total value of items based on the values in the array retrieved with the getValue() method. This example stores the result as the new totalInventory field using the project() method.

The following code shows the pipeline for this aggregation:

val warehouses = current().getMap<MqlNumber>("warehouses")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.project(
            Projections.fields(
                Projections.computed("totalInventory", warehouses
                    .entries()
                    .sum { v -> v.getValue() })
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $project: {
    totalInventory: {
        $sum: {
          $getField: { $objectToArray: "$warehouses" },
        } }
} } ]

String Operations

You can perform a string operation on a value of type MqlString using the methods described in this section.

Suppose you want to generate lowercase usernames for employees of a company from the employees' last names and employee IDs.

The append() method combines the lastName and employeeID fields into a single username, while the toLower() method makes the entire username lowercase. This example stores the result as a new username field using the project() method.

The following code shows the pipeline for this aggregation:

val lastName = current().getString("lastName")
val employeeID = current().getString("employeeID")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.project(
            Projections.fields(
                Projections.computed(
                    "username", lastName
                        .append(employeeID)
                        .toLower()
                )
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $project: {
    username: {
        $toLower: { $concat: ["$lastName", "$employeeID"] } }
} } ]

Type-Checking Operations

You can perform a type-check operation on a value of type MqlValue using the methods described in this section.

These methods do not return boolean values. Instead, you provide a default value that matches the type specified by the method. If the checked value matches the method type, the checked value is returned. Otherwise, the supplied default value is returned. To program branching logic based on the data type, see switchOn().

Suppose you have a collection of rating data. An early version of the review schema allowed users to submit negative reviews without a star rating. You want convert any of these negative reviews without a star rating to have the minimum value of 1 star.

The isNumberOr() method returns either the value of rating, or a value of 1 if rating is not a number or is null. The project() method returns this value as a new numericalRating field.

The following code shows the pipeline for this aggregation:

val rating = current().getField("rating")
val results = collection.aggregate<Document>(
    listOf(
        Aggregates.project(
            Projections.fields(
                Projections.computed(
                    "numericalRating", rating
                        .isNumberOr(of(1))
                )
            )
        )
    )
)

The following code provides an equivalent aggregation pipeline in the Query API:

[ { $project: {
    numericalRating: {
        $cond: { if: { $isNumber: "$rating" },
                 then: "$rating",
                 else: 1
        } }
} } ]