English
English
# JQL common operation method
uniCloud's cloud database provides a batch of powerful computing methods. These methods are executed by the database, not by the cloud function.
These calculation methods are used in conjunction with data query, and they can perform calculations on field values or part of field values, and return the calculated results to the query request.
The database operation method provides more powerful and flexible queries than traditional SQL. More functions can be realized, and the expected results can be queried at one time. It is not necessary to check the database multiple times for multiple calculations, which will not only complicate the code, but also cause performance degradation for multiple database queries; if you use billing cloud space, using these methods can also reduce the number of database queries.
For example, the sum() method can sum the values of a certain field in multiple rows of records, and can sum the values of several fields in a single row of records. If the field is an array, it can also sum the items of the array.
For the convenience of writing, the usage of the database operation method is simplified in JQL (compared to the original database operation method writing method), mainly the parameters are flattened, and the method is a string Express. The following are the database operation methods that can be used in JQL
# Summary of database operation methods
# Complete list of operation methods
| Computation method | Application | Simplified usage of JQL | Description |
|---|---|---|---|
| abs | Returns the absolute value of a number | abs(expression) | - |
| add | Add numbers or add numbers to dates. If one of the values in the argument is a date, the other values will be treated as milliseconds added to that date | add(expression1,expression2) | - |
| ceil | round up | ceil(expression) | - |
| divide | Input dividend and divisor, find quotient | divide(expression1,expression2) | - |
| exp | Take e (the base of natural logarithm, Euler's number) to the power n | exp(expression) | - |
| floor | round down | floor(expression) | - |
| ln | Calculates the natural logarithm value of the given number | ln(expression) | - |
| log | Calculates the log value of a given number under a given logarithm | log(expression1,expression2) | - |
| log10 | Calculates the log value of a given number with logarithm base 10 | log10(expression) | - |
| mod | Modulo operation, the first number is the dividend, the second number is the divisor | mod(expression1,expression2) | - |
| multiply | The result of multiplying the incoming number parameters | multiply(expression1,expression2) | - |
| pow | Find the power of the exponent in the given base | pow(expression1,expression2) | - |
| sqrt | square root | sqrt(expression1,expression2) | - |
| subtract | Subtracts two numbers and returns the difference, or subtracts two dates and returns the difference in milliseconds, or subtracts a date and returns the date of the result. | subtract(expression1,expression2) | - |
| trunc | Truncates a number to an integer | trunc(expression) | - |
| arrayElemAt | returns the element at the specified array index | arrayElemAt(expression1,expression2) | - |
| arrayToObject | converts an array to an object | arrayToObject(expression) | - |
| concatArrays | Concatenate multiple arrays into one array | concatArrays(expression1,expression2) | - |
| filter | Returns a subset of the array that satisfies the condition based on the given condition | filter(input,as,cond) | - |
| in | Given a value and an array, return true if the value is in the array, otherwise return false | in(expression1,expression2) | - |
| indexOfArray | Find the subscript of the first element in the array equal to the given value, if not found, return -1 | indexOfArray(expression1,expression2) | - |
| isArray | Determine whether the given expression is an array, return a Boolean value | isArray(expression) | - |
| map | Similar to the map method on JavaScript Array, convert each element of the given array according to the given conversion method to get a new array | map(input,as,in) | - |
| objectToArray | Converts an object to an array. The method turns each key-value pair of the object into an element of the output array, and the element is in the form of { k: <key>, v: <value> } | objectToArray(expression) | - |
| range | Returns a range of generated sequence numbers. Given a start value, an end value, and a non-zero step size, range returns a sequence growing from the start value in steps of the given step size, excluding the end value. | range(expression1,expression2) | - |
| reduce | Similar to JavaScript's reduce method, apply an expression to each element of the array and then normalize it into one element | reduce(input,initialValue,in) | - |
| reverseArray | Returns the reversed form of the given array | reverseArray(expression) | - |
| size | returns the length of the array | size(expression) | - |
| slice | Similar to JavaScript's slice method. returns the specified subset of the given array | slice(expression1,expression2) | - |
| zip | Assemble the elements with the same serial number in the second dimension of the two-dimensional array into a new array and then assemble a new two-dimensional array. | zip(inputs, useLongestLength, defaults) | - |
| and | Given multiple expressions, and returns true only if all expressions return true, otherwise returns false | and(expression1,expression2) | - |
| not | Given an expression, not returns false if the expression returns true, and true otherwise. Note that the expression cannot be a logical expression (and, or, nor, not) | not(expression) | - |
| or | Given multiple expressions, if any expression returns true, then or returns true, otherwise returns false | or(expression1,expression2) | - |
| cmp | Given two values, return their comparison value. If the first value is less than the second value, return -1 If the first value is greater than the second value, return 1 If the two values are equal, return 0 | cmp(expression1,expression2) | - |
| eq | Matches two values, returns true if equal, false otherwise | eq(expression1,expression2) | - |
| gt | matches two values, returns true if former is greater than latter, false otherwise | gt(expression1,expression2) | - |
| gte | matches two values, returns true if former is greater than or equal to latter, false otherwise | gte(expression1,expression2) | - |
| lt | Matches two values, returns true if former is less than latter, false otherwise | lt(expression1,expression2) | - |
| lte | matches two values, returns true if former is less than or equal to latter, false otherwise | lte(expression1,expression2) | - |
| neq | matches two values, returns true if not equal, false otherwise | neq(expression1,expression2) | - |
| cond | Calculate Boolean expression 1, return expression 2 if true, otherwise return expression 3 | cond(expression 1, expression 2, expression 3) | - |
| ifNull | Evaluates the given expression and returns a replacement value if the expression evaluates to null, undefined or does not exist; otherwise returns the original value. | ifNull(expression1,expression2) | - |
| switch | Calculate the return value according to the given switch-case-default | switch(branches,default) | - |
| dateFromParts | Given information about a date, construct and return a date object | dateFromParts(year,month,day,hour,minute,second,millisecond,timezone) | - |
| isoDateFromParts | Given information about a date, construct and return a date object | isoDateFromParts(isoWeekYear,isoWeek,isoDayOfWeek,hour,minute,second,millisecond,timezone) | - |
| dateFromString | Convert a date/time string to a date object | dateFromString(dateString, format, timezone, onError, onNull) | - |
| dateToString | Format the date object into a string that meets the requirements according to the specified expression | dateToString(date,format,timezone,onNull) | - |
| dayOfMonth | Returns the day (the day of the month) corresponding to the date field, which is a number between 1 and 31 | dayOfMonth(date,timezone) | - |
| dayOfWeek | Returns the day number (the day of the week) corresponding to the date field, which is an integer between 1 (Sunday) and 7 (Saturday) | dayOfWeek(date,timezone) | - |
| dayOfYear | Returns the day number (the day of the year) corresponding to the date field, which is an integer between 1 and 366 | dayOfYear(date,timezone) | - |
| hour | Returns the hour corresponding to the date field, which is an integer between 0 and 23. | hour(date,timezone) | - |
| isoDayOfWeek | Returns the ISO 8601 day number (day of the week) corresponding to the date field, as an integer between 1 (Monday) and 7 (Sunday). | isoDayOfWeek(date, timezone) | - |
| isoWeek | Returns the ISO 8601 week number (week of the year) corresponding to the date field, which is an integer between 1 and 53. | isoWeek(date, timezone) | - |
| isoWeekYear | Returns the ISO 8601 day number (day of the year) corresponding to the date field | isoWeekYear(date,timezone) | - |
| millisecond | returns the milliseconds corresponding to the date field, which is an integer between 0 and 999 | millisecond(date,timezone) | - |
| minute | Returns the minute corresponding to the date field, which is an integer between 0 and 59 | minute(date,timezone) | - |
| month | Returns the month corresponding to the date field, which is an integer between 1 and 12 | month(date,timezone) | - |
| second | Returns the second corresponding to the date field, an integer between 0 and 59, which may be equal to 60 in special cases (leap seconds) | second(date,timezone) | - |
| week | Returns the week number (week of the year) corresponding to the date field, which is an integer between 0 and 53 | week(date,timezone) | - |
| year | returns the year corresponding to the date field | year(date,timezone) | - |
| timestampToDate | Pass in a timestamp and return the corresponding date object | timestampToDate(timestamp) | Only supported in JQL strings, supported since HBuilderX 3.1.0 |
| literal | directly returns a literal value without any parsing and processing | literal(expression) | - |
| mergeObjects | Merge multiple objects into a single object | mergeObjects(expression1,expression2) | - |
| allElementsTrue | Enter an array, or an expression for an array field. Returns true if all elements in the array are true, otherwise returns false. An empty array always returns true | allElementsTrue(expression1,expression2) | - |
| anyElementTrue | Enter an array, or an expression for an array field. Returns true if any element in the array is true, otherwise returns false. An empty array always returns false | anyElementTrue(expression1,expression2) | - |
| setDifference | Input two collections, output elements that only exist in the first collection | setDifference(expression1,expression2) | - |
| setEquals | Input two collections, judge whether the elements contained in the two collections are the same (regardless of order, deduplication) | setEquals(expression 1, expression 2) | - |
| setIntersection | Input two sets, output the intersection of the two sets | setIntersection(expression1,expression2) | - |
| setIsSubset | Input two sets, judge whether the first set is a subset of the second set | setIsSubset(expression1,expression2) | - |
| setUnion | Input two sets, output the union of two sets | setUnion(expression1,expression2) | - |
| concat | Concatenate strings and return the concatenated string | concat(expression1,expression2) | - |
| indexOfBytes | Finds a substring in the target string and returns the UTF-8 byte index (starting from 0) of the first occurrence. If there is no substring, return -1 | indexOfBytes(expression1,expression2) | - |
| indexOfCP | Finds a substring in the target string and returns the UTF-8 code point index (starting from 0) of the first occurrence. If there is no substring, return -1 | indexOfCP(expression1,expression2) | - |
| split | Splits an array by a delimiter and removes the delimiter, returning an array of substrings. If the string cannot be separated by a delimiter, return the original string as the only element of the array | split(expression1,expression2) | - |
| strLenBytes | Calculate and return the number of utf-8 encoded bytes in the specified string | strLenBytes(expression) | - |
| strLenCP | Calculate and return the number of UTF-8 code points of the specified string | strLenCP(expression) | - |
| strcasecmp | Compare two character strings without case sensitivity, and return the comparison result | strcasecmp(expression1,expression2) | - |
| substr | Returns a substring of the specified length starting at the specified position | substr(expression1,expression2) | - |
| substrBytes | Returns a substring of the specified length of the string starting at the specified position. The substring starts with the character at the specified UTF-8 byte index in the string and has a length of the specified number of bytes | substrBytes(expression1,expression2) | - |
| substrCP | Returns a substring of a specified length of a string starting at a specified position. The substring starts with the character at the specified UTF-8 byte index in the string and has a length of the specified number of bytes | substrCP(expression1,expression2) | - |
| toLower | Convert the string to lowercase and return | toLower(expression) | - |
| toUpper | convert string to uppercase and return | toUpper(expression) | - |
| addToSet | Aggregation operator. Adds a value to the array, or does nothing if the value already exists in the array. It can only be used in group stage | addToSet(expression) | - |
| avg | returns the average value of the data corresponding to the specified expression | avg(expression) | - |
| first | Returns the value corresponding to the first record of the specified field in a set. This operation only makes sense if the set of collections is sorted by some definition ( sort ) | first(expression) | - |
| last | Returns the value corresponding to the last record of the specified field in a set. This operation only makes sense if the set of collections is sorted ( sort ) by some definition. | last(expression) | - |
| max | Returns the maximum value of a set of values | max(expression) | - |
| min | Returns the minimum value of a set of values | min(expression) | - |
| push | Returns an array of columns specified by expressions and corresponding values in a group | push(expression) | - |
| stdDevPop | Returns the standard deviation of values corresponding to a set of fields | stdDevPop(expression) | - |
| stdDevSamp | Computes the sample standard deviation of input values | stdDevSamp(expression) | - |
| sum | Returns the sum of all values of a group of fields in groupField, and returns the sum of all elements of an array in non-groupField | sum(expression) | - |
| let | Customize variables and use them in the specified expression, the returned result is the result of the expression | let(vars,in) | - |
The above operators can also be used in combination
Example: The data table article has the following data
{
"_id": "1",
"publish_date": 1611141512751,
"content": "hello uniCloud content 01",
"content": "hello uniCloud title 01",
}
{
"_id": "2",
"publish_date": 1611141512752,
"content": "hello uniCloud content 02",
"content": "hello uniCloud title 02",
}
{
"_id": "3",
"publish_date": 1611141512753,
"content": "hello uniCloud content 03",
"content": "hello uniCloud title 03",
}
You can use the following query to convert the publish_date field from timestamp to 2021-01-20, and then perform statistics by day
const res = await db.collection('article')
.groupBy('dateToString(add(new Date(0),publish_date),"%Y-%m-%d","+0800") as publish_date_str')
.groupField('count(*) as total')
.get()
The above code uses the add method to convert the publish_date timestamp into a date type, and then uses dateToString to format the date in the previous step in the format of 4-digit year-2-digit month-2-digit day according to the time zone '+0800' (Beijing time) , please refer to dateToString for complete format parameters.
The above code execution result is
res = {
result: {
data: [{
publish_date_str: '2021-01-20',
total: 3
}]
}
}
Notice
In the calculation method, only database fields can be directly written as variables without quotation marks, and other strings must still be written as strings
example:
The database contains the following data:
{
"_id": 1,
"sales": [ 1.32, 6.93, 2.48, 2.82, 5.74 ]
}
{
"_id": 2,
"sales": [ 2.97, 7.13, 1.58, 6.37, 3.69 ]
}
Round the sales field in the following data in the cloud function
const db = uniCloud.database()
const $ = db.command.aggregate
let res = await db.collection('stats').aggregate()
.project({
truncated: $.map({
input: '$sales',
as: 'num',
in: $.trunc('$$num'),
})
})
.end()
Realization of the same function in JQL syntax
const db = uniCloud.database()
const res = await db.collection('stats')
.field('map(sales,"num",trunc("$$num")) as truncated')
.get()
# Grouping operation method
The grouping operation method is a database operation method dedicated to statistical summarization. It's also a database method, not a js method.
Equivalent to mongoDB accumulator operator concept
Only the following calculation methods can be used in groupField.
| Operator | Usage | Usage | Description |
|---|---|---|---|
| addToSet | Adds a value to the array, or does nothing if the value already exists in the array | addToSet(expression) | - |
| avg | returns the average value of the data corresponding to the specified expression | avg(expression) | - |
| first | Returns the value corresponding to the first record of the specified field in a set. This operation only makes sense if the collection is sorted by some definition ( sort ) | first(expression) | - |
| last | Returns the value corresponding to the last record of the specified field in a set. This operation only makes sense if the set of collections is sorted ( sort ) by some definition. | last(expression) | - |
| max | Returns the maximum value of a set of values | max(expression) | - |
| min | Returns the minimum value of a set of values | min(expression) | - |
| push | Returns an array of columns specified by expressions and corresponding values in a group | push(expression) | - |
| stdDevPop | Returns the standard deviation of values corresponding to a set of fields | stdDevPop(expression) | - |
| stdDevSamp | Computes the sample standard deviation of the input values | stdDevSamp(expression) | - |
| sum | Returns the sum of all values in a set of fields | sum(expression) | - |
| mergeObjects | Merges a group of objects into a single object | mergeObjects(expression) | Receives only one argument when used inside a groupField |
# Examples of common operation methods
The following lists the application of common operation methods in JQL
# Arithmetic methods
Arithmetic expressions perform mathematical operations on numbers. Some arithmetic expressions can also support date arithmetic.
# abs
Returns the absolute value of a number.
Example collection test data example:
{ _id: 1, start: 5 }
{ _id: 2, start: -4 }
{ _id: 3, start: -9 }
{ _id: 4, start: 6 }
Compute the absolute value of start:
db.collection('test').field('abs(satrt) as absStart').get()
Results of the:
{ _id: 1, absStart: 5 }
{ _id: 2, absStart: 4 }
{ _id: 3, absStart: 9 }
{ _id: 4, absStart: 6 }
# add
Add numbers or add numbers to dates. If one of the values in the parameter is a date, the other values are treated as milliseconds added to that date.
Example collection test data example:
{ _id: 1, price: 5, fee: 1, date: 1667804052630 }
{ _id: 2, price: 4, fee: 5, date: 1667804052630 }
{ _id: 3, price: 9, fee: 2, date: 1667804052630 }
{ _id: 4, price: 6, fee: 8, date: 1667804052630 }
Example:
add numbers
db.collection('test').field('add(price, fee) as total').get()
Results of the:
{ _id: 1, total: 6 }
{ _id: 2, total: 9 }
{ _id: 3, total: 11 }
{ _id: 4, total: 14 }
Add 1 day to the current date
db.collection('test').field('add(date, 24 * 60 * 60 * 1000) as newDate').get()
Results of the:
{ _id: 1, newDate: 1667890452630 }
{ _id: 2, newDate: 1667890452630 }
{ _id: 3, newDate: 1667890452630 }
{ _id: 4, newDate: 1667890452630 }
# ceil
Rounded up.
The collection test data is as follows:
{ _id: 1, value: 9.25 }
{ _id: 2, value: 8.73 }
{ _id: 3, value: 4.32 }
{ _id: 4, value: -5.34 }
Round up the value field:
db.collection('test').field('ceil(value) as ceilingVaule').get()
Results of the:
{ _id: 1, value: 10 }
{ _id: 2, value: 10 }
{ _id: 3, value: 5 }
{ _id: 4, value: -5 }
# divide
Pass in the dividend and divisor to find the quotient.
The example collection test data is as follows:
{ "_id" : 1, "name" : "A", "hours" : 80, "resources" : 7 }
{ "_id" : 2, "name" : "B", "hours" : 40, "resources" : 4 }
Divide the hours field by 8 to calculate the number of working days:
db.collection('test').field('divide(hours, 8) as workdays').get()
Results of the:
{ "_id" : 1, "workdays" : 10 }
{ "_id" : 2, "workdays" : 8 }
# floor
Round down.
The example collection test data is as follows:
{ _id: 1, value: 9.25 }
{ _id: 2, value: 8.73 }
{ _id: 3, value: 4.32 }
{ _id: 4, value: -5.34 }
Round value down:
db.collection('test').field('floor(value) as floorValue').get()
Results of the:
{ _id: 1, floorValue: 9 }
{ _id: 2, floorValue: 8 }
{ _id: 3, floorValue: 4 }
{ _id: 4, floorValue: -6 }
# ln
Calculates the natural logarithm value of the given number.
The example collection test data is as follows:
{ _id: 1, year: "2000", sales: 8700000 }
{ _id: 2, year: "2005", sales: 5000000 }
{ _id: 3, year: "2010", sales: 6250000 }
An example of converting test data is as follows:
db.collection('test').field('year as x, ln(sales) as y').get()
Results of the:
{ "_id" : 1, "x" : "2000", "y" : 15.978833583624812 }
{ "_id" : 2, "x" : "2005", "y" : 15.424948470398375 }
{ "_id" : 3, "x" : "2010", "y" : 15.648092021712584 }
# log
Computes the log value of a given number at a given logarithm.
The example collection test data is as follows:
{ _id: 1, positiveInt: 5 }
{ _id: 2, positiveInt: 2 }
{ _id: 3, positiveInt: 23 }
{ _id: 4, positiveInt: 10 }
Use $log_2$ in the calculation to determine the number of digits required to represent the value of positiveInt:
db.collection('test').field('floor(add(log(positiveInt, 2))) as bitsNeeded')
Results of the:
{ "_id" : 1, "bitsNeeded" : 3 }
{ "_id" : 2, "bitsNeeded" : 2 }
{ "_id" : 3, "bitsNeeded" : 5 }
{ "_id" : 4, "bitsNeeded" : 4 }
# log10
Computes the log value of the given number at log base 10.
The example collection test data is as follows:
{ _id: 1, H3O: 0.0025 }
{ _id: 2, H3O: 0.001 }
{ _id: 3, H3O: 0.02 }
The following example calculates the pH of a sample:
db.collection('test').field('multiply(1, log10(H3O)) as pH').get()
Results of the:
{ "_id" : 1, "pH" : 2.6020599913279625 }
{ "_id" : 2, "pH" : 3 }
{ "_id" : 3, "pH" : 1.6989700043360187 }
# mod
Modulo operation, the first number is the dividend and the second number is the divisor.
The example collection test data is as follows:
{ "_id" : 1, "project" : "A", "hours" : 80, "tasks" : 7 }
{ "_id" : 2, "project" : "B", "hours" : 40, "tasks" : 4 }
Use the mod expression to return the remainder of the hours field divided by the tasks field:
db.collection('test').field('mod(hours, tasks) as remainder').get()
Results of the:
{ "_id" : 1, "remainder" : 3 }
{ "_id" : 2, "remainder" : 0 }
# multiply
Gets the result of multiplying the passed in numeric arguments.
The example collection test data is as follows:
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity": 2 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity": 1 }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity": 10 }
Use the multiply expression to multiply the price and quantity fields:
db.collection('test').field('multiply(price, quantity) as total').get()
Results of the:
{ "_id" : 1, "total" : 20 }
{ "_id" : 2, "total" : 20 }
{ "_id" : 3, "total" : 50 }
# pow
Raises the given base to the power of the exponent.
The example collection test data is as follows:
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity": 2 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity": 1 }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity": 10 }
Use the multiply expression to multiply the price and quantity fields:
db.collection('test').field('multiply(price, quantity) as total').get()
Results of the:
{ "_id" : 1, "total" : 20 }
{ "_id" : 2, "total" : 20 }
{ "_id" : 3, "total" : 50 }
# sqrt
Calculates the square root.
The example collection test data is as follows:
{ _id: 1, p1: { x: 5, y: 8 }, p2: { x: 0, y: 5} }
{ _id: 2, p1: { x: -2, y: 1 }, p2: { x: 1, y: 5} }
{ _id: 3, p1: { x: 4, y: 4 }, p2: { x: 4, y: 0} }
The following example calculates the distance between p1 and p2:
db.collection('test').field('sqrt(add(pow(subtract(p2.y, p1.y), 2), pow(subtract(p2.x, p1.x), 2))) as distance').get()
Results of the:
{ "_id" : 1, "distance" : 5.830951894845301 }
{ "_id" : 2, "distance" : 5 }
{ "_id" : 3, "distance" : 4 }
# subtract
Subtracts two numbers and returns the difference, or subtracts two dates and returns the difference in milliseconds, or subtracts a number from a date and returns the date of the result.
The example collection test data is as follows:
{ "_id" : 1, "item" : "abc", "price" : 10, "fee" : 2, "discount" : 5, "date" : 1393660800000 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "fee" : 1, "discount" : 2, "date" : 1393664400000 }
subtract number
db.collection('test').field('subtract(add(price, fee), discount) as total').get()
Results of the:
{ "_id" : 1, "item" : "abc", "total" : 7 }
{ "_id" : 2, "item" : "jkl", "total" : 19 }
Subtract 1 day from current date
db.collection('test').field('subtract(date, 24 * 60 * 60 * 1000) as dateDifference').get()
Results of the:
{ "_id" : 1, "item" : "abc", "dateDifference" : 1393747200000 }
{ "_id" : 2, "item" : "jkl", "dateDifference" : 1393750800000 }
# trunc
Truncates a number to an integer.
The example collection test data is as follows:
{ _id: 1, value: 19.25 }
{ _id: 2, value: 28.73 }
{ _id: 3, value: 34.32 }
{ _id: 4, value: -45.34 }
截断 value 为整数的信息：
db.collection('test').field('trunc(value) as truncatedValue').get()
Results of the:
{ "_id" : 1, "truncatedValue" : 19 }
{ "_id" : 2, "truncatedValue" : 28 }
{ "_id" : 3, "truncatedValue" : 34 }
{ "_id" : 4, "truncatedValue" : -45 }
# Array operation method
# arrayElemAt
Returns the element at the specified array index.
The example collection users contains the following documents:
{"_id":1,"name":"dave123",favorites:["chocolate","cake","butter","apples"]}
{"_id":2,"name":"li",favorites:["apples","pudding","pie"]}
{"_id":3,"name":"ahn",favorites:["pears","pecans","chocolate","cherries"]}
{"_id":4,"name":"ty",favorites:["ice cream"]}
Return the first and last elements in the favorites array:
db.collection('users').field('arrayElemAt(favorites, 0) as first, arrayElemAt(favorites, -1) as last').get()
Results of the:
{"_id":1,"first":"chocolate","last":"apples"}
{"_id":2,"first":"apples","last":"pie"}
{"_id":3,"first":"pears","last":"cherries"}
{"_id":4,"first":"ice cream","last":"ice cream"}
# arrayToObject
Convert an array to an object.
The example collection inventory contains a collection of the following documents:
{"_id":1,"item":"ABC1",dimensions:[{"k":"l","v":25},{"k":"w","v":10},{"k":"uom","v":"cm"}]}
{"_id":2,"item":"ABC2",dimensions:[["l",50],["w",25],["uom","cm"]]}
{"_id":3,"item":"ABC3",dimensions:[["l",25],["l","cm"],["l",50]]}
The following returns the dimensions field as an object:
db.collection('inventory').field('item, arrayToObject(dimensions) as dimensionsObject').get()
Results of the:
{"_id":1,"item":"ABC1","dimensionsObject":{"l":25,"w":10,"uom":"cm"}}
{"_id":2,"item":"ABC2","dimensionsObject":{"l":50,"w":25,"uom":"cm"}}
{"_id":3,"item":"ABC3","dimensionsObject":{"l":50}}
# concatArrays
Concatenate multiple arrays into one array.
The example collection warehouses contains the following documents:
{"_id":1,instock:["chocolate"],ordered:["butter","apples"]}
{"_id":2,instock:["apples","pudding","pie"]}
{"_id":3,instock:["pears","pecans"],ordered:["cherries"]}
{"_id":4,instock:["ice cream"],ordered:[]}
The following example concatenates the instock and ordered arrays:
db.collection('warehouses').field('concatArrays(instock, ordered) as items').get()
Results of the:
{"_id":1,"items":["chocolate","butter","apples"]}
{"_id":2,"items":null}
{"_id":3,"items":["pears","pecans","cherries"]}
{"_id":4,"items":["ice cream"]}
# filter
Returns the subset of the array that satisfies the condition based on the given condition.
The example collection sales contains the following documents:
{_id:0,items:[{item_id:43,quantity:2,price:10},{item_id:2,quantity:1,price:240}]}
{_id:1,items:[{item_id:23,quantity:3,price:110},{item_id:103,quantity:4,price:5},{item_id:38,quantity:1,price:300}]}
{_id:2,items:[{item_id:4,quantity:1,price:23}]}
Filter the items array to only contain documents with price greater than or equal to 100:
db.collection('sales').field('filter(items, "item", gte("$$item.price", 100)) as filterItems').get()
Results of the:
{"_id":0,"filterItems":[{"item_id":2,"quantity":1,"price":240}]}
{"_id":1,"filterItems":[{"item_id":23,"quantity":3,"price":110},{"item_id":38,"quantity":1,"price":300}]}
{"_id":2,"filterItems":[]}
# in
Given a value and an array, return true if the value is in the array, false otherwise.
The example collection fruit has the following documents:
{"_id":1,"location":"24th Street","in_stock":["apples","oranges","bananas"]}
{"_id":2,"location":"36th Street","in_stock":["bananas","pears","grapes"]}
{"_id":3,"location":"82nd Street","in_stock":["cantaloupes","watermelons","apples"]}
Determine whether the bananas string exists in the in_stock array:
db.collection('fruit').field('location as storeLocation, in("bananas", in_stock) as hasHananas').get()
Results of the:
{"_id":1,"storeLocation":"24th Street","hasBananas":true}
{"_id":2,"storeLocation":"36th Street","hasBananas":true}
{"_id":3,"storeLocation":"82nd Street","hasBananas":false}
# indexOfArray
Finds the index of the first element in the array equal to the given value, or returns -1 if not found.
The example collection inventory contains the following documents:
{"_id":1,"items":["one","two","three"]}
{"_id":2,"items":[1,2,3]}
{"_id":3,"items":[null,null,2]}
{"_id":4,"items":null}
{"_id":5,"amount":3}
Query the array index where the number 2 is located in each items array:
db.collection('inventory').field('indexOfArray(items, 2) as index').get()
Results of the:
{"_id":1,"index":"-1"}
{"_id":2,"index":"1"}
{"_id":3,"index":"2"}
{"_id":4,"index":null}
{"_id":5,"index":null}
# isArray
Determines whether the given expression is an array, returns a Boolean value.
The example collection warehouses contains the following documents:
{"_id":1,instock:["chocolate"],ordered:["butter","apples"]}
{"_id":2,instock:["apples","pudding","pie"]}
{"_id":3,instock:["pears","pecans"],ordered:["cherries"]}
{"_id":4,instock:["ice cream"],ordered:[]}
Check that the instock and ordered fields are arrays before concatenating the two fields together:
db.collection('warehouses').field('cond(and(isArray(instock), isArray(ordered)), concatArrays(instock, ordered), "有字段不是数组类型") as items').get()
Results of the:
{"_id":1,"items":["chocolate","butter","apples"]}
{"_id":2,"items":"有字段不是数组类型"}
{"_id":3,"items":["pears","pecans","cherries"]}
{"_id":4,"items":["ice cream"]}
# map
Similar to the map method on JavaScript Array, each element of the given array is converted by the given conversion method to obtain a new array.
An example collection grades is the following documents:
{_id:1,quizzes:[5,6,7]}
{_id:2,quizzes:[]}
{_id:3,quizzes:[3,8,9]}
Increment each element in the quizzes array by 1
db.collection('grades').field('map(quizzes, "grade", add("$$grade", 2)) as adjustedGrades').get()
Results of the:
{"_id":1,"adjustedGrades":[7,8,9]}
{"_id":2,"adjustedGrades":[]}
{"_id":3,"adjustedGrades":[5,10,11]}
# objectToArray
Convert an object to an array. The method turns each key-value pair of the object into an element of the output array, and the element is in the form of { k: <key>, v: <value> }
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1",dimensions:{l:25,w:10,uom:"cm"}}
{"_id":2,"item":"ABC2",dimensions:{l:50,w:25,uom:"cm"}}
{"_id":3,"item":"XYZ1",dimensions:{l:70,w:75,uom:"cm"}}
Return the dimensions field as an array:
db.collection('inventory').field('item, objectToArray(dimensions) as dimensionsArray').get()
Results of the:
{"_id":1,"item":"ABC1","dimensionsArray":[{"k":"l","v":25},{"k":"w","v":10},{"k":"uom","v":"cm"}]}
{"_id":2,"item":"ABC2","dimensionsArray":[{"k":"l","v":50},{"k":"w","v":25},{"k":"uom","v":"cm"}]}
{"_id":3,"item":"XYZ1","dimensionsArray":[{"k":"l","v":70},{"k":"w","v":75},{"k":"uom","v":"cm"}]}
# range
Returns an array of generated sequence numbers. Given a start value, an end value, and a non-zero step size, range returns a sequence that increases incrementally from the start value by the given step size, excluding the end value.
The example collection distances contains the following documents:
{_id:0,city:"San Jose",distance:42}
{_id:1,city:"Sacramento",distance:88}
{_id:2,city:"Reno",distance:218}
{_id:3,city:"Los Angeles",distance:383}
A cyclist is planning to cycle from San Francisco to every city listed in the set and wants to stop every 25 miles to take a break.
The following operations are performed to determine the stopping points for each trip.
db.collection('distances').field('city, range(0, distance, 25) as restStops').get()
The operation returns the following:
{"city":"San Jose","restStops":[0,25]}
{"city":"Sacramento","restStops":[0,25,50,75]}
{"city":"Reno","restStops":[0,25,50,75,100,125,150,175,200]}
{"city":"Los Angeles","restStops":[0,25,50,75,100,125,150,175,200,225,250,275,300,325,350,375]}
# reduce
Similar to JavaScript's reduce method, it applies an expression to each element of an array and reduces it to a single element.
The example collection clothes contains the following documents:
{"_id":1,"productId":"ts1","description":"T-Shirt","color":"black","size":"M","price":20,"discounts":[0.5,0.1]}
{"_id":2,"productId":"j1","description":"Jeans","color":"blue","size":"36","price":40,"discounts":[0.25,0.15,0.05]}
{"_id":3,"productId":"s1","description":"Shorts","color":"beige","size":"32","price":30,"discounts":[0.15,0.05]}
{"_id":4,"productId":"ts2","description":"Cool T-Shirt","color":"White","size":"L","price":25,"discounts":[0.3]}
{"_id":5,"productId":"j2","description":"Designer Jeans","color":"blue","size":"30","price":80,"discounts":[0.1,0.25]}
Each document contains a discounts array containing currently available coupons for each item.
If each discount can be applied to the product at one time, the minimum price can be calculated by applying the following formula to each element in the discounts array via reduce
(1-discount)*price.
db.collection('clothes').field('reduce(discounts, price, multiply("$$vaule", subtract(1, "$$this")) as discountedPrice').get()
The operation returns the following:
{"_id":ObjectId("57c893067054e6e47674ce01"),"discountedPrice":9}
{"_id":ObjectId("57c9932b7054e6e47674ce12"),"discountedPrice":24.224999999999998}
{"_id":ObjectId("57c993457054e6e47674ce13"),"discountedPrice":24.224999999999998}
{"_id":ObjectId("57c993687054e6e47674ce14"),"discountedPrice":17.5}
{"_id":ObjectId("57c993837054e6e47674ce15"),"discountedPrice":54}
# reverseArray
Returns the reversed form of the given array.
The example collection users contains the following documents:
{"_id":1,"name":"dave123","favorites":["chocolate","cake","butter","apples"]}
{"_id":2,"name":"li","favorites":["apples","pudding","pie"]}
{"_id":3,"name":"ahn","favorites":[]}{"_id":4,"name":"ty"}
The following example favorites returns an array containing the elements of the array in reverse order:
db.collection('users').field('name, reverseArray(favorites) as reverseFavorites').get()
Results of the:
{"_id":1,"name":"dave123","reverseFavorites":["apples","butter","cake","chocolate"]}
{"_id":2,"name":"li","reverseFavorites":["pie","pudding","apples"]}
{"_id":3,"name":"ahn","reverseFavorites":[]}{"_id":4,"name":"ty","reverseFavorites":null}
# size
Returns the length of the array.
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1","description":"product 1",colors:["blue","black","red"]}
{"_id":2,"item":"ABC2","description":"product 2",colors:["purple"]}
{"_id":3,"item":"XYZ1","description":"product 3",colors:[]}
{"_id":4,"item":"ZZZ1","description":"product 4 - missing colors"}
{"_id":5,"item":"ZZZ2","description":"product 5 - colors is string",colors:"blue,red"}
Returns the number of elements in the colors array:
db.collection('inventory').field('item, cond(isArray(colors), size(colors), "None") as numberOfColors').get()
The operation returns the following:
{"_id":1,"item":"ABC1","numberOfColors":3}
{"_id":2,"item":"ABC2","numberOfColors":1}
{"_id":3,"item":"XYZ1","numberOfColors":0}
{"_id":4,"item":"ZZZ1","numberOfColors":"None"}
{"_id":5,"item":"ZZZ2","numberOfColors":"None"}
# slice
Similar to JavaScript's slice method. Returns the specified subset of the given array.
The example collection users contains the following documents:
{"_id":1,"name":"dave123",favorites:["chocolate","cake","butter","apples"]}
{"_id":2,"name":"li",favorites:["apples","pudding","pie"]}
{"_id":3,"name":"ahn",favorites:["pears","pecans","chocolate","cherries"]}
{"_id":4,"name":"ty",favorites:["ice cream"]}
Return at most the first three elements in the favorites array for each user:
db.collection('users').field('name, slice(favorites, 3) as threeFavorites').get()
Results of the:
{"_id":1,"name":"dave123","threeFavorites":["chocolate","cake","butter"]}
{"_id":2,"name":"li","threeFavorites":["apples","pudding","pie"]}
{"_id":3,"name":"ahn","threeFavorites":["pears","pecans","chocolate"]}
{"_id":4,"name":"ty","threeFavorites":["ice cream"]}
# zip
The elements of the same serial number in the second dimension array of the two-dimensional array are respectively assembled into a new array and then assembled into a new two-dimensional array.
The example collection matrices contains the following documents:
{matrix:[[1,2],[2,3],[3,4]]}
{matrix:[[8,7],[7,6],[5,4]]}
Compute the transpose of each 3x2 matrix in this set:
db.collection('matrices').field('zip(arrayElemAt(matrix, 0), arrayElemAt(matrix, 1), arrayElemAt(matrix, 2))').get()
Execution returns:
{"transposed":[[1,2,3],[2,3,4]]}
{"transposed":[[8,7,5],[7,6,4]]}
# Boolean methods
A Boolean expression evaluates its argument expressions to Boolean values and returns a Boolean value as the result.
# and
Given multiple expressions, and returns true only if all of them return true, and returns false otherwise.
Accepts any number of argument expressions.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
Determine whether the qty field is greater than 100 and less than 250:
db.collection('inventory').field('item, qty, and(gt(qty, 100), lt(qty, 250)) as result').get()
Results of the:
{"_id":1,"item":"abc1","qty":300,"result":false}
{"_id":2,"item":"abc2","qty":200,"result":true}
{"_id":3,"item":"xyz1","qty":250,"result":false}
{"_id":4,"item":"VWZ1","qty":300,"result":false}
{"_id":5,"item":"VWZ2","qty":180,"result":true}
# not
Given an expression, not returns false if the expression returns true, and true otherwise. Note that the expression cannot be a logical expression (and, or, nor, not).
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
Determine whether the qty field is not greater than 250:
db.collection('inventory').field('item, not(gt(qty, 250)) as result').get()
Results of the:
{"_id":1,"item":"abc1","result":false}
{"_id":2,"item":"abc2","result":true}
{"_id":3,"item":"xyz1","result":true}
{"_id":4,"item":"VWZ1","result":false}
{"_id":5,"item":"VWZ2","result":true}
# or
Given multiple expressions, or returns true if any one of them returns true, otherwise it returns false.
Accepts any number of argument expressions.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
Determine whether the qty field is greater than 250 or smaller than 200:
db.collection('inventory').field('item, or(gt(qty, 250), lt(qty, 200)) as result').get()
Results of the:
{"_id":1,"item":"abc1","result":true}
{"_id":2,"item":"abc2","result":false}
{"_id":3,"item":"xyz1","result":false}
{"_id":4,"item":"VWZ1","result":true}
{"_id":5,"item":"VWZ2","result":true}
# Comparison operation method
Comparison expressions return a boolean value, except cmp, which returns a number.
# cmp
Given two values, return their comparison value. Returns -1 if the first value is less than the second value 1 if the first value is greater than the second value 0 if the two values are equal.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
The qty value is compared to 250:
db.collection('inventory').field('item, qty, cmp(qty, 250) as cmpTo250').get()
Results of the:
{"_id": 1,"item":"abc1","qty":300,"cmpTo250":1}
{"_id": 2,"item":"abc2","qty":200,"cmpTo250":-1}
{"_id": 3,"item":"xyz1","qty":250,"cmpTo250":0}
{"_id": 4,"item":"VWZ1","qty":300,"cmpTo250":1}
{"_id": 5,"item":"VWZ2","qty":180,"cmpTo250":-1}
# eq
Matches two values, returning true if they are equal, otherwise false.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
Determine whether qty is equal to 250:
db.collection('inventory').field('item, qty, eq(qty, 250) as qtyEq250').get()
Results of the:
{"id": 1,"item":"abc1","qty":300,"qtyEq250":false}
{"id": 2,"item":"abc2","qty":200,"qtyEq250":false}
{"id": 3,"item":"xyz1","qty":250,"qtyEq250":true}
{"id": 4,"item":"VWZ1","qty":300,"qtyEq250":false}
{"id": 5,"item":"VWZ2","qty":180,"qtyEq250":false}
# gt
Matches two values, returning true if the former is greater than the latter, false otherwise.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
Determine whether qty is greater than 250:
db.collection('inventory').field('item, qty, gt(qty, 250) as qtyGt250').get()
Results of the:
{"item":"abc1","qty":300,"qtyGt250":true}
{"item":"abc2","qty":200,"qtyGt250":false}
{"item":"xyz1","qty":250,"qtyGt250":false}
{"item":"VWZ1","qty":300,"qtyGt250":true}
{"item":"VWZ2","qty":180,"qtyGt250":false}
# gte
Matches two values, returning true if the former is greater than or equal to the latter, false otherwise.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
Determine whether qty is greater than or equal to 250:
db.collection('inventory').field('item, qty, gte(qty, 250) as qtyGte250').get()
Results of the:
{"item":"abc1","qty":300,"qtyGte250":true}
{"item":"abc2","qty":200,"qtyGte250":false}
{"item":"xyz1","qty":250,"qtyGte250":true}
{"item":"VWZ1","qty":300,"qtyGte250":true}
{"item":"VWZ2","qty":180,"qtyGte250":false}
# lt
Matches two values, returning true if the former is less than the latter, false otherwise.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
Determine whether qty is less than 250:
db.collection('inventory').field('item, qty, lt(qty, 250) as qtyLt250').get()
Results of the:
{"_id": 1,"item":"abc1","qty":300,"qtyLt250":false}
{"_id": 2,"item":"abc2","qty":200,"qtyLt250":true}
{"_id": 3,"item":"xyz1","qty":250,"qtyLt250":false}
{"_id": 4,"item":"VWZ1","qty":300,"qtyLt250":false}
{"_id": 5,"item":"VWZ2","qty":180,"qtyLt250":true}
# lte
Matches two values, returning true if the former is less than or equal to the latter, false otherwise.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
Determine whether qty is less than or equal to 250:
db.collection('inventory').field('item, qty, lte(qty, 250) as qtyLte250').get()
Results of the:
{"_id": 1,"item":"abc1","qty":300,"qtyLte250":false}
{"_id": 2,"item":"abc2","qty":200,"qtyLte250":true}
{"_id": 3,"item":"xyz1","qty":250,"qtyLte250":true}
{"_id": 4,"item":"VWZ1","qty":300,"qtyLte250":false}
{"_id": 5,"item":"VWZ2","qty":180,"qtyLte250":true}
# neq
Matches two values, returning true if they are not equal, false otherwise.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}
Determine whether qty is not equal to 250:
db.collection('inventory').field('item, qty, neq(qty, 250) as qtyNe250').get()
Results of the:
{"_id": 1,"item":"abc1","qty":300,"qtyNe250":true}
{"_id": 2,"item":"abc2","qty":200,"qtyNe250":true}
{"_id": 3,"item":"xyz1","qty":250,"qtyNe250":false}
{"_id": 4,"item":"VWZ1","qty":300,"qtyNe250":true}
{"_id": 5,"item":"VWZ2","qty":180,"qtyNe250":true}
# Conditional operation method
# cond
Evaluates Boolean expression 1, returns expression 2 if true, otherwise returns expression 3.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",qty:300}
{"_id":2,"item":"abc2",qty:200}
{"_id":3,"item":"xyz1",qty:250}
If qty value is greater than or equal to 250, set discount to 30, otherwise set to 20:
db.collection('inventory').field('item, cond(gte(qty, 250), 30, 20) as discount').get()
Results of the:
{"_id":1,"item":"abc1","discount":30}
{"_id":2,"item":"abc2","discount":20}
{"_id":3,"item":"xyz1","discount":30}
# ifNull
Evaluates the given expression and returns a replacement value if the expression evaluates to null, undefined, or does not exist; otherwise returns the original value.
The example collection inventory contains the following documents:
{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:null,qty:200}
{"_id":3,"item":"xyz1",qty:250}
If the description field is empty or does not exist, return the string "Unspecified", otherwise return the value of the description field:
db.collection('inventory').field('item, ifNull(description, "Unspecified") as description').get()
Results of the:
{"_id":1,"item":"abc1","description":"product 1"}
{"_id":2,"item":"abc2","description":"Unspecified"}
{"_id":3,"item":"xyz1","description":"Unspecified"}
# switch
Computes the return value according to the given switch-case-default.
The example collection grades contains the following documents:
{"_id":1,"name":"Susan Wilkes","scores":[87,86,78]}
{"_id":2,"name":"Bob Hanna","scores":[71,64,81]}
{"_id":3,"name":"James Torrelio","scores":[91,84,97]}
Display specific messages based on each student's average score.
db.collection('grades').field('name, switch([{case: gte(avg(scores), 90), then: "Doing great!"}, {case: and(gte(avg(scores), 80), lt(avg(scores), 90)), then: "Doing pretty well."}, {case: lt(avg(scores), 80), then: "Needs improvement."}], "No scores found.")').get()
The operation returns the following:
{"_id":1,"name":"Susan Wilkes","summary":"Doing pretty well."}
{"_id":2,"name":"Bob Hanna","summary":"Needs improvement."}
{"_id":3,"name":"James Torrelio","summary":"Doing great!"}
# Date operation method
# dateFromParts
Given information about a date, construct and return a date object.
Constructs three date objects from the provided input fields:
db.collection('sales').field('dateFromParts(2017, 2, 8, 12) as date, isoDateFromParts(2017, 6, 3, 12) as date_iso, dateFromParts(2016, 12, 31, 23, 46, 12, "America/New_York") as date_timezone').get()
Results of the:
{"_id":1,"date":"2017-02-08T12:00:00Z","date_iso":"2017-02-08T12:00:00Z","date_timezone":"2017-01-01T04:46:12Z"}
# dateFromString
Convert a date/time string to a date object.
The example collection logmessages contains the following collections.
{_id:1,date:"2017-02-08T12:10:40.787",timezone:"America/New_York",message:"Step 1: Started"}
{_id:2,date:"2017-02-08",timezone:"-05:00",message:"Step 1: Ended"}
{_id:3,message:" Step 1: Ended "}
{_id:4,date:"2017-02-09",timezone:"Europe/London",message:"Step 2: Started"}
{_id:5,date:"2017-02-09T03:35:02.055",timezone:"+0530",message:"Step 2: In Progress"}
** Convert a date value to a date object:**
db.collection('logmessages').field('dateFromString(date, "%Y-%m-%dT%H:%M:%S.%LZ", "America/New_York") as date').get()
Results of the:
{"_id":1,"date": "2017-02-08T17:10:40.787Z"}
{"_id":2,"date": "2017-02-08T05:00:00Z"}
{"_id":3,"date":null}
{"_id":4,"date": "2017-02-09T05:00:00Z"}
{"_id":5,"date": "2017-02-09T08:35:02.055Z"}
** The timezone parameter can also be provided via a document field instead of a hardcoded parameter. For example:**
db.collection('logmessages').field('dateFromString(date, "%Y-%m-%dT%H:%M:%S.%LZ", timezone) as date').get()
Results of the:
{"_id":1,"date":"2017-02-08T17:10:40.787Z"}
{"_id":2,"date":"2017-02-08T05:00:00Z"}
{"_id":3,"date":null}
{"_id":4,"date":"2017-02-09T00:00:00Z"}
{"_id":5,"date":"2017-02-08T22:05:02.055Z"}
If the collection has an unparseable date string, an error onError will be raised, and the onError parameter can be used to return an invalid date in its raw string form:
An example collection dates is as follows:
{"_id":1,"date":"2017-02-08T12:10:40.787",timezone:"America/New_York"}
{"_id":2,"date":"20177-02-09T03:35:02.055",timezone:"America/New_York"}
db.collection('dates').field('dateFromString(date, "%Y-%m-%dT%H:%M:%S.%L", timezone, date)')
Results of the:
{"_id":1,"date": "2017-02-08T17:10:40.787Z"}
{"_id":2,"date": "20177-02-09T03:35:02.055"}
If there is a null date string in the collection, you can use the onNull parameter to return a date:
An example collection dates is as follows:
{"_id":1,"date":"2017-02-08T12:10:40.787",timezone:"America/New_York"}
{"_id":2,"date":null,timezone:"America/New_York"}
db.collection('dates').field('dateFromString(date, "%Y-%m-%dT%H:%M:%S.%L", timezone, date, new Date(0))')
Results of the:
{"_id":1,"date": "2017-02-08T17:10:40.787Z"}
{"_id":2,"date": "1970-01-01T00:00:00Z"}
# dateToString
Formats a date object as a string according to the specified expression.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Format the date field and return:
db.collection('sales').field('dateToString(date, "%Y-%m-%d") as yearMonthDayUTC, dateToString(date, "%H:%M:%S:%L%z", "America/New_York") as timewithOffsetNY, dateToString(date, "%H:%M:%S:%L%z", "+04:30") as timewithOffset430, dateToString(date, "%Z", "America/New_York") as minutesOffsetNY, dateToString(date, "%Z", "+04:30") as minutesOffset430').get()
Results of the:
{"_id":1,"yearMonthDayUTC":"2014-01-01","timewithOffsetNY":"03:15:39:736-0500","timewithOffset430":"12:45:39:736+0430","minutesOffsetNY":"-300","minutesOffset430":"270"}
# dayOfMonth
Returns the day (the day of the month) corresponding to the date field, which is a number between 1 and 31.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
Results of the:
{"_id":1,"year":2014,"month":1,"day":1,"hour":8,"minutes":15,"seconds":39,"milliseconds":736,"dayOfYear":1,"dayOfWeek":4,"week":0}
# dayOfWeek
Returns the day (day of the week) corresponding to the date field, which is an integer between 1 (Sunday) and 7 (Saturday).
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
Results of the:
{"_id":1,"year":2014,"month":1,"day":1,"hour":8,"minutes":15,"seconds":39,"milliseconds":736,"dayOfYear":1,"dayOfWeek":4,"week":0}
# dayOfYear
Returns the day number (the day of the year) corresponding to the date field, which is an integer between 1 and 366.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
Results of the:
{"_id":1,"year":2014,"month":1,"day":1,"hour":8,"minutes":15,"seconds":39,"milliseconds":736,"dayOfYear":1,"dayOfWeek":4,"week":0}
# hour
Returns the hour corresponding to the date field, an integer between 0 and 23.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
Results of the:
{"_id":1,"year":2014,"month":1,"day":1,"hour":8,"minutes":15,"seconds":39,"milliseconds":736,"dayOfYear":1,"dayOfWeek":4,"week":0}
# isoDayOfWeek
Returns the ISO 8601 day number (day of the week) corresponding to the date field, which is an integer between 1 (Monday) and 7 (Sunday).
The example collection birthdays contains the following documents:
{"_id":1,"name":"Betty","birthday":"1993-09-21T00:00:00Z"}
{"_id":2,"name":"Veronica","birthday":"1981-11-07T00:00:00Z"}
Return the day of the week from the birthday field:
db.collection('dates').field('name, isoDayOfWeek(birthday) as dayOfWeek').get()
Results of the:
{"name":"Betty","dayOfWeek":2}{"name":"Veronica","dayOfWeek":6}
# isoWeek
Returns the ISO 8601 week number (week of the year) corresponding to the date field, an integer between 1 and 53.
The example collection deliveries contains the following documents:
{"_id":1,"date":"2006-10-24T00:00:00Z","city":"Boston"}
{"_id":2,"date":"2011-08-18T00:00:00Z","city":"Detroit"}
Returns the week number for each date field.
db.collection('deliveries').field('city, isoWeek(date) as weekNumber').get()
Results of the:
{"city":"Boston","weekNumber":43}{"city":"Detroit","weekNumber":33}
# isoWeekYear
Returns the ISO 8601 day number (day of the year) for a date field.
The example collection anniversaries contains the following documents:
{"_id":1,"date":"2016-01-01T00:00:00Z"}
{"_id":2,"date":"2016-01-04T00:00:00Z"}
{"_id":3,"date":"2015-01-01T00:00:00Z"}
{"_id":4,"date":"2014-04-21T00:00:00Z"}
Returns the year in ISO 8601 format for the date field.
db.collection('anniversaries').field('isoWeekYear(date) as yearNumber').get()
Results of the:
{"_id":1,"yearNumber":2015}
{"_id":2,"yearNumber":2016}
{"_id":3,"yearNumber":2015}
{"_id":4,"yearNumber":2014}
# millisecond
Returns the milliseconds corresponding to the date field, which is an integer between 0 and 999.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
# minute
Returns the minute corresponding to the date field, an integer between 0 and 59.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
# month
Returns the month corresponding to the date field, which is an integer between 1 and 12.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
# second
Returns the number of seconds corresponding to the date field, an integer between 0 and 59, which may be equal to 60 in special cases (leap seconds).
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
# week
Returns the week number (week of the year) corresponding to the date field, which is an integer between 0 and 53.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
# year
Returns the year corresponding to the date field.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}
Parsing the date field:
db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()
# timestampToDate
Pass in a timestamp and return the corresponding date object.
Only supported in JQL strings, supported since HBuilderX 3.1.0
The example collection sales contains the following documents:
{"_id":1,"item":"abc","date": 1664697600000}
Return a date object for the date field:
db.collection('sales').field('item, timestampToDate(date) as date').get()
Results of the:
{"_id":1,"item":"abc","date": "2022-10-02T08:00:00.000Z"}
# Literal operation methods
# literal
Directly returns a value literal without any parsing and processing.
Treat $ as a literal value
The example collection records contains the following documents:
{"_id":1,"item":"abc123",price:"$2.50"}
{"_id":2,"item":"xyz123",price:"1"}
{"_id":3,"item":"ijk123",price:"$1"}
Check whether price is equal to the string $1:
db.collection('records').field('eq(price, literal($1)) as costsOneDollar').get()
Results of the:
{"_id":1,"costsOneDollar":false}
{"_id":2,"costsOneDollar":false}
{"_id":3,"costsOneDollar":true}
# Object operation method
# mergeObjects
Merge multiple objects into a single object.
Note: If the documents to be merged contain the same field names, the fields in the resulting document will override the values in the last merged document.
The example collection orders contains the following documents:
{"_id":1,"item":"abc","price":12,"ordered":2}
{"_id":2,"item":"jkl","price":20,"ordered":1}
Another example collection items contains the following documents:
{"_id":1,"item":"abc",description:"product 1","instock":120}
{"_id":2,"item":"def",description:"product 2","instock":80}
{"_id":3,"item":"jkl",description:"product 3","instock":60}
Merge items and orders
First associate the two tables through lookup, and then use the mergeObjects expression to merge the two tables in the replaceRoot stage.
db.collection('orders').aggregate().lookup({
from: "items",
let: {
item: "$item"
},
pipeline: $.pipeline().match(db.command.expr($.and([
$.eq(['$item', '$$item'])
]))).done(),
as: 'fromItems'
}).replaceRoot({
newRoot: $.mergeObjects([ $.arrayElemAt(['$fromItems', 0]), '$$ROOT' ])
}).project({
fromItems: 0
}).end()
Execution returns:
{"_id":1,"item":"abc","description":"product 1","instock":120,"price":12,"ordered":2}
{"_id":2,"item":"jkl","description":"product 3","instock":60,"price":20,"ordered":1}
used in groupBy
The example collection sales contains the following documents:
{_id:1,year:2017,item:"A",quantity:{"2017Q1":500,"2017Q2":500}}
{_id:2,year:2016,item:"A",quantity:{"2016Q1":400,"2016Q2":300,"2016Q3":0,"2016Q4":0}}
{_id:3,year:2017,item:"B",quantity:{"2017Q1":300}}
{_id:4,year:2016,item:"B",quantity:{"2016Q3":100,"2016Q4":250}}
Group by item, mergeObjects only accepts single object operations.
db.collection('sales').groupBy('item').groupField('mergeObjects(quantity) as mergedSales').get()
Results of the:
{"_id":"B","mergedSales":{"2017Q1":300,"2016Q3":100,"2016Q4":250}}
{"_id":"A","mergedSales":{"2017Q1":500,"2017Q2":500,"2016Q1":400,"2016Q2":300,"2016Q3":0,"2016Q4":0}}
# objectToArray
Convert a document to an array of documents representing key-value pairs.
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1",dimensions:{l:25,w:10,uom:"cm"}}
{"_id":2,"item":"ABC2",dimensions:{l:50,w:25,uom:"cm"}}
{"_id":3,"item":"XYZ1",dimensions:{l:70,w:75,uom:"cm"}}
Return the dimensions field as an array:
db.collection('inventory').field('item, objectToArray(dimensions) as dimensionsArray').get()
Results of the:
{"_id":1,"item":"ABC1","dimensions":[{"k":"l","v":25},{"k":"w","v":10},{"k":"uom","v":"cm"}]}
{"_id":2,"item":"ABC2","dimensions":[{"k":"l","v":50},{"k":"w","v":25},{"k":"uom","v":"cm"}]}
{"_id":3,"item":"XYZ1","dimensions":[{"k":"l","v":70},{"k":"w","v":75},{"k":"uom","v":"cm"}]}
# Set operation method
Set expressions perform set operations on arrays, treating arrays as sets. Set expressions ignore duplicate entries and the order of elements in each input array.
If a set operation returns a set, the operation filters out duplicates in the result to output an array containing only unique entries. The order of elements in the output array is unspecified.
If the set contains nested array elements, the set expression does not descend into the nested array, but evaluates the array at the top level.
# allElementsTrue
Enter an array, or an expression for an array field. Returns true if all elements in the array are true, otherwise returns false. Empty arrays always return true.
The example collection survey uses the following documents:
{"_id":1,"responses":[true]}
{"_id":2,"responses":[true,false]}
{"_id":3,"responses":[]}
{"_id":4,"responses":[1,true,"seven"]}
{"_id":5,"responses":[0]}
{"_id":6,"responses":[[]]}
{"_id":7,"responses":[[0]]}
{"_id":8,"responses":[[false]]}
{"_id":9,"responses":[null]}
{"_id":10,"responses":[undefined]}
Check if the responses array contains only values that evaluate to true:
db.collection('survey').field('responses, allElementsTrue(responses) as isAllTrue').get()
Results of the:
{"responses":[true],"isAllTrue":true}
{"responses":[true,false],"isAllTrue":false}
{"responses":[],"isAllTrue":true}
{"responses":[1,true,"seven"],"isAllTrue":true}
{"responses":[0],"isAllTrue":false}
{"responses":[[]],"isAllTrue":true}
{"responses":[[0]],"isAllTrue":true}
{"responses":[[false]],"isAllTrue":true}
{"responses":[null],"isAllTrue":false}
{"responses":[undefined],"isAllTrue":false}
# anyElementTrue
Enter an array, or an expression for an array field. Returns true if any element in the array is true, otherwise returns false. Empty arrays always return false.
The example collection survey contains the following documents:
{"_id":1,"responses":[true]}
{"_id":2,"responses":[true,false]}
{"_id":3,"responses":[]}
{"_id":4,"responses":[1,true,"seven"]}
{"_id":5,"responses":[0]}
{"_id":6,"responses":[[]]}
{"_id":7,"responses":[[0]]}
{"_id":8,"responses":[[false]]}
{"_id":9,"responses":[null]}
{"_id":10,"responses":[undefined]}
Determine if the responses array contains any that evaluate to true:
db.collection('survey').field('responses, anyElementTrue(responses) as isAnyTrue').get()
Results of the:
{"responses":[true],"isAnyTrue":true}
{"responses":[true,false],"isAnyTrue":true}
{"responses":[],"isAnyTrue":false}
{"responses":[1,true,"seven"],"isAnyTrue":true}
{"responses":[0],"isAnyTrue":false}
{"responses":[[]],"isAnyTrue":true}
{"responses":[[0]],"isAnyTrue":true}
{"responses":[[false]],"isAnyTrue":true}
{"responses":[null],"isAnyTrue":false}
{"responses":[undefined],"isAnyTrue":false}
# setDifference
Input two collections, output elements that only exist in the first collection
The example collection experiments contains the following documents:
{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}
{"_id":9,"A":[],"B":["red"]}
Compare A and B arrays and return only the values in array B:
db.collection('experiments').field('A, B, setDifference(B, A) as inBOnly').get()
Results of the:
{"A":["red","blue"],"B":["red","blue"],"inBOnly":[]}
{"A":["red","blue"],"B":["blue","red","blue"],"inBOnly":[]}
{"A":["red","blue"],"B":["red","blue","green"],"inBOnly":["green"]}
{"A":["red","blue"],"B":["green","red"],"inBOnly":["green"]}
{"A":["red","blue"],"B":[],"inBOnly":[]}
{"A":["red","blue"],"B":[["red"],["blue"]],"inBOnly":[["red"],["blue"]]}
{"A":["red","blue"],"B":[["red","blue"]],"inBOnly":[["red","blue"]]}
{"A":[],"B":[],"inBOnly":[]}
{"A":[],"B":["red"],"inBOnly":["red"]}
# setEquals
Enter two collections, and judge whether the elements contained in the two collections are the same (regardless of order, deduplication)
The example collection experiments contains the following documents:
{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}{"_id":9,"A":[],"B":["red"]}
Determine whether A array and B array contain the same elements:
db.collection('experiments').field('A, B, setEquals(A, B) as sameElements').get()
Results of the:
{"A":["red","blue"],"B":["red","blue"],"sameElements":true}
{"A":["red","blue"],"B":["blue","red","blue"],"sameElements":true}
{"A":["red","blue"],"B":["red","blue","green"],"sameElements":false}
{"A":["red","blue"],"B":["green","red"],"sameElements":false}
{"A":["red","blue"],"B":[],"sameElements":false}
{"A":["red","blue"],"B":[["red"],["blue"]],"sameElements":false}
{"A":["red","blue"],"B":[["red","blue"]],"sameElements":false}
{"A":[],"B":[],"sameElements":true}{"A":[],"B":["red"],"sameElements":false}
# setIntersection
Input two sets, output the intersection of the two sets
The example collection experiments contains the following documents:
{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}{"_id":9,"A":[],"B":["red"]}
Returns an array of elements common to arrays A and B:
db.collection('experiments').field('A, B, setIntersection(A, B) as commonToBoth').get()
Results of the:
{"A":["red","blue"],"B":["red","blue"],"commonToBoth":["blue","red"]}
{"A":["red","blue"],"B":["blue","red","blue"],"commonToBoth":["blue","red"]}
{"A":["red","blue"],"B":["red","blue","green"],"commonToBoth":["blue","red"]}
{"A":["red","blue"],"B":["green","red"],"commonToBoth":["red"]}
{"A":["red","blue"],"B":[],"commonToBoth":[]}
{"A":["red","blue"],"B":[["red"],["blue"]],"commonToBoth":[]}
{"A":["red","blue"],"B":[["red","blue"]],"commonToBoth":[]}
{"A":[],"B":[],"commonToBoth":[]}{"A":[],"B":["red"],"commonToBoth":[]}
# setIsSubset
Given two sets, determine whether the first set is a subset of the second set.
The example collection experiments contains the following documents:
{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}{"_id":9,"A":[],"B":["red"]}
Determine whether the array A is a subset of the array B:
db.collection('experiments').field('A, B, setIsSubset(A, B) as AisSubset').get()
Results of the:
{"A":["red","blue"],"B":["red","blue"],"AisSubset":true}
{"A":["red","blue"],"B":["blue","red","blue"],"AisSubset":true}
{"A":["red","blue"],"B":["red","blue","green"],"AisSubset":true}
{"A":["red","blue"],"B":["green","red"],"AisSubset":false}
{"A":["red","blue"],"B":[],"AisSubset":false}
{"A":["red","blue"],"B":[["red"],["blue"]],"AisSubset":false}
{"A":["red","blue"],"B":[["red","blue"]],"AisSubset":false}
{"A":[],"B":[],"AisSubset":true}{"A":[],"B":["red"],"AisSubset":true}
# setUnion
Input two sets and output the union of the two sets.
The example collection experiments contains the following documents:
{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}{"_id":9,"A":[],"B":["red"]}
Return an array of elements found in the A array or the B array, or both:
db.collection('experiments').field('A, B, setUnion(A, B) as allValues').get()
Results of the:
{"A":["red","blue"],"B":["red","blue"],"allValues":["blue","red"]}
{"A":["red","blue"],"B":["blue","red","blue"],"allValues":["blue","red"]}
{"A":["red","blue"],"B":["red","blue","green"],"allValues":["blue","red","green"]}
{"A":["red","blue"],"B":["green","red"],"allValues":["blue","red","green"]}
{"A":["red","blue"],"B":[],"allValues":["blue","red"]}
{"A":["red","blue"],"B":[["red"],["blue"]],"allValues":["blue","red",["red"],["blue"]]}
{"A":["red","blue"],"B":[["red","blue"]],"allValues":["blue","red",["red","blue"]]}
{"A":[],"B":[],"allValues":[]}{"A":[],"B":["red"],"allValues":["red"]}
# String operation method
String expressions (except concat) have well-defined behavior only for strings of ASCII characters.
The behavior of concat is well-defined independent of the characters used.
# concat
Concatenate any number of strings.
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}
Concatenate the item field and the description field with the "-" delimiter:
db.collection('inventory').field('concat(item, "-", "description") as itemDescription').get()
Results of the:
{"_id":1,"itemDescription":"ABC1 - product 1"}
{"_id":2,"itemDescription":"ABC2 - product 2"}
{"_id":3,"itemDescription":null}
# indexOfBytes
Finds a substring in the target string and returns the UTF-8 byte index (starting from 0) of the first occurrence. Returns -1 if no substring exists
The example collection inventory contains the following documents:
{"_id":1,"item":"foo"}
{"_id":2,"item":"fóofoo"}
{"_id":3,"item":"the foo bar"}
{"_id":4,"item":"hello world fóo"}
{"_id":5,"item":null}{"_id":6,"amount":3}
Retrieve the index where the string foo resides in each item:
db.collection('inventory').field('indexOfBytes(item, "foo") as bytyLocation').get()
Results of the:
{"_id":1,"byteLocation":"0"}
{"_id":2,"byteLocation":"4"}
{"_id":3,"byteLocation":"4"}
{"_id":4,"byteLocation":"-1"}
{"_id":5,"byteLocation":null}
{"_id":6,"byteLocation":null}
# indexOfCP
Finds a substring in the target string and returns the UTF-8 code point index (starting from 0) of the first occurrence. Returns -1 if no substring exists
The example collection inventory contains the following documents:
{"_id":1,"item":"foo"}
{"_id":2,"item":"fóofoo"}
{"_id":3,"item":"the foo bar"}
{"_id":4,"item":"hello world fóo"}
{"_id":5,"item":null}
{"_id":6,"amount":3}
Returns the code point index where the string is located in each item string of foo:
db.collection('inventory').field('indexOfCP(item, "foo") as cpLocation').get()
Results of the:
{"_id":1,"cpLocation":"0"}
{"_id":2,"cpLocation":"3"}
{"_id":3,"cpLocation":"4"}
{"_id":4,"cpLocation":"-1"}
{"_id":5,"cpLocation":null}
{"_id":6,"cpLocation":null}
# split
Splits the array by delimiter and removes the delimiter, returning an array of substrings. If the string cannot be separated by a delimiter, return the original string as the only element of the array
The example collection deliveries contains the following documents:
{"_id":1,"city":"Berkeley, CA","qty":648}
{"_id":2,"city":"Bend, OR","qty":491}
{"_id":3,"city":"Kensington, CA","qty":233}
Create string array city field by splitting, using , as delimiter.
db.collection('deliveries').field('split(city, ", ") as city_state').get()
Results of the:
{"_id":1,"city_state":["Berkeley", "CA"]}
{"_id":2,"city_state":["Bend", "OR"]}
{"_id":3,"city_state":["Kensington", "CA"]}
# strLenBytes
Calculates and returns the number of utf-8 encoded bytes in the specified string.
The example collection food contains the following documents:
{"_id":1,"name":"apple"}
{"_id":2,"name":"banana"}
{"_id":3,"name":"éclair"}
{"_id":4,"name":"hamburger"}
{"_id":5,"name":"jalapeño"}
{"_id":6,"name":"pizza"}
{"_id":7,"name":"tacos"}
{"_id":8,"name":"寿司"}
Compute the length for each name value:
db.collection('food').field('name, strLenBytes(name) as length').get()
Results of the:
{"_id":1,"name":"apple","length":5}
{"_id":2,"name":"banana","length":6}
{"_id":3,"name":"éclair","length":7}
{"_id":4,"name":"hamburger","length":9}
{"_id":5,"name":"jalapeño","length":9}
{"_id":6,"name":"pizza","length":5}
{"_id":7,"name":"tacos","length":5}
{"_id":8,"name":"寿司","length":6}
The documents for _id: 3 and _id: 5 each contain an umlaut (é and ñ, respectively), which requires two bytes to encode.
The document with _id: 8 contains two Japanese characters encoded using three bytes each.
This makes the length greater than the number of characters in the names of documents with _id: 3, _id: 5, and _id: 8.
# strLenCP
Calculates and returns the number of UTF-8 code points for the specified string.
The example collection food contains the following documents:
{"_id":1,"name":"apple"}
{"_id":2,"name":"banana"}
{"_id":3,"name":"éclair"}
{"_id":4,"name":"hamburger"}
{"_id":5,"name":"jalapeño"}
{"_id":6,"name":"pizza"}
{"_id":7,"name":"tacos"}
{"_id":8,"name":"寿司"}
Compute the length for each name value:
db.collection('food').field('name, strLenCP(name) as length').get()
Results of the:
{"_id":1,"name":"apple","length":5}
{"_id":2,"name":"banana","length":6}
{"_id":3,"name":"éclair","length":6}
{"_id":4,"name":"hamburger","length":9}
{"_id":5,"name":"jalapeño","length":8}
{"_id":6,"name":"pizza","length":5}
{"_id":7,"name":"tacos","length":5}
{"_id":8,"name":"寿司","length":2}
# strcasecmp
Compare two strings case-insensitively, and return the result of the comparison
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}
Perform a case-insensitive comparison of the quarter field value with the string "13q4":
db.collection('inventory').field('item, strcasecmp(quarter, "13q4") as comparisonResult').get()
Results of the:
{"_id":1,"item":"ABC1","comparisonResult":-1}
{"_id":2,"item":"ABC2","comparisonResult":0}
{"_id":3,"item":"XYZ1","comparisonResult":1}
# substr
Returns a substring of the specified length of the string starting at the specified position.
Deprecated, recommend using
substrBytesorsubstrCP
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}
Divide quarter into yearSubstring and QuarterSubstring:
db.collection('inventory').field('item, substr(quarter, 0, 2) as yearSubstring, substr(quarter, 2, -1) as quarterSubtring')
Results of the:
{"_id":1,"item":"ABC1","yearSubstring":"13","quarterSubtring":"Q1"}
{"_id":2,"item":"ABC2","yearSubstring":"13","quarterSubtring":"Q4"}
{"_id":3,"item":"XYZ1","yearSubstring":"14","quarterSubtring":"Q2"}
# substrBytes
Returns a substring of the specified length of the string starting at the specified position. The substring starts at the character at the specified UTF-8 byte index in the string and is the specified number of bytes long.
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}
Separate quarter (containing only single-byte US-ASCII characters) into yearSubstring and QuarterSubstring.
The QuarterSubstring field represents the rest of the string from the specified byte index after yearSubstring.
It is calculated by subtracting the byte index from the length of the string using strLenBytes.
db.collection('inventory').field('item, substrBytes(quarter, 0, 2) as yearSubstring, substrBytes(quarter, 2, subtract(strLenBytes(quarter), 2)) as quarterSubtring').get()
Results of the:
{"_id":1,"item":"ABC1","yearSubstring":"13","quarterSubtring":"Q1"}
{"_id":2,"item":"ABC2","yearSubstring":"13","quarterSubtring":"Q4"}
{"_id":3,"item":"XYZ1","yearSubstring":"14","quarterSubtring":"Q2"}
# substrCP
Returns a substring of the specified length of the string starting at the specified position. The substring starts at the character at the specified UTF-8 byte index in the string and is the specified number of bytes long
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}
Split quarter into yearSubstring and QuarterSubstring.
The QuarterSubstring field represents the rest of the string from the specified byte index after yearSubstring.
It is calculated by subtracting the byte index from the length of the string using strLenCP.
db.collection('inventory').field('item, substrCP(quarter, 0, 2) as yearSubstring, substrCP(quarter, 2, subtract(strLenCP(quarter), 2))').get()
Results of the:
{"_id":1,"item":"ABC1","yearSubstring":"13","quarterSubtring":"Q1"}
{"_id":2,"item":"ABC2","yearSubstring":"13","quarterSubtring":"Q4"}
{"_id":3,"item":"XYZ1","yearSubstring":"14","quarterSubtring":"Q2"}
# toLower
Convert a string to lowercase. Accepts a single argument expression.
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1",quarter:"13Q1","description":"PRODUCT 1"}
{"_id":2,"item":"abc2",quarter:"13Q4","description":"Product 2"}
{"_id":3,"item":"xyz1",quarter:"14Q2","description":null}
Return lowercase item and lowercase description values:
db.collection('inventory').field('toLower(item) as item, toLower(description) as description').get()
Results of the:
{"_id":1,"item":"abc1","description":"product 1"}
{"_id":2,"item":"abc2","description":"product 2"}
{"_id":3,"item":"xyz1","description":""}
# toUpper
Convert a string to uppercase. Accepts a single argument expression.
The example collection inventory contains the following documents:
{"_id":1,"item":"ABC1",quarter:"13Q1","description":"PRODUCT 1"}
{"_id":2,"item":"abc2",quarter:"13Q4","description":"Product 2"}
{"_id":3,"item":"xyz1",quarter:"14Q2","description":null}
Return uppercase item and uppercase description values:
db.collection('inventory').field('toUpper(item) as item, toUpper(description) as description').get()
Results of the:
{"_id":1,"item":"ABC1","description":"PRODUCT 1"}
{"_id":2,"item":"ABC2","description":"PRODUCT 2"}
{"_id":3,"item":"XYZ1","description":""}
# Grouping (groupBy) operation method
# addToSet
Adds a value to the array, or does nothing if the value already exists in the array.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:12:00Z"}
Compute the list of unique items sold by each group:
db.collection('sales').groupBy('dayOfYear(date) as day, year(date) as year').groupField('addToSet(item) as itemsSold').get()
Results of the:
{"day":46,"year":2014,"itemsSold":["xyz","abc"]}
{"day":34,"year":2014,"itemsSold":["xyz","jkl"]}
{"day":1,"year":2014,"itemsSold":["abc"]}
# avg
Returns the mean of the data corresponding to the specified expression.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:12:00Z"}
Group documents by the item field and calculate the average and average count for each group.
db.collection('sales').groupBy('item').groupField('avg(multiply(price, quantity)) as avgAmount, avg(quantity) as avgQuantity').get()
Results of the:
{"_id":"xyz","avgAmount":37.5,"avgQuantity":7.5}
{"_id":"jkl","avgAmount":20,"avgQuantity":1}
{"_id":"abc","avgAmount":60,"avgQuantity":6}
# first
Returns the value corresponding to the first record of the specified field in a collection. This operation only makes sense if the set of collections is sorted ( sort ) by some definition.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}
{"_id":6,"item":"xyz","price":5,"quantity":5,"date":"2014-02-15T12:05:10Z"}
{"_id":7,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T14:12:12Z"}
Group by field item and calculate the first sale date for each item:
const $ = db.command.aggregate
db.collection('sales').aggregate().sort({item: 1, date: 1}).group({
_id: "$item",
firstSalesDate: $.first('$date')
}).end()
Results of the:
{"_id":"xyz","firstSalesDate":"2014-02-03T09:05:00Z"}
{"_id":"jkl","firstSalesDate":"2014-02-03T09:00:00Z"}
{"_id":"abc","firstSalesDate":"2014-01-01T08:00:00Z"}
# last
Returns the value corresponding to the last record of the specified field in a collection. This operation only makes sense if the set of collections is sorted ( sort ) by some definition.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","date":"2014-01-01T08:00:00Z","price":10,"quantity":2}
{"_id":2,"item":"jkl","date":"2014-02-03T09:00:00Z","price":20,"quantity":1}
{"_id":3,"item":"xyz","date":"2014-02-03T09:05:00Z","price":5,"quantity":5}
{"_id":4,"item":"abc","date":"2014-02-15T08:00:00Z","price":10,"quantity":10}
{"_id":5,"item":"xyz","date":"2014-02-15T09:05:00Z","price":5,"quantity":10}
{"_id":6,"item":"xyz","date":"2014-02-15T12:05:10Z","price":5,"quantity":5}
{"_id":7,"item":"xyz","date":"2014-02-15T14:12:12Z","price":5,"quantity":10}
Sort the documents by item and date, then group the now sorted documents by the item field and calculate the last sale date for each item:
const $ = db.command.aggregate
db.collection('sales').aggregate().sort({item: 1, date: 1}).group({
_id: "$item",
lastSalesDate: $.last('$date')
}).end()
Results of the:
{"_id":"xyz","lastSalesDate":"2014-02-15T14:12:12Z"}
{"_id":"jkl","lastSalesDate":"2014-02-03T09:00:00Z"}
{"_id":"abc","lastSalesDate":"2014-02-15T08:00:00Z"}
# max
Returns the maximum value of a set of numbers.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}
Group by field item, and calculate the maximum total and maximum number of documents for each group.
db.collection('sales').groupBy('item').groupField('max(multiply(price, quantity)) as maxTotalAmount, max(quantity) as maxQuantity').get()
Results of the:
{"_id":"xyz","maxTotalAmount":50,"maxQuantity":10}
{"_id":"jkl","maxTotalAmount":20,"maxQuantity":1}
{"_id":"abc","maxTotalAmount":100,"maxQuantity":10}
# min
Returns the minimum value of a set of numbers.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}
Groups documents by the item field and computes the minimum and minimum counts for each grouping.
db.collection('sales').groupBy('item').groupField('min(quantity) as minQuantity').get()
Results of the:
{"item":"xyz","minQuantity":5}
{"item":"jkl","minQuantity":1}
{"item":"abc","minQuantity":2}
# push
Returns an array composed of the column specified by the expression and the corresponding value in a group.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}
{"_id":6,"item":"xyz","price":5,"quantity":5,"date":"2014-02-15T12:05:10Z"}
{"_id":7,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T14:12:12Z"}
Group by the date and year of the date field and calculate the BOM and sales quantity for each group:
db.collection('sales').groupBy('dayOfYear(date) as day, year(date) as year').groupField('push({item: item, quantity: quantity}) as itemsSold').get()
Results of the:
{"day":46,"year":2014,"itemsSold":[{"item":"abc","quantity":10},{"item":"xyz","quantity":10},{"item":"xyz","quantity":5},{"item":"xyz","quantity":10}]}
{"day":34,"year":2014,"itemsSold":[{"item":"jkl","quantity":1},{"item":"xyz","quantity":5}]}
{"day":1,"year":2014,"itemsSold":[{"item":"abc","quantity":2}]}
# stdDevPop
Returns the standard deviation of the corresponding values for a set of fields.
The example collection users contains the following documents:
{"_id":1,"name":"dave123","quiz":1,"score":85}
{"_id":2,"name":"dave2","quiz":1,"score":90}
{"_id":3,"name":"ahn","quiz":1,"score":71}
{"_id":4,"name":"li","quiz":2,"score":96}
{"_id":5,"name":"annT","quiz":2,"score":77}
{"_id":6,"name":"ty","quiz":2,"score":82}
Compute the standard deviation for each test:
db.collection('users').groupBy('quiz').groupField('stdDevPop(score) as stdDev').get()
Results of the:
{"quiz":2,"stdDev":8.04155872120988}
{"quiz":1,"stdDev":8.04155872120988}
# stdDevSamp
Computes the sample standard deviation of the input values.
The example collection users contains the following documents:
{_id:0,username:"user0",age:20}
{_id:1,username:"user1",age:42}
{_id:2,username:"user2",age:28}
...
To calculate the standard deviation of a sample of users, first use the sample pipeline to sample 100 users, then use stdDevSamp to calculate the standard deviation of the sample users.
db.collection('users').aggregate()
.sample({size: 100})
.group({
_id: null,
ageStdDev:{$stdDevSamp:"$age"}
})
.end()
The operation returns the following results:
{"_id":null,"ageStdDev":7.811258386185771}
# sum
Returns the sum of all values for a set of fields.
The example collection sales contains the following documents:
{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}
Group documents by the date and year of the date field and calculate the total and count of each group of documents.
db.collection('sales').groupBy('dayOfYear(date) as day, year(date) as year').groupField('sum(multiply(price, quantity)) as totalAmount, sum(1) as count').get()
Results of the:
{"_id":{"day":46,"year":2014},"totalAmount":150,"count":2}
{"_id":{"day":34,"year":2014},"totalAmount":45,"count":2}
{"_id":{"day":1,"year":2014},"totalAmount":20,"count":1}
# mergeObjects
Merge multiple documents into one.
Note: If the documents to be merged contain the same field names, the fields in the resulting document will override the values in the last merged document.
The example collection sales contains the following documents:
{_id:1,year:2017,item:"A",quantity:{"2017Q1":500,"2017Q2":500}}
{_id:2,year:2016,item:"A",quantity:{"2016Q1":400,"2016Q2":300,"2016Q3":0,"2016Q4":0}}
{_id:3,year:2017,item:"B",quantity:{"2017Q1":300}}
{_id:4,year:2016,item:"B",quantity:{"2016Q3":100,"2016Q4":250}}
Group by item, mergeObjects only accepts single object operations.
db.collection('sales').groupBy('item').groupField('mergeObjects(quantity) as mergedSales').get()
Results of the:
{"_id":"B","mergedSales":{"2017Q1":300,"2016Q3":100,"2016Q4":250}}
{"_id":"A","mergedSales":{"2017Q1":500,"2017Q2":500,"2016Q1":400,"2016Q2":300,"2016Q3":0,"2016Q4":0}}
# Mall example
Schema
// Product table goods
{
"name": {
"bsonType": "string",
"description": "商品名称"
},
"category": {
"bsonType": "string",
"description": "商品分类"
},
"price": {
"bsonType": "number",
"description": "商品价格"
},
"stock": {
"bsonType": "number",
"description": "库存"
}
}
// order table orders
{
"goodsId": {
"bsonType": "string",
"description": "商品ID",
"foreignKey": "goods._id"
},
"buyNum": {
"bsonType": "number",
"description": "购买数量"
},
"userId": {
"bsonType": "string",
"description": "用户ID",
"foreignKey": "users._id"
},
"createDate": {
"bsonType": "timestamp",
"description": "购买时间"
}
}
// user table users
{
"name": {
"bsonType": "string",
"description": "用户名"
}
}
sample data
// goods
[
{
"_id": "6363666826e8170001b62baa",
"category": "手机",
"name": "iPhone 14 512G",
"price": 8488,
"stock": 100
},
{
"_id": "6363666f3aeb530001925bb6",
"category": "手机",
"name": "iPhone 13 Pro Max 128G",
"price": 9688,
"stock": 20
},
{
"_id": "63636678a899370001a22627",
"category": "手机",
"name": "华为 Meta P50",
"price": 7699,
"stock": 50
},
{
"_id": "63636681ff2a54000133afd7",
"category": "手机",
"name": "小米 12 Ultra",
"price": 4599,
"stock": 200
},
{
"_id": "6363668cd69bc10001765c5f",
"category": "电脑",
"name": "红米 Redmibook 15寸 256G",
"price": 3999,
"stock": 100
},
{
"_id": "6363669353a7f30001b46c84",
"category": "电脑",
"name": "Macbook Pro 512G",
"price": 10499,
"stock": 100
},
{
"_id": "6363669a26e8170001b63213",
"category": "电脑",
"name": "Macbook Air 128G",
"price": 7699,
"stock": 100
}
]
// orders
[
{
"_id": "6364b2e8b653d6000163c4f3",
"buyNum": 2,
"createDate": 1664769600000,
"goodsId": "6363666826e8170001b62baa",
"userId": "6364cc82b653d6000167f848"
},
{
"_id": "6364b2e8b653d6000163c4f4",
"buyNum": 1,
"createDate": 1664773200000,
"goodsId": "6363668cd69bc10001765c5f",
"userId": "6364cc82b653d6000167f848"
},
{
"_id": "6364b2e8b653d6000163c4f5",
"buyNum": 2,
"createDate": 1664758800000,
"goodsId": "6363669353a7f30001b46c84",
"userId": "6364cc82b653d6000167f849"
},
{
"_id": "6364b2e8b653d6000163c4f6",
"buyNum": 1,
"createDate": 1664769600000,
"goodsId": "63636678a899370001a22627",
"userId": "6364cc82b653d6000167f848"
},
{
"_id": "6364b2e8b653d6000163c4f7",
"buyNum": 3,
"createDate": 1664769600000,
"goodsId": "63636681ff2a54000133afd7",
"userId": "6364cc82b653d6000167f848"
},
{
"_id": "6364b2e8b653d6000163c4f8",
"buyNum": 1,
"createDate": 1664769600000,
"goodsId": "6363668cd69bc10001765c5f",
"userId": "6364cc82b653d6000167f849"
},
{
"_id": "6364b2e8b653d6000163c4f9",
"buyNum": 3,
"createDate": 1664683200000,
"goodsId": "6363669353a7f30001b46c84",
"userId": "6364cc82b653d6000167f848"
},
{
"_id": "6364b2e8b653d6000163c4fa",
"buyNum": 2,
"createDate": 1664679600000,
"goodsId": "6363666f3aeb530001925bb6",
"userId": "6364cc82b653d6000167f848"
},
{
"_id": "6364b2e8b653d6000163c4fb",
"buyNum": 1,
"createDate": 1664697600000,
"goodsId": "63636678a899370001a22627",
"userId": "6364cc82b653d6000167f849"
}
]
// users
[
{
"_id": "6364cc82b653d6000167f847",
"name": "张三"
},
{
"_id": "6364cc82b653d6000167f848",
"name": "李四"
},
{
"_id": "6364cc82b653d6000167f849",
"name": "王五"
}
]
# Count the quantity and inventory of commodities according to the classification of commodities
In this example, group and groupField are used, corresponding to the $group and $project stages of the Aggregate stage.
Use groupBy to group the product classification category field, use the sum expression in the groupField to add 1 to the product quantity (goodsNumber), and also use the sum expression to calculate the total inventory of the stock (stock).
// JQL
db.collection('goods').groupBy('category').groupField('sum(1) as goodsNumber, sum(stock) as stock').get()
Compared with the above JQL method, the following Aggregate is slightly more complicated, but the general logic is the same.
In the first stage of Aggregate, group according to product classification, and add the goodsNumber and stock fields in the group. The calculation is the same as the above calculation method, and the result is passed to the next stage.
In the second stage, use the $project pipeline to trim the fields, remove the _id field, add the category field, and keep the goodsNumber and stock fields. If you use the JQL method, you don’t need to manually trim the fields.
// JQL Aggregate
const $ = db.command.aggregate
db.collection('goods').aggregate().group({
_id: "$category",
goodsNumber: $.sum(1),
stock: $.sum('$stock')
}).project({
_id: false,
category: "$_id",
goodsNumber: 1,
stock: 1
}).end()
Results of the
[
{
"category": "手机",
"goodsNumber": 4,
"stock": 370
},
{
"category": "电脑",
"goodsNumber": 3,
"stock": 300
}
]
# Query the number of products whose price exceeds 5,000 yuan under each category
Use where to preliminarily screen products whose value is greater than 5000, use groupBy to group the filtered products according to category, and use sum expression in groupField to add 1 to the number of products (goodsNumber).
// JQL
db.collection('goods').where('price > 5000').groupBy('category').groupField('sum(1) as goodsNumber').get()
In stage 1, use the $match pipeline to filter by item amount, and pass the result to the next stage.
In the second stage, the $group pipeline is used to group by category, and the goodsNumber field is added to the group, and the sum expression is used to add 1 to the quantity.
In the third stage, use the $project pipeline to trim the fields, remove the _id field, add the category field value to the product category passed in the previous stage, and keep the goodsNumber field.
// JQL Aggregate
db.collection('goods').aggregate().match({
price: $.gt(5000)
}).group({
_id: '$category',
goodsNumber: $.sum(1)
}).project({
_id: 0,
category: "$_id",
goodsNumber: 1
}).end()
Results of the
[
{
"category": "手机",
"goodsNumber": 3
},
{
"category": "电脑",
"goodsNumber": 2
}
]
# Query the highest priced item and the lowest item
The first stage uses the $sort pipeline to sort the goods table by price in reverse, passing the result to the next stage.
In the second stage, the $group pipeline is used to group by category. Since the price has been arranged in reverse order in the first stage, the maxGoodsName and maxGoodsPrice fields are added to the group using the $first expression to select the first document and the one with the largest price Indicates the item with the highest price. Conversely, the minGoodsName, minGoodsPrice fields use the $last expression to select the last document, which represents the item with the lowest price.
In the third stage, use the $project pipeline to trim the data, remove the _id field, add the category field value to the product category passed in the previous stage, maxGoods is the product with the highest price, and minGoods is the product with the lowest price.
// JQL Aggregate
db.collection('goods').aggregate().sort({
price: -1
}).group({
_id: "$category",
maxGoodsName: {$first: "$name"},
maxGoodsPrice: {$first: "$price"},
minGoodsName: {$last: "$name"},
minGoodsPrice: {$last: "$price"}
}).project({
_id: 0,
category: "$_id",
maxGoods: {
name: "$maxGoodsName",
price: "$maxGoodsPrice"
},
minGoods: {
name: "$minGoodsName",
price: "$minGoodsPrice"
}
}).end()
Results of the
[
{
"category": "电脑",
"maxGoods": {
"name": "Macbook Pro 512G",
"price": 10499
},
"minGoods": {
"name": "红米 Redmibook 15寸 256G",
"price": 3999
}
},
{
"category": "手机",
"maxGoods": {
"name": "iPhone 13 Pro Max 128G",
"price": 9688
},
"minGoods": {
"name": "小米 12 Ultra",
"price": 4599
}
}
]
# Query the average customer unit price of a certain day
The first stage uses the $match pipeline to filter orders for a certain day and pass the results to the next stage
The second stage uses the $group pipeline to calculate the average customer unit price using the avg expression
// JQL Aggregate
const $ = db.command.aggregate
db.collection('orders').aggregate().match({
createDate: $.and($.gt(new Date('2022-10-03T00:00:00.000Z')), $.lt(new Date('2022-10-04T00:00:00.000Z')))
}).group({
_id: null,
avgPrice: $.avg('$price')
}).end()
Results of the
[
{
"_id": null,
"avgPrice": 11364
}
]
# Query the most popular products
// JQL
const orders = db.collection('orders').getTemp()
db.collection(orders, 'goods')
.foreignKey('orders.goodsId')
.field('arrayElemAt(goodsId, 0) as goods, buyNum')
.groupBy('goods._id as goodsId')
.groupField('sum(buyNum) as count, first(goods.name) as name, first(goods.category) as category')
.orderBy('count desc')
.get()
// JQL Aggregate
db.collection('orders').aggregate().lookup({
from: "goods",
let: {
goodsId: "$goodsId"
},
pipeline: $.pipeline()
.match(db.command.expr(
db.command.eq(['$_id', '$$goodsId'])
)).done(),
as: "goods"
}).unwind('$goods').group({
_id: "$goods._id",
count: $.sum("$buyNum"),
name: $.first("$goods.name"),
category: $.first("$goods.category")
}).sort({
count: -1
}).end()
Results of the
[
{
"_id": "6363669353a7f30001b46c84",
"category": "电脑",
"count": 5,
"name": "Macbook Pro 512G"
},
{
"_id": "63636681ff2a54000133afd7",
"category": "手机",
"count": 3,
"name": "小米 12 Ultra"
},
{
"_id": "63636678a899370001a22627",
"category": "手机",
"count": 2,
"name": "华为 Meta P50"
},
{
"_id": "6363668cd69bc10001765c5f",
"category": "电脑",
"count": 2,
"name": "红米 Redmibook 15寸 256G"
},
{
"_id": "6363666826e8170001b62baa",
"category": "手机",
"count": 2,
"name": "iPhone 14 512G"
},
{
"_id": "6363666f3aeb530001925bb6",
"category": "手机",
"count": 2,
"name": "iPhone 13 Pro Max 128G"
}
]
# Statistics of users who purchased mobile phones
// JQL
const orders = db.collection('orders').getTemp()
db.collection(orders, 'goods', 'users')
.where('goodsId.category=="手机"')
.field('arrayElemAt(goodsId, 0) as goods, arrayElemAt(userId, 0) as user')
.groupBy('goods.category as category')
.groupField('addToSet(user) as users')
.get()
// Aggregate
db.collection('orders').aggregate().lookup({
from: "goods",
let: {
goodsId: "$goodsId"
},
pipeline: $.pipeline().match(db.command.expr(
db.command.eq(['$_id', '$$goodsId'])
)).done(),
as: "goods"
}).match({
'goods.category': "手机"
}).unwind("$goods").lookup({
from: "users",
let: {
userId: "$userId"
},
pipeline: $.pipeline().match(db.command.expr(
db.command.eq(['$_id', '$$userId'])
)).done(),
as: "user"
}).unwind('$user').group({
_id: "$goods.category",
users: {$addToSet: "$user"}
}).end()
Results of the
[
{
"category": "手机",
"users": [
{
"_id": "6364cc82b653d6000167f849",
"name": "王五"
},
{
"_id": "6364cc82b653d6000167f848",
"name": "李四"
}
]
}
]