English
English
# Cloud database operation method
equivalent to mongoDB aggregate operator concept
# Arithmetic operators
# abs
Returns the absolute value of a number.
# API Description
The syntax is as follows:
db.command.aggregate.abs(<number>)
In addition to numeric constants, the value passed in by abs can also be any expression that eventually resolves to a number.
If the expression resolves to null or points to a field that does not exist, the result of abs is null. If the value resolves to NaN, the result is NaN.
# Sample code
Suppose the collection ratings has the following records:
{ _id: 1, start: 5, end: 8 }
{ _id: 2, start: 4, end: 4 }
{ _id: 3, start: 9, end: 7 }
{ _id: 4, start: 6, end: 7 }
···
The absolute difference between start and end for each record can be found as follows:
const $ = db.command.aggregate
let res = await db.collection('ratings').aggregate()
.project({
delta: $.abs($.subtract(['$start', '$end']))
})
.end()
The returned results are as follows:
{ "_id" : 1, "delta" : 3 }
{ "_id" : 2, "delta" : 0 }
{ "_id" : 3, "delta" : 2 }
{ "_id" : 4, "delta" : 1 }
# add
Add numbers or add numbers to dates. If one of the values in the array is a date, the other values are treated as milliseconds added to that date.
# API Description
The syntax is as follows:
db.command.aggregate.add([<表达式1>, <表达式2>, ...])
The expression can be in the form of $ + specified field, or it can be a normal string. As long as it can be parsed into a string.
# Sample code
Suppose the collection staff has the following records:
{ _id: 1, department: "x", sales: 5, engineer: 10, lastUpdate: ISODate("2019-05-01T00:00:00Z") }
{ _id: 2, department: "y", sales: 10, engineer: 20, lastUpdate: ISODate("2019-05-01T02:00:00Z") }
{ _id: 3, department: "z", sales: 20, engineer: 5, lastUpdate: ISODate("2019-05-02T03:00:00Z") }
SUM OF NUMBERS
The total number of people in each record can be obtained as follows:
const $ = db.command.aggregate
let res = await db.collection('staff').aggregate()
.project({
department: 1,
total: $.add(['$sales', '$engineer'])
})
.end()
The returned results are as follows:
{ _id: 1, department: "x", total: 15 }
{ _id: 2, department: "y", total: 30 }
{ _id: 3, department: "z", total: 25 }
Increase date value
The following operation can obtain the value of lastUpdate plus one hour later for each record:
const $ = db.command.aggregate
let res = await db.collection('staff').aggregate()
.project({
department: 1,
lastUpdate: $.add(['$lastUpdate', 60*60*1000])
})
.end()
The returned results are as follows:
{ _id: 1, department: "x", lastUpdate: ISODate("2019-05-01T01:00:00Z") }
{ _id: 2, department: "y", lastUpdate: ISODate("2019-05-01T03:00:00Z") }
{ _id: 3, department: "z", lastUpdate: ISODate("2019-05-02T04:00:00Z") }
# ceil
Round up, returns the smallest integer greater than or equal to the given number.
# API Description
The syntax is as follows:
db.command.aggregate.ceil(<number>)
<number> can be any expression that resolves to a number. Returns null if the expression resolves to null or points to a field that does not exist, or NaN if it resolves to NaN.
# Sample code
Suppose the collection sales has the following records:
{ _id: 1, sales: 5.2 }
{ _id: 2, sales: 1.32 }
{ _id: 3, sales: -3.2 }
Each number can be rounded up as follows:
const $ = db.command.aggregate
let res = await db.collection('sales').aggregate()
.project({
sales: $.ceil('$sales')
})
.end()
The returned results are as follows:
{ _id: 1, sales: 6 }
{ _id: 2, sales: 2 }
{ _id: 3, sales: -3 }
# divide
Pass in the dividend and the divisor, and find the quotient.
# API Description
The syntax is as follows:
db.command.aggregate.divide([<被除数表达式>, <除数表达式>])
An expression can be any expression that resolves to a number.
# Sample code
Suppose the collection railroads has the following records:
{ _id: 1, meters: 5300 }
{ _id: 2, meters: 64000 }
{ _id: 3, meters: 130 }
You can take the values after converting each number to kilometers as follows:
const $ = db.command.aggregate
let res = await db.collection('railroads').aggregate()
.project({
km: $.divide(['$meters', 1000])
})
.end()
The returned results are as follows:
{ _id: 1, km: 5.3 }
{ _id: 2, km: 64 }
{ _id: 3, km: 0.13 }
# exp
Take e (the base of the natural logarithm, Euler's number) to the nth power.
# API Description
The syntax is as follows:
db.command.aggregate.exp(<exponent>)
<exponent> can be any expression that resolves to a number. Returns null if the expression resolves to null or points to a field that does not exist, or NaN if it resolves to NaN.
# Sample code
Suppose the set math has the following records:
{ _id: 1, exp: 0 }
{ _id: 2, exp: 1 }
{ _id: 3, exp: 2 }
const $ = db.command.aggregate
let res = await db.collection('math').aggregate()
.project({
result: $.exp('$exp')
})
.end()
The returned results are as follows:
{ _id: 1, result: 1 }
{ _id: 2, result: 2.71828182845905 }
{ _id: 3, result: 7.38905609893065 }
# floor
Round down, returns the smallest integer greater than or equal to the given number.
# API Description
The syntax is as follows:
db.command.aggregate.floor(<number>)
<number> can be any expression that resolves to a number. Returns null if the expression resolves to null or points to a field that does not exist, or NaN if it resolves to NaN.
# Sample code
Suppose the collection sales has the following records:
{ _id: 1, sales: 5.2 }
{ _id: 2, sales: 1.32 }
{ _id: 3, sales: -3.2 }
Each number can be rounded down as follows:
const $ = db.command.aggregate
let res = await db.collection('sales').aggregate()
.project({
sales: $.floor('$sales')
})
.end()
The returned results are as follows:
{ _id: 1, sales: 5 }
{ _id: 2, sales: 1 }
{ _id: 3, sales: -4 }
# ln
Calculates the natural logarithm of the given number.
# API Description
The syntax is as follows:
db.command.aggregate.ln(<number>)
<number> can be any expression that resolves to a non-negative number.
ln is equivalent to log([<number>, Math.E]), where Math.E is the JavaScript method for getting the value of e.
# Sample code
Suppose the collection curve has the following records:
{ _id: 1, x: 1 }
{ _id: 2, x: 2 }
{ _id: 3, x: 3 }
Compute the value of ln(x):
const $ = db.command.aggregate
let res = await db.collection('curve').aggregate()
.project({
log: $.ln('$x')
})
.end()
The returned results are as follows:
{ _id: 1, ln: 0 }
{ _id: 2, ln: 0.6931471805599453 }
{ _id: 3, ln: 1.0986122886681098 }
# log
Calculates the log value of the given number under the given logarithm.
# API Description
The syntax is as follows:
db.command.aggregate.log([<number>, <base>])
<number> can be any expression that resolves to a non-negative number. <base> can be any expression that resolves to a number greater than 1.
log returns null if either argument resolves to null or points to any non-existent field. If either argument resolves to NaN, log returns NaN.
# Sample code
Suppose the collection curve has the following records:
{ _id: 1, x: 1 }
{ _id: 2, x: 2 }
{ _id: 3, x: 3 }
Compute the value of log2(x):
const $ = db.command.aggregate
let res = await db.collection('curve').aggregate()
.project({
log: $.log(['$x', 2])
})
.end()
The returned results are as follows:
{ _id: 1, log: 0 }
{ _id: 2, log: 1 }
{ _id: 3, log: 1.58496250072 }
# log10
Calculates the log value of the given number in log base 10.
# API Description
The syntax is as follows:
db.command.aggregate.log(<number>)
<number> can be any expression that resolves to a non-negative number.
log10 is equivalent to the log method where the second parameter is fixed to 10.
# Sample code
# db.command.aggregate.log10
Calculates the log value of the given number at log base 10.
The syntax is as follows:
db.command.aggregate.log(<number>)
<number> can be any expression that resolves to a non-negative number.
log10 is equivalent to the log method where the second parameter is fixed to 10.
# mod
The modulo operation takes the modulo value of the number.
# API Description
The syntax is as follows:
db.command.aggregate.mod([<dividend>, <divisor>])
The first number is the dividend and the second number is the divisor. The argument can be any expression that resolves to a number.
# Sample code
Suppose the collection shopping has the following records:
{ _id: 1, bags: 3, items: 5 }
{ _id: 2, bags: 2, items: 8 }
{ _id: 3, bags: 5, items: 16 }
Each record takes the remainder of dividing items by bags (items % bags):
const $ = db.command.aggregate
let res = await db.collection('shopping').aggregate()
.project({
overflow: $.mod(['$items', '$bags'])
})
.end()
The returned results are as follows:
{ _id: 1, overflow: 2 }
{ _id: 2, overflow: 0 }
{ _id: 3, overflow: 1 }
# multiply
Takes the result of multiplying the passed numeric arguments.
# API Description
The syntax is as follows:
db.command.aggregate.multiply([<expression1>, <expression2>, ...])
The argument can be any expression that resolves to a number.
# Sample code
Suppose the collection fruits has the following records:
{ "_id": 1, "name": "apple", "price": 10, "quantity": 100 }
{ "_id": 2, "name": "orange", "price": 15, "quantity": 50 }
{ "_id": 3, "name": "lemon", "price": 5, "quantity": 20 }
Find the total value of each fruit:
const $ = db.command.aggregate
let res = await db.collection('fruits').aggregate()
.project({
name: 1,
total: $.multiply(['$price', '$quantity']),
})
.end()
The returned results are as follows:
{ "_id": 1, "name": "apple", "total": 1000 }
{ "_id": 2, "name": "orange", "total": 750 }
{ "_id": 3, "name": "lemo", "total": 100 }
# pow
Raises the exponential power of a given base.
# API Description
The syntax is as follows:
db.command.aggregate.pow([<base>, <exponent>])
The argument can be any expression that resolves to a number.
# Sample code
Suppose the collection stats has the following records:
{ "_id": 1, "x": 2, "y": 3 }
{ "_id": 2, "x": 5, "y": 7 }
{ "_id": 3, "x": 10, "y": 20 }
Find the sum of the squares of x and y:
const $ = db.command.aggregate
let res = await db.collection('stats').aggregate()
.project({
sumOfSquares: $.add([$.pow(['$x', 2]), $.pow(['$y', 2])]),
})
.end()
The returned results are as follows:
{ "_id": 1, "sumOfSquares": 13 }
{ "_id": 2, "sumOfSquares": 74 }
{ "_id": 3, "sumOfSquares": 500 }
# sqrt
Find the square root.
# API Description
The syntax is as follows:
db.command.aggregate.sqrt([<number>])
The argument can be any expression that resolves to a non-negative number.
# Sample code
Suppose the collection of right triangles triangle has the following records:
{ "_id": 1, "x": 2, "y": 3 }
{ "_id": 2, "x": 5, "y": 7 }
{ "_id": 3, "x": 10, "y": 20 }
Assuming x and y are two right-angled sides, find the length of the hypotenuse:
const $ = db.command.aggregate
let res = await db.collection('triangle').aggregate()
.project({
len: $.sqrt([$.add([$.pow(['$x', 2]), $.pow(['$y', 2])])]),
})
.end()
The returned results are as follows:
{ "_id": 1, "len": 3.605551275463989 }
{ "_id": 2, "len": 8.602325267042627 }
{ "_id": 3, "len": 22.360679774997898 }
# subtract
Subtracts two numbers and returns the difference, or subtracts two dates and returns the milliseconds difference, or subtracts a number from a date and returns the date of the result.
# API Description
The syntax is as follows:
db.command.aggregate.subtract([<expression1>, <expression2>])
The argument can be any expression that resolves to a number or date.
# Sample code
Suppose the collection scores has the following records:
{ "_id": 1, "max": 10, "min": 1 }
{ "_id": 2, "max": 7, "min": 5 }
{ "_id": 3, "max": 6, "min": 6 }
Find the difference between max and min for each record. :
const $ = db.command.aggregate
let res = await db.collection('scores').aggregate()
.project({
diff: $.subtract(['$max', '$min'])
})
.end()
The returned results are as follows:
{ "_id": 1, "diff": 9 }
{ "_id": 2, "diff": 2 }
{ "_id": 3, "diff": 0 }
# trunc
Truncate numbers to integers.
# API Description
The syntax is as follows:
db.command.aggregate.trunc(<number>)
The argument can be any expression that resolves to a number.
# Sample code
Suppose the collection scores has the following records:
{ "_id": 1, "value": 1.21 }
{ "_id": 2, "value": 3.83 }
{ "_id": 3, "value": -4.94 }
const $ = db.command.aggregate
let res = await db.collection('scores').aggregate()
.project({
int: $.trunc('$value')
})
.end()
The returned results are as follows:
{ "_id": 1, "value": 1 }
{ "_id": 2, "value": 3 }
{ "_id": 3, "value": -4 }
# Array operators
# arrayElemAt
Returns the element at the index of the specified array.
# API Description
The syntax is as follows:
db.command.aggregate.arrayElemAt([<array>, <index>])
<array> can be any expression that resolves to an array.
<index> can be any expression that resolves to an integer. If positive, arrayElemAt returns the element at index position, if negative, arrayElemAt returns the element at index position from the end of the array.
# Sample code
Suppose the collection exams has the following records:
{ "_id": 1, "scores": [80, 60, 65, 90] }
{ "_id": 2, "scores": [78] }
{ "_id": 3, "scores": [95, 88, 92] }
Find the sum of the scores for each first test and the final score:
const $ = db.command.aggregate
let res = await db.collection('exams').aggregate()
.project({
first: $.arrayElemAt(['$scores', 0]),
last: $.arrayElemAt(['$scores', -1]),
})
.end()
The returned results are as follows:
{ "_id": 1, "first": 80, "last": 90 }
{ "_id": 2, "first": 78, "last": 78 }
{ "_id": 3, "first": 95, "last": 92 }
# arrayToObject
Convert an array to an object.
# API Description
There are two syntaxes:
The first type: pass in a two-dimensional array, the length of the second dimension must be 2, the first value is the field name, and the second value is the field value
db.command.aggregate.arrayToObject([
[<key1>, <value1>],
[<key2>, <value2>],
...
])
The second: pass in an array of objects, each object must contain the fields k and v, specifying the field name and field value respectively
db.command.aggregate.arrayToObject([
{ "k": <key1>, "v": <value1> },
{ "k": <key2>, "v": <value2> },
...
])
Arguments passed to arrayToObject can be resolved to one of the above two notations.
# Sample code
Suppose the collection shops has the following records:
{ "_id": 1, "sales": [ ["max", 100], ["min", 50] ] }
{ "_id": 2, "sales": [ ["max", 70], ["min", 60] ] }
{ "_id": 3, "sales": [ { "k": "max", "v": 50 }, { "k": "min", "v": 30 } ] }
Convert an array to an object:
const $ = db.command.aggregate
let res = await db.collection('shops').aggregate()
.project({
sales: $.arrayToObject('$sales'),
})
.end()
The returned results are as follows:
{ "_id": 1, "sales": { "max": 100, "min": 50 } }
{ "_id": 2, "sales": { "max": 70, "min": 60 } }
{ "_id": 3, "sales": { "max": 50, "min": 30 } }
# concatArrays
Concatenates multiple arrays into one array.
# API Description
The syntax is as follows:
db.command.aggregate.concatArrays([ <array1>, <array2>, ... ])
The argument can be any expression that resolves to an array.
# Sample code
Suppose the collection items has the following records:
{ "_id": 1, "fruits": [ "apple" ], "vegetables": [ "carrot" ] }
{ "_id": 2, "fruits": [ "orange", "lemon" ], "vegetables": [ "cabbage" ] }
{ "_id": 3, "fruits": [ "strawberry" ], "vegetables": [ "spinach" ] }
const $ = db.command.aggregate
let res = await db.collection('items').aggregate()
.project({
list: $.concatArrays(['$fruits', '$vegetables']),
})
.end()
The returned results are as follows:
{ "_id": 1, "list": [ "apple", "carrot" ] }
{ "_id": 2, "list": [ "orange", "lemon", "cabbage" ] }
{ "_id": 3, "list": [ "strawberry", "spinach" ] }
# filter
Returns a subset of an array that satisfies the condition based on the given condition.
# API Description
The syntax is as follows:
db.command.aggregate.filter({
input: <array>,
as: <string>,
cond: <expression>
})
| Fields | Description |
|---|---|
| input | An expression that can be parsed as an array |
| as | optional, a variable used to represent each element of the array, the default is thYes |
| cond | An expression that can be parsed as a boolean value to determine whether each element satisfies the condition, the name of each element is determined by the as parameter (the parameter name needs to be prefixed with $$, such as $$this) |
The argument can be any expression that resolves to an array.
# Sample code
Suppose the collection fruits has the following records:
{
"_id": 1,
"stock": [
{ "name": "apple", "price": 10 },
{ "name": "orange", "price": 20 }
],
}
{
"_id": 2,
"stock": [
{ "name": "lemon", "price": 15 },
],
}
const _ = db.command
const $ = db.command.aggregate
let res = await db.collection('fruits').aggregate()
.project({
stock: $.filter({
input: '$stock',
as: 'item',
cond: $.gte(['$$item.price', 15])
})
})
.end()
The returned results are as follows:
{ "_id": 1, "stock": [ { "name": "orange", "price": 20} ] }
{ "_id": 2, "stock": [ { "name": "lemon", "price": 15 } ] }
# in
Given a value and an array, return true if the value is in the array, false otherwise.
# API Description
The syntax is as follows:
db.command.aggregate.in([<value>, <array>])
<value> can be any expression.
<array> can be any expression that resolves to an array.
# Sample code
Suppose the collection shops has the following records:
{ "_id": 1, "topsellers": ["bread", "ice cream", "butter"] }
{ "_id": 2, "topsellers": ["ice cream", "cheese", "yagurt"] }
{ "_id": 3, "topsellers": ["croissant", "cucumber", "coconut"] }
Mark the record for the highest-selling item containing ice cream.
const $ = db.command.aggregate
let res = await db.collection('price').aggregate()
.project({
included: $.in(['ice cream', '$topsellers'])
})
.end()
The returned results are as follows:
{ "_id": 1, "included": true }
{ "_id": 2, "included": true }
{ "_id": 3, "included": false }
# indexOfArray
Finds the index of the first element in the array that is equal to the given value, or returns -1 if not found.
# API Description
The syntax is as follows:
db.command.aggregate.indexOfArray([ <array expression>, <search expression>, <start>, <end> ])
| Field | Type | Description |
|---|---|---|
| - | string | An expression that resolves to an array, if it resolves to null, indexOfArray returns null |
| - | string | Conditional match expression applied to each element of the data |
| - | integer | optional, used to specify the starting index of the search, must be a non-negative integer |
| - | integer | is optional, it is used to specify the end index of the search, it must be a non-negative integer, and should also be specified when is specified, otherwise the default is used as |
The argument can be any expression that resolves to an array.
# Sample code
Suppose the collection stats has the following records:
{
"_id": 1,
"sales": [ 1, 6, 2, 2, 5 ]
}
{
"_id": 2,
"sales": [ 4, 2, 1, 5, 2 ]
}
{
"_id": 3,
"sales": [ 2, 5, 3, 3, 1 ]
}
const $ = db.command.aggregate
let res = await db.collection('stats').aggregate()
.project({
index: $.indexOfArray(['$sales', 2, 2])
})
.end()
The returned results are as follows:
{ "_id": 1, "index": 2 }
{ "_id": 2, "index": 4 }
{ "_id": 3, "index": -1 }
# isArray
Checks whether the given expression is an array, returning a boolean value.
# API Description
The syntax is as follows:
db.command.aggregate.isArray(<expression>)
Arguments can be arbitrary expressions.
# Sample code
Suppose the collection stats has the following records:
{
"_id": 1,
"base": 10,
"sales": [ 1, 6, 2, 2, 5 ]
}
{
"_id": 2,
"base": 1,
"sales": 100
}
Calculates the total sales, taking sales * base if sales is a number, or the product of the sum of the elements of the array and base if sales is an array.
const $ = db.command.aggregate
let res = await db.collection('stats').aggregate()
.project({
sum: $.cond({
if: $.isArray('$sales'),
then: $.multiply([$.sum(['$sales']), '$base']),
else: $.multiply(['$sales', '$base']),
})
})
.end()
The returned results are as follows:
{ "_id": 1, "sum": 160 }
{ "_id": 2, "sum": 100 }
# map
Similar to the map method on JavaScript Array, converts each element of the given array according to the given conversion method to obtain a new array.
# API Description
The syntax is as follows:
db.command.aggregate.map({
input: <expression>,
as: <string>,
in: <expression>
})
| Fields | Description |
|---|---|
| input | An expression that can be parsed as an array |
| as | optional, a variable used to represent each element of the array, the default is thYes |
| in | an expression that can be applied to each element of a given array, the name of each element is determined by the as parameter (the parameter name needs to be prefixed with $$, such as $$this) |
# Sample code
Suppose the collection stats has the following records:
{
"_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 ]
}
truncate individual numbers to integers, then sum
const $ = db.command.aggregate
let res = await db.collection('stats').aggregate()
.project({
truncated: $.map({
input: '$sales',
as: 'num',
in: $.trunc('$$num'),
})
})
.project({
total: $.sum('$truncated')
})
.end()
The returned results are as follows:
{ "_id": 1, "total": 16 }
{ "_id": 2, "total": 19 }
# objectToArray
Convert an object to an array. The method turns each key-value pair of the object into an element of the output array, with elements of the form { k: <key>, v: <value> }.
# API Description
The syntax is as follows:
db.command.aggregate.objectToArray(<object>)
# Sample code
Suppose the collection items has the following records:
{ "_id": 1, "attributes": { "color": "red", "price": 150 } }
{ "_id": 2, "attributes": { "color": "blue", "price": 50 } }
{ "_id": 3, "attributes": { "color": "yellow", "price": 10 } }
const $ = db.command.aggregate
let res = await db.collection('items').aggregate()
.project({
array: $.objectToArray('$attributes')
})
.end()
The returned results are as follows:
{ "_id": 1, "array": [{ "k": "color", "v": "red" }, { "k": "price", "v": 150 }] }
{ "_id": 2, "array": [{ "k": "color", "v": "blue" }, { "k": "price", "v": 50 }] }
{ "_id": 3, "array": [{ "k": "color", "v": "yellow" }, { "k": "price", "v": 10 }] }
# range
Returns a set of generated sequence numbers. Given a start value, an end value, and a non-zero step size, range returns a sequence that starts at the start value and gradually increases with the given step size, but not the end value.
# API Description
The syntax is as follows:
db.command.aggregate.range([<start>, <end>, <non-zero step>])
| Fields | Description |
|---|---|
| start | start value, an expression that resolves to an integer |
| end | The end value, an expression that resolves to an integer |
| non-zero step | optional, step size, an expression that resolves to a non-zero integer, defaults to 1 |
# Sample code
Suppose the collection stats has the following records:
{ "_id": 1, "max": 52 }
{ "_id": 2, "max": 38 }
const $ = db.command.aggregate
db.collection('stats').aggregate()
.project({
points: $.range([0, '$max', 10])
})
.end()
The returned results are as follows:
{ "_id": 1, "points": [0, 10, 20, 30, 40, 50] }
{ "_id": 2, "points": [0, 10, 20, 30] }
# reduce
Similar to JavaScript's reduce method, which applies an expression to each element of an array and then normalizes it to a single element.
# API Description
The syntax is as follows:
db.command.aggregate.reduce({
input: <array>
initialValue: <expression>,
in: <expression>
})
| Fields | Description |
|---|---|
| input | input array, which can be any expression that resolves to an array |
| initialValue | Initial Value |
| in | The expression used to act on each element, there are two variables available in in, value is the variable representing the accumulated value, this is the variable representing the current array element |
# Sample code
Simple String Concatenation
Suppose the collection player has the following records:
{ "_id": 1, "fullname": [ "Stephen", "Curry" ] }
{ "_id": 2, "fullname": [ "Klay", "Thompsom" ] }
Get the full name of each player, prefixed with Player::
const $ = db.command.aggregate
let res = await db.collection('player').aggregate()
.project({
info: $.reduce({
input: '$fullname',
initialValue: 'Player:',
in: $.concat(['$$value', ' ', '$$this']),
})
})
.end()
The returned results are as follows:
{ "_id": 1, "info": "Player: Stephen Curry" }
{ "_id": 2, "info": "Player: Klay Thompson" }
Get the full name of each player, unprefixed:
const $ = db.command.aggregate
let res = await db.collection('player').aggregate()
.project({
name: $.reduce({
input: '$fullname',
initialValue: '',
in: $.concat([
'$$value',
$.cond({
if: $.eq(['$$value', '']),
then: '',
else: ' ',
}),
'$$this',
]),
})
})
.end()
The returned results are as follows:
{ "_id": 1, "name": "Stephen Curry" }
{ "_id": 2, "name": "Klay Thompson" }
# reverseArray
Returns the reversed form of the given array.
# API Description
The syntax is as follows:
db.command.aggregate.reverseArray(<array>)
Arguments can be arbitrarily parsed as array expressions.
# Sample code
Suppose the collection stats has the following records:
{
"_id": 1,
"sales": [ 1, 2, 3, 4, 5 ]
}
Take sales in reverse order:
const $ = db.command.aggregate
let res = await db.collection('stats').aggregate()
.project({
reversed: $.reverseArray('$sales'),
})
.end()
The returned results are as follows:
{ "_id": 1, "reversed": [5, 4, 3, 2, 1] }
# size
Returns the length of the array.
# API Description
The syntax is as follows:
db.command.aggregate.size(<array>)
<array> can be any expression that resolves to an array.
# Sample code
Suppose the collection shops has the following records:
{ "_id": 1, "staff": [ "John", "Middleton", "George" ] }
{ "_id": 2, "staff": [ "Steph", "Jack" ] }
Count the number of employees in each store:
const $ = db.command.aggregate
let res = await db.collection('shops').aggregate()
.project({
totalStaff: $.size('$staff')
})
.end()
The returned results are as follows:
{ "_id": 1, "totalStaff": 3 }
{ "_id": 2, "totalStaff": 2 }
# slice
Similar to JavaScript's slice method. Returns the specified subset of the given array.
# API Description
There are two syntaxes:
Return n elements from the beginning or end:
db.command.aggregate.slice([<array>, <n>])
Returns n elements from the specified position counted as the beginning of the array, backwards or forwards:
db.command.aggregate.slice([<array>, <position>, <n>])
<array> can be any expression that resolves to an array.
<position> can be any expression that resolves to an integer. If positive, start the array with the <position>-th element of the array; if <position> is longer than the array length, slice returns an empty array. If it is a negative number, the last <position> element of the array is used as the start of the array; if the absolute value of <position> is greater than the length of the array, the start position is the start position of the array.
<n> can be any expression that resolves to an integer. If <position> is provided, <n> must be a positive integer. If positive, slice returns the first n elements. If negative, slice returns the last n elements.
# Sample code
Suppose the collection people has the following records:
{ "_id": 1, "hobbies": [ "basketball", "football", "tennis", "badminton" ] }
{ "_id": 2, "hobbies": [ "golf", "handball" ] }
{ "_id": 3, "hobbies": [ "table tennis", "swimming", "rowing" ] }
Unity returns the first two hobbies:
const $ = db.command.aggregate
let res = await db.collection('fruits').aggregate()
.project({
hobbies: $.slice(['$hobbies', 2]),
})
.end()
The returned results are as follows:
{ "_id": 1, "hobbies": [ "basketball", "football" ] }
{ "_id": 2, "hobbies": [ "golf", "handball" ] }
{ "_id": 3, "hobbies": [ "table tennis", "swimming" ] }
# zip
The elements of the same serial number in the second-dimensional array of the two-dimensional array are assembled into a new array and then assembled into a new two-dimensional array. For example, [ [ 1, 2, 3 ], [ "a", "b", "c" ] ] can be converted to [ [ 1, "a" ], [ 2, "b" ], [ 3 , "c" ] ].
# API Description
The syntax is as follows:
db.command.aggregate.zip({
inputs: [<array1>, <array2>, ...],
useLongestLength: <boolean>,
defaults: <array>
})
inputs is a two-dimensional array (inputs cannot be a field reference), where the expression for each element (this can be a field reference) can be resolved as an array. If either of these expressions returns null, <inputs> also returns null. An error is returned if either of the expressions does not point to a valid field / resolves to an array / resolves to null.
useLongestLength determines whether the length of the output array takes the length of the longest array in the input array. The default is false, that is, the length of the shortest array in the input array is the length of each element of the output array.
defaults is an array that specifies the default values for each element of the array to use if the input arrays are of different lengths. If this field is specified, useLongestLength must be specified, otherwise an error will be returned. If useLongestLength is true but defaults is empty or not specified, zip uses null as the default default value for array elements. When specifying default values for each element, the length of the defaults array must be the maximum length of the input array.
# Sample code
Suppose the collection stats has the following records:
{ "_id": 1, "zip1": [1, 2], "zip2": [3, 4], "zip3": [5, 6] ] }
{ "_id": 2, "zip1": [1, 2], "zip2": [3], "zip3": [4, 5, 6] ] }
{ "_id": 3, "zip1": [1, 2], "zip2": [3] ] }
Only transmit inputs
const $ = db.command.aggregate
let res = await db.collection('items').aggregate()
.project({
zip: $.zip({
inputs: [
'$zip1', // 字段引用
'$zip2',
'$zip3',
],
})
})
.end()
The returned results are as follows:
{ "_id": 1, "zip": [ [1, 3, 5], [2, 4, 6] ] }
{ "_id": 2, "zip": [ [1, 3, 4] ] }
{ "_id": 3, "zip": null }
set useLongestLength
If useLongestLength is set to true:
const $ = db.command.aggregate
let res = await db.collection('items').aggregate()
.project({
zip: $.zip({
inputs: [
'$zip1', // 字段引用
'$zip2',
'$zip3',
],
useLongestLength: true,
})
})
.end()
The returned results are as follows:
{ "_id": 1, "zip": [ [1, 3, 5], [2, 4, 6] ] }
{ "_id": 2, "zip": [ [1, 3, 4], [2, null, 5], [null, null, 6] ] }
{ "_id": 3, "zip": null }
set defaults
const $ = db.command.aggregate
let res = await db.collection('items').aggregate()
.project({
zip: $.zip({
inputs: [
'$zip1', // 字段引用
'$zip2',
'$zip3',
],
useLongestLength: true,
defaults: [-300, -200, -100],
})
})
.end()
The returned results are as follows:
{ "_id": 1, "zip": [ [1, 3, 5], [2, 4, 6] ] }
{ "_id": 2, "zip": [ [1, 3, 4], [2, -200, 5], [-300, -200, 6] ] }
{ "_id": 3, "zip": null }
# boolean operators
# and
Given multiple expressions, and returns true only if all expressions return true, and false otherwise.
# API Description
The syntax is as follows:
db.command.aggregate.and([<expression1>, <expression2>, ...])
If the expression returns false, null, 0, or undefined, the expression resolves to false, otherwise it is considered true for other return values.
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "min": 10, "max": 100 }
{ "_id": 2, "min": 60, "max": 80 }
{ "_id": 3, "min": 30, "max": 50 }
Find records where min is greater than or equal to 30 and max is less than or equal to 80.
const $ = db.command.aggregate
let res = await db.collection('price').aggregate()
.project({
fullfilled: $.and([$.gte(['$min', 30]), $.lte(['$max', 80])])
})
.end()
The returned results are as follows:
{ "_id": 1, "fullfilled": false }
{ "_id": 2, "fullfilled": true }
{ "_id": 3, "fullfilled": true }
# not
Given an expression, not returns false if the expression returns true, and true otherwise. Note that expressions cannot be logical expressions (and, or, nor, not).
# API Description
The syntax is as follows:
db.command.aggregate.not(<expression>)
If the expression returns false, null, 0, or undefined, the expression resolves to false, otherwise it is considered true for other return values.
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "min": 10, "max": 100 }
{ "_id": 2, "min": 60, "max": 80 }
{ "_id": 3, "min": 30, "max": 50 }
Find records whose min is not greater than 40.
const $ = db.command.aggregate
let res = await db.collection('price').aggregate()
.project({
fullfilled: $.not($.gt(['$min', 40]))
})
.end()
The returned results are as follows:
{ "_id": 1, "fullfilled": true }
{ "_id": 2, "fullfilled": false }
{ "_id": 3, "fullfilled": true }
# or
Given multiple expressions, or returns true if any of the expressions returns true, otherwise it returns false.
# API Description
The syntax is as follows:
db.command.aggregate.or([<expression1>, <expression2>, ...])
If the expression returns false, null, 0, or undefined, the expression resolves to false, otherwise it is considered true for other return values.
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "min": 10, "max": 100 }
{ "_id": 2, "min": 50, "max": 60 }
{ "_id": 3, "min": 30, "max": 50 }
Find records where min is less than 40 or max is greater than 60.
const $ = db.command.aggregate
let res = await db.collection('price').aggregate()
.project({
fullfilled: $.or([$.lt(['$min', 40]), $.gt(['$max', 60])])
})
.end()
The returned results are as follows:
{ "_id": 1, "fullfilled": true }
{ "_id": 2, "fullfilled": false }
{ "_id": 3, "fullfilled": true }
# Comparison operator
# cmp
Given two values, return their comparison value:
# API Description
If the first value is less than the second value, return -1 Returns 1 if the first value is greater than the second value Returns 0 if the two values are equal
The syntax is as follows:
db.command.aggregate.cmp([<expression1>, <expression2>])
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "shop1": 10, "shop2": 100 }
{ "_id": 2, "shop1": 80, "shop2": 20 }
{ "_id": 3, "shop1": 50, "shop2": 50 }
Find the price comparison of each item in shop1 and shop2.
const $ = db.command.aggregate
let res = await db.collection('price').aggregate()
.project({
compare: $.cmp(['$shop1', '$shop2']))
})
.end()
The returned results are as follows:
{ "_id": 1, "compare": -1 }
{ "_id": 2, "compare": 1 }
{ "_id": 3, "compare": 0 }
# eq
Matches two values and returns true if they are equal, false otherwise.
# API Description
The syntax is as follows:
db.command.aggregate.eq([<value1>, <value2>])
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "value": 10 }
{ "_id": 2, "value": 80 }
{ "_id": 3, "value": 50 }
Find records with value equal to 50.
const $ = db.command.aggregate
let res = await db.collection('price').aggregate()
.project({
matched: $.eq(['$value', 50])
})
.end()
The returned results are as follows:
{ "_id": 1, "matched": false }
{ "_id": 2, "matched": false }
{ "_id": 3, "matched": true }
# gt
Matches two values and returns true if the former is greater than the latter, false otherwise.
# API Description
The syntax is as follows:
db.command.aggregate.gt([<value1>, <value2>])
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "value": 10 }
{ "_id": 2, "value": 80 }
{ "_id": 3, "value": 50 }
Check if value is greater than 50.
const $ = db.command.aggregate
db.collection('price').aggregate()
.project({
matched: $.gt(['$value', 50])
})
.end()
The returned results are as follows:
{ "_id": 1, "matched": false }
{ "_id": 2, "matched": true }
{ "_id": 3, "matched": false }
# gte
Matches two values and returns true if the former is greater than or equal to the latter, false otherwise.
# API Description
The syntax is as follows:
db.command.aggregate.gte([<value1>, <value2>])
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "value": 10 }
{ "_id": 2, "value": 80 }
{ "_id": 3, "value": 50 }
Check if value is greater than or equal to 50.
const $ = db.command.aggregate
let res = await b.collection('price').aggregate()
.project({
matched: $.gte(['$value', 50])
})
.end()
The returned results are as follows:
{ "_id": 1, "matched": false }
{ "_id": 2, "matched": true }
{ "_id": 3, "matched": true }
# lt
Matches two values and returns true if the former is less than the latter, false otherwise.
# API Description
The syntax is as follows:
db.command.aggregate.lt([<value1>, <value2>])
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "value": 10 }
{ "_id": 2, "value": 80 }
{ "_id": 3, "value": 50 }
Check if value is less than 50.
const $ = db.command.aggregate
let res = await db.collection('price').aggregate()
.project({
matched: $.lt(['$value', 50])
})
.end()
The returned results are as follows:
{ "_id": 1, "matched": true }
{ "_id": 2, "matched": false }
{ "_id": 3, "matched": false }
# lte
Matches two values and returns true if the former is less than or equal to the latter, false otherwise.
# API Description
The syntax is as follows:
db.command.aggregate.lte([<value1>, <value2>])
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "value": 10 }
{ "_id": 2, "value": 80 }
{ "_id": 3, "value": 50 }
Check if value is less than 50.
const $ = db.command.aggregate
let res = await db.collection('price').aggregate()
.project({
matched: $.lte(['$value', 50])
})
.end()
The returned results are as follows:
{ "_id": 1, "matched": true }
{ "_id": 2, "matched": false }
{ "_id": 3, "matched": true }
# neq
Matches two values, returning true if they are not equal, false otherwise.
# API Description
The syntax is as follows:
db.command.aggregate.neq([<value1>, <value2>])
# Sample code
Suppose the collection price has the following records:
{ "_id": 1, "value": 10 }
{ "_id": 2, "value": 80 }
{ "_id": 3, "value": 50 }
Find records where value is not equal to 50.
const $ = db.command.aggregate
let res = await db.collection('price').aggregate()
.project({
matched: $.neq(['$value', 50])
})
.end()
The returned results are as follows:
{ "_id": 1, "matched": true }
{ "_id": 2, "matched": true }
{ "_id": 3, "matched": false }
# Conditional operator
# cond
Evaluates a Boolean expression, returning one of the two specified values.
# API Description
cond is used as follows:
cond({ if: <布尔表达式>, then: <真值>, else: <假值> })
or:
cond([ <布尔表达式>, <真值>, <假值> ])
In both forms, the three arguments (if, then, else) are required.
$cond will return <true value> if the boolean expression is true, otherwise it will return <false value>
# Sample code
Suppose the collection items records as follows:
{ "_id": "0", "name": "item-a", "amount": 100 }
{ "_id": "1", "name": "item-b", "amount": 200 }
{ "_id": "2", "name": "item-c", "amount": 300 }
We can use cond to generate a new field discount based on the amount field:
const $ = db.command.aggregate
let res = await db.collection('items').aggregate()
.project({
name: 1,
discount: $.cond({
if: $.gte(['$amount', 200]),
then: 0.7,
else: 0.9
})
})
.end()
The output is as follows:
{ "_id": "0", "name": "item-a", "discount": 0.9 }
{ "_id": "1", "name": "item-b", "discount": 0.7 }
{ "_id": "2", "name": "item-c", "discount": 0.7 }
# ifNull
Evaluates the given expression and returns an alternate value if the expression result is null, undefined, or does not exist; otherwise, returns the original value.
# API Description
ifNull is used as follows:
ifNull([ <表达式>, <替代值> ])
# Sample code
Suppose the collection items records as follows:
{ "_id": "0", "name": "A", "description": "这是商品A" }
{ "_id": "1", "name": "B", "description": null }
{ "_id": "2", "name": "C" }
We can use ifNull to add an alternative value for documents where the desc field does not exist, or for which the desc field is null.
const $ = db.command.aggregate
let res = await db.collection('items').aggregate()
.project({
_id: 0,
name: 1,
description: $.ifNull(['$description', '商品描述空缺'])
})
.end()
The output is as follows:
{ "name": "A", "description": "这是商品A" }
{ "name": "B", "description": "商品描述空缺" }
{ "name": "C", "description": "商品描述空缺" }
# switch
Calculate the return value according to the given switch-case-default,
# API Description
switch is used as follows:
switch({
branches: [
case: <表达式>, then: <表达式>,
case: <表达式>, then: <表达式>,
...
],
default: <表达式>
})
# Sample code
Suppose the collection items records as follows:
{ "_id": "0", "name": "item-a", "amount": 100 }
{ "_id": "1", "name": "item-b", "amount": 200 }
{ "_id": "2", "name": "item-c", "amount": 300 }
We can use switch to generate a new field discount based on the amount field:
const $ = db.command.aggregate
let res = await db.collection('items').aggregate()
.project({
name: 1,
discount: $.switch({
branches: [
{ case: $.gt(['$amount', 250]), then: 0.8 },
{ case: $.gt(['$amount', 150]), then: 0.9 }
],
default: 1
})
})
.end()
The output is as follows:
{ "_id": "0", "name": "item-a", "discount": 1 }
{ "_id": "1", "name": "item-b", "discount": 0.9 }
{ "_id": "2", "name": "item-c", "discount": 0.8 }
# date operator
Notice
timezonein the following date operators supports the following forms
timezone: "Asia/Shanghai" // Asia/Shanghai时区
timezone: "+08" // utc+8时区
timezone: "+08:30" // 时区偏移8小时30分
timezone: "+0830" // 时区偏移8小时30分，同上
# dateFromParts
Given information about a date, constructs and returns a date object.
# API Description
The syntax is as follows:
db.command.aggregate.dateFromParts({
year: <year>,
month: <month>,
day: <day>,
hour: <hour>,
minute: <minute>,
second: <second>,
millisecond: <ms>,
timezone: <tzExpression>
})
You can also use the ISO 8601 standard:
db.command.aggregate.dateFromParts({
isoWeekYear: <year>,
isoWeek: <week>,
isoDayOfWeek: <day>,
hour: <hour>,
minute: <minute>,
second: <second>,
millisecond: <ms>,
timezone: <tzExpression>
})
# Sample code
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
date: $.dateFromParts({
year: 2017,
month: 2,
day: 8,
hour: 12,
timezone: 'America/New_York'
}),
})
.end()
The output is as follows:
{
"date": ISODate("2017-02-08T17:00:00.000Z")
}
# dateFromString
Convert a date/time string to a date object
# API Description
The syntax is as follows:
db.command.aggregate.dateFromString({
dateString: <dateStringExpression>,
timezone: <tzExpression>
})
# Sample code
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
date: $.dateFromString({
dateString: "2019-05-14T09:38:51.686Z"
})
})
.end()
The output is as follows:
{
"date": ISODate("2019-05-14T09:38:51.686Z")
}
# dateToString
Formats a date object as a string according to the specified expression.
# API Description
The call form of dateToString is as follows:
db.command.aggregate.dateToString({
date: <日期表达式>,
format: <格式化表达式>,
timezone: <时区表达式>,
onNull: <空值表达式>
})
The following is a detailed description of the four expressions:
| Name | Description |
|---|---|
| Date Expression | Required. Specifies that the field value should be a date that can be converted to a string. |
| Formatting Expression | Optional. It can be any valid string containing a "format specifier". |
| Timezone expression | Optional. Specifies the time zone for the result of the operation. It can parse strings in the format UTC Offset or Olson Timezone Identifier. |
| null expression | optional. When <date_expression> returns null or does not exist, the value specified by this expression will be returned. |
The following is a detailed description of the format specifiers:
| specifier | description | legal values |
|---|---|---|
| %d | Day of the month (2 digits, 0-padded) | 01 - 31 |
| %G | Year in ISO 8601 format | 0000 - 9999 |
| %H | hour (2 digits, 0-padded, 24-hour clock) | 00 - 23 |
| %j | Day of the year (3 digits, 0-padded) | 001 - 366 |
| %L | milliseconds (3 digits, 0-padded) | 000 - 999 |
| %m | month (2 digits, 0-padded) | 01 - 12 |
| %M | minutes (2 digits, 0-padded) | 00 - 59 |
| %S | Seconds (2 digits, padded with 0s) | 00 - 60 |
| %w | Day of the week | 1 - 7 |
| %u | Day of the week in ISO 8601 format | 1 - 7 |
| %U | week of year (2 digits, 0-padded) | 00 - 53 |
| %V | week of year in ISO 8601 format | 1 - 53 |
| %Y | Year (4 digits, 0-padded) | 0000 - 9999 |
| %z | Timezone offset from UTC | +/-[hh][mm] |
| %Z | timezone offset from UTC in minutes | +/-mmm |
| %% | Percent sign as character | % |
# Sample code
Suppose the collection students has the following records:
{ "date": "1999-12-11T16:00:00.000Z", "firstName": "Yuanxin", "lastName": "Dong" }
{ "date": "1998-11-10T16:00:00.000Z", "firstName": "Weijia", "lastName": "Wang" }
{ "date": "1997-10-09T16:00:00.000Z", "firstName": "Chengxi", "lastName": "Li" }
Format Date
The following is the value of the date field, formatted as a string like year-month-date:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
formatDate: $.dateToString({
date: '$date',
format: '%Y-%m-%d'
})
})
.end()
The results returned are as follows:
{ "formatDate": "1999-12-11" }
{ "formatDate": "1998-11-10" }
{ "formatDate": "1997-10-09" }
Time zone time
The following is an example of formatting a date field value to Shanghai time zone time:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
formatDate: $.dateToString({
date: '$date',
format: '%H:%M:%S',
timezone: 'Asia/Shanghai'
})
})
.end()
The results returned are as follows:
{ "formatDate": "00:00:00" }
{ "formatDate": "00:00:00" }
{ "formatDate": "00:00:00" }
Default value for missing cases
When the specified <date_expression> returns empty or does not exist, you can set the default value in the absence of:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
formatDate: $.dateToString({
date: '$empty',
onNull: 'null'
})
})
.end()
The results returned are as follows:
{ "formatDate": "null" }
{ "formatDate": "null" }
{ "formatDate": "null" }
# dayOfMonth
Returns the number of days (day of the month) corresponding to the date field, as a number between 1 and 31.
# API Description
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.dayOfMonth(<日期字段>)
db.command.aggregate.dayOfMonth({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use dayOfMonth() to project the date field to get the corresponding date:
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
dayOfMonth: $.dayOfMonth('$date')
})
.end()
The output is as follows:
{
"dayOfMonth": 14
}
# dayOfWeek
Returns the number of days (day of the week) corresponding to the date field, as an integer between 1 (Sunday) and 7 (Saturday).
# API Description
NOTE: Sunday is the 1st day of the week
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.dayOfWeek(<日期字段>)
db.command.aggregate.dayOfWeek({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use dayOfWeek() to project the date field to get the corresponding day (day of the week):
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
dayOfWeek: $.dayOfWeek('$date')
})
.end()
The output is as follows:
{
"dayOfWeek": 3
}
# dayOfYear
Returns the number of days (day of the year) corresponding to the date field, as an integer between 1 and 366.
# API Description
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.dayOfYear(<日期字段>)
db.command.aggregate.dayOfYear({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use dayOfYear() to project the date field to get the corresponding number of days (day of the year):
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
dayOfYear: $.dayOfYear('$date')
})
.end()
The output is as follows:
{
"dayOfYear": 134
}
# hour
Returns the hour corresponding to the date field, as an integer between 0 and 23.
# API Description
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.hour(<日期字段>)
db.command.aggregate.hour({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use hour() to project the date field to get the corresponding hour:
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
hour: $.hour('$date')
})
.end()
The output is as follows:
{
"hour": 9
}
# isoDayOfWeek
Returns the ISO 8601 standard day number (day of the week) for the date field, as an integer between 1 (Monday) and 7 (Sunday).
# API Description
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.isoDayOfWeek(<日期字段>)
db.command.aggregate.isoDayOfWeek({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use isoDayOfWeek() to project the date field to get the corresponding ISO 8601 standard number of days (day of the week):
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
isoDayOfWeek: $.isoDayOfWeek('$date')
})
.end()
The output is as follows:
{
"isoDayOfWeek": 2
}
# isoWeek
Returns the ISO 8601 standard week number (week of the year) for the date field, as an integer between 1 and 53.
# API Description
According to the ISO 8601 standard, Monday to Sunday are regarded as a week, and the week on which the first Thursday of the current year is located is regarded as the first week of the current year.
For example: January 7, 2016 is the first Thursday of that year, then 2016.01.04 (Monday) to 2016.01.10 (Sunday) is week 1. Similarly, the week number for January 1, 2016 is 53.
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.isoWeek(<日期字段>)
db.command.aggregate.isoWeek({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use isoWeek() to project the date field to get the corresponding ISO 8601 week number (week of the year):
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
isoWeek: $.isoWeek('$date')
})
.end()
The output is as follows:
{
"isoWeek": 20
}
# isoWeekYear
Returns the ISO 8601 standard number of days (day of the year) for a date field.
# API Description
The "year" here starts on the Monday of the first week and ends on the Sunday of the last week.
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.isoWeekYear(<日期字段>)
db.command.aggregate.isoWeekYear({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use isoWeekYear() to project the date field to get the corresponding ISO 8601 standard number of days (day of the year):
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
isoWeekYear: $.isoWeekYear('$date')
})
.end()
The output is as follows:
{
"isoWeekYear": 2019
}
# millisecond
Returns the number of milliseconds corresponding to the date field, as an integer between 0 and 999.
# API Description
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.millisecond(<日期字段>)
db.command.aggregate.millisecond({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use millisecond() to project the date field to get the corresponding milliseconds:
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
millisecond: $.millisecond('$date'),
})
.end()
The output is as follows:
{
"millisecond": 686
}
# minute
Returns the number of minutes corresponding to the date field, as an integer between 0 and 59.
# API Description
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.minute(<日期字段>)
db.command.aggregate.minute({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use minute() to project the date field to get the corresponding minutes:
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
minute: $.minute('$date')
})
.end()
The output is as follows:
{
"minute": 38
}
# month
Returns the month corresponding to the date field as an integer between 1 and 12.
# API Description
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.month(<日期字段>)
db.command.aggregate.month({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use month() to project the date field to get the corresponding month:
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
month: $.month('$date')
})
.end()
The output is as follows:
{
"month": 5
}
# second
Returns the number of seconds corresponding to the date field, as an integer between 0 and 59, and may be equal to 60 in special cases (leap seconds).
# API Description
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.second(<日期字段>)
db.command.aggregate.second({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use second() to project the date field to get the corresponding seconds:
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
second: $.second('$date')
})
.end()
The output is as follows:
{
"second": 51
}
# week
Returns the week number (week of the year) corresponding to the date field, as an integer between 0 and 53.
# API Description
Week starts with Sunday, the first Sunday of every year is the start of week 1, before this day is week 0.
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.week(<日期字段>)
db.command.aggregate.week({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use week() to project the date field to get the corresponding week number (week of the year):
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
week: $.week('$date')
})
.end()
The output is as follows:
{
"week": 19
}
# year
Returns the year corresponding to the date field.
# API Description
The interface has the following two usages, the syntax is as follows:
db.command.aggregate.year(<日期字段>)
db.command.aggregate.year({date:<日期字段>,timezone:<时区>})
# Sample code
Suppose the collection dates has the following documents:
{
"_id": 1,
"date": ISODate("2019-05-14T09:38:51.686Z")
}
We use year() to project the date field to get the corresponding year:
const $ = db.command.aggregate
let res = await db
.collection('dates')
.aggregate()
.project({
_id: 0,
year: $.year('$date')
})
.end()
The output is as follows:
{
"year": 2019
}
# subtract
see subtract
# constant operator
# literal
Returns the literal of a value directly, without any parsing and processing.
# API Description
literal is used as follows:
literal(<值>)
If <value> is an expression, then literal will not parse or evaluate the expression, but return the expression directly.
# Sample code
For example, we have an items collection with the following data:
{ "_id": "0", "price": "$1" }
{ "_id": "1", "price": "$5.60" }
{ "_id": "2", "price": "$8.90" }
Use $ in literal form
The following code uses literal to generate a new field isOneDollar that indicates whether the price field is strictly equal to "$1".
Note: We cannot use eq(['$price', '$1']) here, because "$1" is an expression that represents the value of the "1" field, not a string literal "$1".
const $ = db.command.aggregate
let res = await db.collection('items').aggregate()
.project({
isOneDollar: $.eq(['$price', $.literal('$1')])
})
.end()
The output is as follows:
{ "_id": "0", "isOneDollar": true }
{ "_id": "1", "isOneDollar": false }
{ "_id": "2", "isOneDollar": false }
Project a field, the corresponding value is 1
The following code uses literal, projecting a new field amount with a value of 1.
const $ = db.command.aggregate
db.collection('items').aggregate()
.project({
price: 1,
amount: $.literal(1)
})
.end()
The output is as follows:
{ "_id": "0", "price": "$1", "amount": 1 }
{ "_id": "1", "price": "$5.60", "amount": 1 }
{ "_id": "2", "price": "$8.90", "amount": 1 }
# Object operators
# mergeObjects
Merge multiple documents into a single document.
# API Description
The form of use is as follows:
When used in group():
mergeObjects(<document>)
When used in other expressions:
mergeObjects([<document1>, <document2>, ...])
# Sample code
Use with group()
Suppose the following documents exist in the collection sales:
{ "_id": 1, "year": 2018, "name": "A", "volume": { "2018Q1": 500, "2018Q2": 500 } }
{ "_id": 2, "year": 2017, "name": "A", "volume": { "2017Q1": 400, "2017Q2": 300, "2017Q3": 0, "2017Q4": 0 } }
{ "_id": 3, "year": 2018, "name": "B", "volume": { "2018Q1": 100 } }
{ "_id": 4, "year": 2017, "name": "B", "volume": { "2017Q3": 100, "2017Q4": 250 } }
The following code uses mergeObjects() to merge documents with the same name:
const $ = db.command.aggregate
let res = await db.collection('sales').aggregate()
.group({
_id: '$name',
mergedVolume: $.mergeObjects('$volume')
})
.end()
The output is as follows:
{ "_id": "A", "mergedVolume": { "2017Q1": 400, "2017Q2": 300, "2017Q3": 0, "2017Q4": 0, "2018Q1": 500, "2018Q2": 500 } }
{ "_id": "B", "mergedVolume": { "2017Q3": 100, "2017Q4": 250, "2018Q1": 100 } }
General usage
Suppose the following documents exist in the collection test:
{ "_id": 1, "foo": { "a": 1 }, "bar": { "b": 2 } }
{ "_id": 2, "foo": { "c": 1 }, "bar": { "d": 2 } }
{ "_id": 3, "foo": { "e": 1 }, "bar": { "f": 2 } }
The following code uses mergeObjects() to merge the foo and bar fields in the document into foobar:
const $ = db.command.aggregate
let res = await db.collection('sales').aggregate()
.project({
foobar: $.mergeObjects(['$foo', '$bar'])
})
.end()
The output is as follows:
{ "_id": 1, "foobar": { "a": 1, "b": 2 } }
{ "_id": 2, "foobar": { "c": 1, "d": 2 } }
{ "_id": 3, "foobar": { "e": 1, "f": 2 } }
# objectToArray
# set operators
# 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.
# API Description
The syntax is as follows:
allElementsTrue([<expression>])
# Sample code
Suppose the collection test has the following records:
{ "_id": 1, "array": [ true ] }
{ "_id": 2, "array": [ ] }
{ "_id": 3, "array": [ false ] }
{ "_id": 4, "array": [ true, false ] }
{ "_id": 5, "array": [ 0 ] }
{ "_id": 6, "array": [ "stark" ] }
The following code uses allElementsTrue() to determine whether all of the array fields are true:
const $ = db.command.aggregate
let res = await db.collection('price')
.aggregate()
.project({
isAllTrue: $.allElementsTrue(['$array'])
})
.end()
The returned results are as follows:
{ "_id": 1, "isAllTrue": true }
{ "_id": 2, "isAllTrue": true }
{ "_id": 3, "isAllTrue": false }
{ "_id": 4, "isAllTrue": false }
{ "_id": 5, "isAllTrue": false }
{ "_id": 6, "isAllTrue": true }
# 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.
# API Description
The syntax is as follows:
anyElementTrue([<expression>])
# Sample code
Suppose the collection test has the following records:
{ "_id": 1, "array": [ true ] }
{ "_id": 2, "array": [ ] }
{ "_id": 3, "array": [ false ] }
{ "_id": 4, "array": [ true, false ] }
{ "_id": 5, "array": [ 0 ] }
{ "_id": 6, "array": [ "stark" ] }
The following code uses anyElementTrue() to determine whether the array field contains a true value:
const $ = db.command.aggregate
let res = await db.collection('price')
.aggregate()
.project({
isAnyTrue: $.anyElementTrue(['$array'])
})
.end()
The returned results are as follows:
{ "_id": 1, "isAnyTrue": true }
{ "_id": 2, "isAnyTrue": false }
{ "_id": 3, "isAnyTrue": false }
{ "_id": 4, "isAnyTrue": true }
{ "_id": 5, "isAnyTrue": false }
{ "_id": 6, "isAnyTrue": true }
# setDifference
Takes two sets as input, and outputs elements that exist only in the first set.
# API Description
The form of use is as follows:
setDifference([<expression1>, <expression2>])
# Sample code
Suppose the following data exists in the collection test:
{ "_id": 1, "A": [ 1, 2 ], "B": [ 1, 2 ] }
{ "_id": 2, "A": [ 1, 2 ], "B": [ 2, 1, 2 ] }
{ "_id": 3, "A": [ 1, 2 ], "B": [ 1, 2, 3 ] }
{ "_id": 4, "A": [ 1, 2 ], "B": [ 3, 1 ] }
{ "_id": 5, "A": [ 1, 2 ], "B": [ ] }
{ "_id": 6, "A": [ 1, 2 ], "B": [ {}, [] ] }
{ "_id": 7, "A": [ ], "B": [ ] }
{ "_id": 8, "A": [ ], "B": [ 1 ] }
The following code uses setDifference to find numbers that only exist in B:
let res = await db.collection('test')
.aggregate()
.project({
isBOnly: $.setDifference(['$B', '$A'])
})
.end()
{ "_id": 1, "isBOnly": [] }
{ "_id": 2, "isBOnly": [] }
{ "_id": 3, "isBOnly": [3] }
{ "_id": 4, "isBOnly": [3] }
{ "_id": 5, "isBOnly": [] }
{ "_id": 6, "isBOnly": [{}, []] }
{ "_id": 7, "isBOnly": [] }
{ "_id": 8, "isBOnly": [1] }
# setEquals
Input two sets and determine whether the elements contained in the two sets are the same (regardless of order, deduplication).
# API Description
The form of use is as follows:
setEquals([<expression1>, <expression2>])
# Sample code
Suppose the following data exists in the collection test:
{ "_id": 1, "A": [ 1, 2 ], "B": [ 1, 2 ] }
{ "_id": 2, "A": [ 1, 2 ], "B": [ 2, 1, 2 ] }
{ "_id": 3, "A": [ 1, 2 ], "B": [ 1, 2, 3 ] }
{ "_id": 4, "A": [ 1, 2 ], "B": [ 3, 1 ] }
{ "_id": 5, "A": [ 1, 2 ], "B": [ ] }
{ "_id": 6, "A": [ 1, 2 ], "B": [ {}, [] ] }
{ "_id": 7, "A": [ ], "B": [ ] }
{ "_id": 8, "A": [ ], "B": [ 1 ] }
The following code uses setEquals to determine whether two sets contain the same elements:
let res = await db.collection('test')
.aggregate()
.project({
sameElements: $.setEquals(['$A', '$B'])
})
.end()
{ "_id": 1, "sameElements": true }
{ "_id": 2, "sameElements": true }
{ "_id": 3, "sameElements": false }
{ "_id": 4, "sameElements": false }
{ "_id": 5, "sameElements": false }
{ "_id": 6, "sameElements": false }
{ "_id": 7, "sameElements": true }
{ "_id": 8, "sameElements": false }
# setIntersection
Input two sets, output the intersection of the two sets.
# API Description
The form of use is as follows:
setIntersection([<expression1>, <expression2>])
# Sample code
Suppose the following data exists in the collection test:
{ "_id": 1, "A": [ 1, 2 ], "B": [ 1, 2 ] }
{ "_id": 2, "A": [ 1, 2 ], "B": [ 2, 1, 2 ] }
{ "_id": 3, "A": [ 1, 2 ], "B": [ 1, 2, 3 ] }
{ "_id": 4, "A": [ 1, 2 ], "B": [ 3, 1 ] }
{ "_id": 5, "A": [ 1, 2 ], "B": [ ] }
{ "_id": 6, "A": [ 1, 2 ], "B": [ {}, [] ] }
{ "_id": 7, "A": [ ], "B": [ ] }
{ "_id": 8, "A": [ ], "B": [ 1 ] }
The following code uses setIntersection to output the intersection of two sets:
let res = await db.collection('test')
.aggregate()
.project({
commonToBoth: $.setIntersection(['$A', '$B'])
})
.end()
The output is as follows:
{ "_id": 1, "commonToBoth": [ 1, 2 ] }
{ "_id": 2, "commonToBoth": [ 1, 2 ] }
{ "_id": 3, "commonToBoth": [ 1, 2 ] }
{ "_id": 4, "commonToBoth": [ 1 ] }
{ "_id": 5, "commonToBoth": [ ] }
{ "_id": 6, "commonToBoth": [ ] }
{ "_id": 7, "commonToBoth": [ ] }
{ "_id": 8, "commonToBoth": [ ] }
# setIsSubset
Given two sets, determine whether the first set is a subset of the second set.
# API Description
The form of use is as follows:
setIsSubset([<expression1>, <expression2>])
# Sample code
Suppose the following data exists in the collection test:
{ "_id": 1, "A": [ 1, 2 ], "B": [ 1, 2 ] }
{ "_id": 2, "A": [ 1, 2 ], "B": [ 2, 1, 2 ] }
{ "_id": 3, "A": [ 1, 2 ], "B": [ 1, 2, 3 ] }
{ "_id": 4, "A": [ 1, 2 ], "B": [ 3, 1 ] }
{ "_id": 5, "A": [ 1, 2 ], "B": [ ] }
{ "_id": 6, "A": [ 1, 2 ], "B": [ {}, [] ] }
{ "_id": 7, "A": [ ], "B": [ ] }
{ "_id": 8, "A": [ ], "B": [ 1 ] }
The following code uses setIsSubset to determine whether the first set is a subset of the second set:
let res = await db.collection('test')
.aggregate()
.project({
AisSubsetOfB: $.setIsSubset(['$A', '$B'])
})
.end()
{ "_id": 1, "AisSubsetOfB": true }
{ "_id": 2, "AisSubsetOfB": true }
{ "_id": 3, "AisSubsetOfB": true }
{ "_id": 4, "AisSubsetOfB": false }
{ "_id": 5, "AisSubsetOfB": false }
{ "_id": 6, "AisSubsetOfB": false }
{ "_id": 7, "AisSubsetOfB": true }
{ "_id": 8, "AisSubsetOfB": true }
# setUnion
Input two sets, output the union of the two sets.
# API Description
The form of use is as follows:
setUnion([<expression1>, <expression2>])
# Sample code
Suppose the following data exists in the collection test:
{ "_id": 1, "A": [ 1, 2 ], "B": [ 1, 2 ] }
{ "_id": 2, "A": [ 1, 2 ], "B": [ 2, 1, 2 ] }
{ "_id": 3, "A": [ 1, 2 ], "B": [ 1, 2, 3 ] }
{ "_id": 4, "A": [ 1, 2 ], "B": [ 3, 1 ] }
{ "_id": 5, "A": [ 1, 2 ], "B": [ ] }
{ "_id": 6, "A": [ 1, 2 ], "B": [ {}, [] ] }
{ "_id": 7, "A": [ ], "B": [ ] }
{ "_id": 8, "A": [ ], "B": [ 1 ] }
The following code uses setUnion to output the union of two sets:
let res = await db.collection('test')
.aggregate()
.project({
AB: $.setUnion(['$A', '$B'])
})
.end()
The output is as follows:
{ "_id": 1, "AB": [ 1, 2 ] }
{ "_id": 2, "AB": [ 1, 2 ] }
{ "_id": 3, "AB": [ 1, 2, 3 ] }
{ "_id": 4, "AB": [ 1, 2, 3 ] }
{ "_id": 5, "AB": [ 1, 2 ] }
{ "_id": 6, "AB": [ 1, 2, {}, [] ] }
{ "_id": 7, "AB": [ ] }
{ "_id": 8, "AB": [ 1 ] }
# String operators
# concat
Concatenate strings and return the concatenated string.
# API Description
The syntax of concat is as follows:
db.command.aggregate.concat([<表达式1>, <表达式2>, ...])
The expression can be in the form of $ + specified field, or it can be a normal string. As long as it can be parsed into a string.
# Sample code
Suppose the collection students has the following records:
{ "firstName": "Yuanxin", "group": "a", "lastName": "Dong", "score": 84 }
{ "firstName": "Weijia", "group": "a", "lastName": "Wang", "score": 96 }
{ "firstName": "Chengxi", "group": "b", "lastName": "Li", "score": 80 }
With concat you can concatenate the lastName and firstName fields to get the full name of each student:
const $ = db.command.aggregate
db
.collection('students')
.aggregate()
.project({
_id: 0,
fullName: $.concat(['$firstName', ' ', '$lastName'])
})
.end()
The results returned are as follows:
{ "fullName": "Yuanxin Dong" }
{ "fullName": "Weijia Wang" }
{ "fullName": "Chengxi Li" }
# dateFromString
see dateFromString
# dateToString
see dateToString
# indexOfBytes
Finds a substring in the target string and returns the byte index (0-based) of the first occurrence of UTF-8. Returns -1 if no substring exists.
# API Description
The syntax of indexOfBytes is as follows:
db.command.aggregate.indexOfBytes([<目标字符串表达式>, <子字符串表达式>, <开始位置表达式>, <结束位置表达式>])
The following is a detailed description of the four expressions:
| expression | description |
|---|---|
| target string expression | any expression that can be parsed as a string |
| Substring expression | Any expression that can be parsed as a string |
| Start position expression | Any expression that can be parsed as a non-negative integer |
| End position expression | Any expression that can be parsed as a non-negative integer |
# Sample code
Suppose the collection students has the following records:
{ "firstName": "Yuanxin", "group": "a", "lastName": "Dong", "score": 84 }
{ "firstName": "Weijia", "group": "a", "lastName": "Wang", "score": 96 }
{ "firstName": "Chengxi", "group": "b", "lastName": "Li", "score": 80 }
Use indexOfBytes to find the position of the character "a" in the field firstName:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
aStrIndex: $.indexOfBytes(['$firstName', 'a'])
})
.end()
The results returned are as follows:
{ "aStrIndex": 2 }
{ "aStrIndex": 5 }
{ "aStrIndex": -1 }
# indexOfCP
Finds a substring in the target string and returns the code point index (0-based) of the first occurrence of UTF-8. Returns -1 if no substring exists.
# API Description
code point is a "code point", aka "code position". This refers specifically to the code points in the Unicode package, ranging from 0 (hexadecimal) to 10FFFF (hexadecimal).
The syntax of indexOfCP is as follows:
db.command.aggregate.indexOfCP([<目标字符串表达式>, <子字符串表达式>, <开始位置表达式>, <结束位置表达式>])
The following is a detailed description of the four expressions:
| expression | description |
|---|---|
| target string expression | any expression that can be parsed as a string |
| Substring expression | Any expression that can be parsed as a string |
| Start position expression | Any expression that can be parsed as a non-negative integer |
| End position expression | Any expression that can be parsed as a non-negative integer |
# Sample code
Suppose the collection students has the following records:
{ "firstName": "Yuanxin", "group": "a", "lastName": "Dong", "score": 84 }
{ "firstName": "Weijia", "group": "a", "lastName": "Wang", "score": 96 }
{ "firstName": "Chengxi", "group": "b", "lastName": "Li", "score": 80 }
Use indexOfCP to find the position of the character "a" in the field firstName:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
aStrIndex: $.indexOfCP(['$firstName', 'a'])
})
.end()
The results returned are as follows:
{ "aStrIndex": 2 }
{ "aStrIndex": 5 }
{ "aStrIndex": -1 }
# split
Delimits the array by the delimiter and removes the delimiter to return an array of substrings. If the string cannot be separated by a delimiter, returns the original string as the only element of the array.
# API Description
The syntax of split is as follows:
db.command.aggregate.split([<字符串表达式>, <分隔符表达式>])
String expressions and delimiter expressions can be any expression as long as it can be parsed as a string.
# Sample code
Suppose the collection students has the following records:
{ "birthday": "1999/12/12" }
{ "birthday": "1998/11/11" }
{ "birthday": "1997/10/10" }
Use split to separate the corresponding values of the birthday field in each record into arrays, each consisting of 3 elements representing the year, month, and day:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
birthday: $.split(['$birthday', '/'])
})
.end()
The results returned are as follows:
{ "birthday": [ "1999", "12", "12" ] }
{ "birthday": [ "1998", "11", "11" ] }
{ "birthday": [ "1997", "10", "10" ] }
# strLenBytes
Calculates and returns the number of utf-8 encoded bytes in the specified string.
# API Description
The syntax of strLenBytes is as follows:
db.command.aggregate.strLenBytes(<表达式>)
An expression is a valid expression as long as it can be parsed into a string.
# Sample code
Suppose the collection students has the following records:
{ "name": "dongyuanxin", "nickname": "心谭" }
Calculate the length in bytes of the corresponding values of the name field and nickname field with the help of strLenBytes:
const $ = db.command.aggregate
db
.collection('students')
.aggregate()
.project({
_id: 0,
nameLength: $.strLenBytes('$name'),
nicknameLength: $.strLenBytes('$nickname')
})
.end()
The returned results are as follows:
{ "nameLength": 11, "nicknameLength": 6 }
# strLenCP
Calculates and returns the UTF-8 code points of the specified string number.
# API Description
The syntax of strLenCP is as follows:
db.command.aggregate.strLenCP(<表达式>)
An expression is a valid expression as long as it can be parsed into a string.
# Sample code
Suppose the collection students has the following records:
{ "name": "dongyuanxin", "nickname": "心谭" }
Calculate UTF-8 code points number:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
nameLength: $.strLenCP('$name'),
nicknameLength: $.strLenCP('$nickname')
})
.end()
The returned results are as follows:
{ "nameLength": 11, "nicknameLength": 2 }
# strcasecmp
Compares two strings case-insensitively and returns the result of the comparison.
# API Description
The syntax of strcasecmp is as follows:
db.command.aggregate.strcasecmp([<表达式1>, <表达式2>])
expression1 and expression2 are valid as long as they can be parsed as strings.
The comparison results returned are 1, 0 and -1:
- 1:
expression1parsed string >expression2parsed string - 0:expression1parsed string =expression2parsed string - -1:expression2expression1parsed string <expression2` parsed string
# Sample code
Suppose the collection students has the following records:
{ "firstName": "Yuanxin", "group": "a", "lastName": "Dong", "score": 84 }
{ "firstName": "Weijia", "group": "a", "lastName": "Wang", "score": 96 }
{ "firstName": "Chengxi", "group": "b", "lastName": "Li", "score": 80 }
Use strcasecmp to compare the size of the firstName field value and the lastName field value:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
result: $.strcasecmp(['$firstName', '$lastName']),
})
.end()
The returned results are as follows:
{ "result": 1 }
{ "result": 1 }
{ "result": -1 }
# substr
Returns a substring of the specified length of the string starting at the specified position. It is an alias for db.command.aggregate.substrBytes, the latter is preferred.
# API Description
The syntax of substr is as follows:
db.command.aggregate.substr([<表达式1>, <表达式2>, <表达式3>])
expression1 is any valid expression that can be parsed as a string, expression2 and expression3 are any valid expression that can be parsed as a number.
If expression2 is negative, the result returned is "".
If expression3 is negative, the result returned is the substring starting at the position specified by expression2 and the rest after that.
# Sample code
Suppose the collection students has the following records:
{ "birthday": "1999/12/12", "firstName": "Yuanxin", "group": "a", "lastName": "Dong", "score": 84 }
{ "birthday": "1998/11/11", "firstName": "Weijia", "group": "a", "lastName": "Wang", "score": 96 }
{ "birthday": "1997/10/10", "firstName": "Chengxi", "group": "b", "lastName": "Li", "score": 80 }
With the help of substr, you can extract the year, month, and day information in birthday, the code is as follows:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
year: $.substr(['$birthday', 0, 4]),
month: $.substr(['$birthday', 5, 2]),
day: $.substr(['$birthday', 8, -1])
})
.end()
The results returned are as follows:
{ "day": "12", "month": "12", "year": "1999" }
{ "day": "11", "month": "11", "year": "1998" }
{ "day": "10", "month": "10", "year": "1997" }
# 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.
# API Description
The syntax of substrBytes is as follows:
db.command.aggregate.substrBytes([<表达式1>, <表达式2>, <表达式3>])
expression1 is any valid expression that can be parsed as a string, expression2 and expression3 are any valid expression that can be parsed as a number.
If expression2 is negative, the result returned is "".
If expression3 is negative, the result returned is the substring starting at the position specified by expression2 and the rest after that.
# Sample code
Suppose the collection students has the following records:
{ "birthday": "1999/12/12", "firstName": "Yuanxin", "group": "a", "lastName": "Dong", "score": 84 }
{ "birthday": "1998/11/11", "firstName": "Weijia", "group": "a", "lastName": "Wang", "score": 96 }
{ "birthday": "1997/10/10", "firstName": "Chengxi", "group": "b", "lastName": "Li", "score": 80 }
With the help of substrBytes, you can extract the year, month, and day information in birthday, the code is as follows:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
year: $.substrBytes(['$birthday', 0, 4]),
month: $.substrBytes(['$birthday', 5, 2]),
day: $.substrBytes(['$birthday', 8, -1])
})
.end()
The results returned are as follows:
{ "day": "12", "month": "12", "year": "1999" }
{ "day": "11", "month": "11", "year": "1998" }
{ "day": "10", "month": "10", "year": "1997" }
# 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.
# API Description
The syntax of substrCP is as follows:
db.command.aggregate.substrCP([<表达式1>, <表达式2>, <表达式3>])
expression1 is any valid expression that can be parsed as a string, expression2 and expression3 are any valid expression that can be parsed as a number.
If expression2 is negative, the result returned is "".
If expression3 is negative, the result returned is the substring starting at the position specified by expression2 and the rest after that.
# Sample code
Suppose the collection students has the following records:
{ "name": "dongyuanxin", "nickname": "心谭" }
With the help of substrCP, the first Chinese character of the nickname field value can be extracted:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
firstCh: $.substrCP(['$nickname', 0, 1])
})
.end()
The results returned are as follows:
{ "firstCh": "心" }
# toLower
Convert the string to lowercase and return.
# API Description
The syntax of toLower is as follows:
db.command.aggregate.toLower(表达式)
An expression is a valid expression as long as it can be parsed into a string. For example: $ + specify field.
# Sample code
Suppose the collection students has the following records:
{ "firstName": "Yuanxin", "group": "a", "lastName": "Dong", "score": 84 }
{ "firstName": "Weijia", "group": "a", "lastName": "Wang", "score": 96 }
{ "firstName": "Chengxi", "group": "b", "lastName": "Li", "score": 80 }
Convert the field value of firstName to lowercase with the help of toLower:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
result: $.toLower('$firstName'),
})
.end()
The results returned are as follows:
{ "result": "yuanxin" }
{ "result": "weijia" }
{ "result": "chengxi" }
# toUpper
Convert the string to uppercase and return.
# API Description
The syntax of toUpper is as follows:
db.command.aggregate.toUpper(表达式)
An expression is a valid expression as long as it can be parsed into a string. For example: $ + specify field.
# Sample code
Suppose the collection students has the following records:
{ "firstName": "Yuanxin", "group": "a", "lastName": "Dong", "score": 84 }
{ "firstName": "Weijia", "group": "a", "lastName": "Wang", "score": 96 }
{ "firstName": "Chengxi", "group": "b", "lastName": "Li", "score": 80 }
Use toUpper to convert the field value of lastName to uppercase:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.project({
_id: 0,
result: $.toUpper('$lastName'),
})
.end()
The results returned are as follows:
{ "result": "DONG" }
{ "result": "WANG" }
{ "result": "LI" }
# Grouping operation method
# addToSet
Aggregation operator. Adds a value to the array, and does nothing if the value already exists in the array. It can only be used in group stage.
# API Description
The addToSet syntax is as follows:
db.command.aggregate.addToSet(<表达式>)
An expression is a string of the form $ + specified field. If the value of the specified field is an array, the entire array is treated as one element.
# Sample code
Suppose the collection passages has the following records:
{ "category": "web", "tags": [ "JavaScript", "CSS" ], "title": "title1" }
{ "category": "System", "tags": [ "C++", "C" ], "title": "title2" }
non-array field
The type of the corresponding value of category for each record is non-array, use addToSet to count all categories:
const $ = db.command.aggregate
let res = await db
.collection('passages')
.aggregate()
.group({
_id: null,
categories: $.addToSet('$category')
})
.end()
The results returned are as follows:
{ "_id": null, "categories": [ "System", "web" ] }
Array field
The type of the corresponding value of tags for each record is an array, and the array will not be automatically expanded:
const $ = db.command.aggregate
let res = await db
.collection('passages')
.aggregate()
.group({
_id: null,
tagsList: $.addToSet('$tags')
})
.end()
The results returned are as follows:
{ "_id": null, "tagsList": [ [ "C++", "C" ], [ "JavaScript", "CSS" ] ] }
# avg
Returns the average value of the data corresponding to the specified field in a set of collections.
# API Description
The syntax of avg is as follows:
db.command.aggregate.avg(<number>)
In addition to numeric constants, the value passed to avg can also be any expression that eventually resolves to a number. It ignores non-numeric values.
# Sample code
Suppose the collection students has the following records:
{ "group": "a", "name": "stu1", "score": 84 }
{ "group": "a", "name": "stu2", "score": 96 }
{ "group": "b", "name": "stu3", "score": 80 }
{ "group": "b", "name": "stu4", "score": 100 }
With avg it is possible to calculate the average of the score of all records:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.group({
_id: null,
average: $.avg('$score')
})
.end()
The results returned are as follows:
{ "_id": null, "average": 90 }
# first
Returns the value corresponding to the specified field in the first record of a set. This operation only makes sense if the set of collections is sorted by some definition ( sort ).
# API Description
The syntax of first is as follows:
db.command.aggregate.first(<表达式>)
An expression is a string of the form $ + specified field.
first can only be used in the group phase, and only makes sense in conjunction with sort.
# Sample code
Suppose the collection students has the following records:
{ "group": "a", "name": "stu1", "score": 84 }
{ "group": "a", "name": "stu2", "score": 96 }
{ "group": "b", "name": "stu3", "score": 80 }
{ "group": "b", "name": "stu4", "score": 100 }
If you need to get the minimum value of score in all records, you can first sort all records according to score, and then take the first of the first record.
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.sort({
score: 1
})
.group({
_id: null,
min: $.first('$score')
})
.end()
The returned data results are as follows:
{ "_id": null, "min": 80 }
# last
Returns the value corresponding to the specified field in the last record of a set. This operation only makes sense if the set of collections is sorted by some definition ( sort ).
# API Description
The syntax of last is as follows:
db.command.aggregate.last(<表达式>)
An expression is a string of the form $ + specified field.
last can only be used in the group phase, and only makes sense with sort.
# Sample code
Suppose the collection students has the following records:
{ "group": "a", "name": "stu1", "score": 84 }
{ "group": "a", "name": "stu2", "score": 96 }
{ "group": "b", "name": "stu3", "score": 80 }
{ "group": "b", "name": "stu4", "score": 100 }
If you need to get the maximum value of score in all records, you can first sort all records according to score, and then take the last of the last record.
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.sort({
score: 1
})
.group({
_id: null,
max: $.last('$score')
})
.end()
The returned data results are as follows:
{ "_id": null, "max": 100 }
# max
Returns the maximum value of a set of values.
# API Description
The syntax of max is as follows:
db.command.aggregate.max(<表达式>)
An expression is a string of the form $ + specified field.
# Sample code
Suppose the collection students has the following records:
{ "group": "a", "name": "stu1", "score": 84 }
{ "group": "a", "name": "stu2", "score": 96 }
{ "group": "b", "name": "stu3", "score": 80 }
{ "group": "b", "name": "stu4", "score": 100 }
With the help of max, you can count the highest value of grades in different groups ( group ), the code is as follows:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.group({
_id: '$group',
maxScore: $.max('$score')
})
.end()
The returned data results are as follows:
{ "_id": "b", "maxScore": 100 }
{ "_id": "a", "maxScore": 96 }
...
# mergeObjects
see mergeObjects
# min
Returns the minimum value of a set of values.
# API Description
The syntax for min is as follows:
db.command.aggregate.min(<表达式>)
An expression is a string of the form $ + specified field.
# Sample code
Suppose the collection students has the following records:
{ "group": "a", "name": "stu1", "score": 84 }
{ "group": "a", "name": "stu2", "score": 96 }
{ "group": "b", "name": "stu3", "score": 80 }
{ "group": "b", "name": "stu4", "score": 100 }
With the help of min, you can count the lowest value of grades in different groups ( group ), the code is as follows:
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.group({
_id: '$group',
minScore: $.min('$score')
})
.end()
The returned data results are as follows:
{ "_id": "b", "minScore": 80 }
{ "_id": "a", "minScore": 84 }
# push
In the group stage, returns a group of arrays consisting of the columns specified by the expression and the corresponding values.
# API Description
The push syntax is as follows:
db.command.aggregate.push({
<字段名1>: <指定字段1>,
<字段名2>: <指定字段2>,
...
})
# Sample code
Suppose the collection students has the following records:
{ "group": "a", "name": "stu1", "score": 84 }
{ "group": "a", "name": "stu2", "score": 96 }
{ "group": "b", "name": "stu3", "score": 80 }
{ "group": "b", "name": "stu4", "score": 100 }
With the push operation, for all records in different groups ( group ), aggregate all the data and put it into a new field, further structuring and semantic data.
const $ = db.command.aggregate
let res = await db
.collection('students')
.aggregate()
.group({
_id: '$group',
students: $.push({
name: '$name',
score: '$score'
})
})
.end()
The output is as follows:
{ "_id": "b", "students": [{ "name": "stu3", "score": 80 }, { "name": "stu4", "score": 100 }] }
{ "_id": "a", "students": [{ "name": "stu1", "score": 84 }, { "name": "stu2", "score": 96 }] }
# stdDevPop
Returns the standard deviation of the corresponding values for a set of fields.
# API Description
stdDevPop is used as follows:
db.command.aggregate.stdDevPop(<表达式>)
The expression passes in the specified field, and the data type of the value corresponding to the specified field must be number , otherwise the result will return null.
# Sample code
Suppose the records of the set students are as follows: The grades of the students in the a group are 84 and 96, respectively, and the grades of the students in the b group are 80 and 100, respectively.
{ "group":"a", "score":84 }
{ "group":"a", "score":96 }
{ "group":"b", "score":80 }
{ "group":"b", "score":100 }
You can use stdDevPop to calculate the standard deviation of the scores of the two groups of students a and b respectively, in order to compare which group of students has more stable scores. code show as below:
const $ = db.command.aggregate
let res = await db.collection('students').aggregate()
.group({
_id: '$group',
stdDev: $.stdDevPop('$score')
})
.end()
The returned data results are as follows:
{ "_id": "b", "stdDev": 10 }
{ "_id": "a", "stdDev": 6 }
# stdDevSamp
Calculates the sample standard deviation of the input values. Use db.command.aggregate.stdDevPop instead if the input value represents the population of data, or does not generalize more data.
# API Description
stdDevSamp is used as follows:
db.command.aggregate.stdDevSamp(<表达式>)
The expression passed in is the specified field, and stdDevSamp will automatically ignore non-numeric values. If all values of the specified field are non-numeric, the result returns null.
# Sample code
Suppose the collection students has the following records:
{ "score": 80 }
{ "score": 100 }
The standard sample deviation of grades can be calculated using stdDevSamp. code show as below:
const $ = db.command.aggregate
let res = await db.collection('students').aggregate()
.group({
_id: null,
ageStdDev: $.stdDevSamp('$score')
})
.end()
The returned data results are as follows:
{ "_id": null, "ageStdDev": 14.142135623730951 }
If you add a new record to the collection students, its score field type is string:
{ "score": "aa" }
When using the above code to calculate standard sample deviation, stdDevSamp will automatically ignore records whose type is not number, and the returned result will remain unchanged.
# sum
Calculates and returns the sum of all values in a set of fields.
# API Description
sum is used in the following form:
db.command.aggregate.sum(<表达式>)
The expression can be passed in a specified field, or a list of specified fields can be passed in. sum automatically ignores non-numeric values. If all values under the field are non-numeric, the result returns 0. If a numeric constant is passed in, the value of this field in all records will be given the given constant, added during aggregation, and the final value will be the number of input records multiplied by the constant.
# Sample code
Suppose the records representing the collection of goods goods are as follows: price represents the sales of the goods, cost represents the cost of the goods
{ "cost": -10, "price": 100 }
{ "cost": -15, "price": 1 }
{ "cost": -10, "price": 10 }
Separate field
With the help of sum you can calculate the total sales of all products, the code is as follows:
const $ = db.command.aggregate
let res = await db
.collection('goods')
.aggregate()
.group({
_id: null,
totalPrice: $.sum('$price')
})
.end()
The result of the returned data is as follows: Sales is 111
{ "_id": null, "totalPrice": 111 }
Field List
If you need to calculate the total profit of all items, you need to add the cost and price of each record to get the profit of the item corresponding to this record. Finally, calculate the total profit of all commodities.
With sum, the code is as follows:
const $ = db.command.aggregate
let res = await db
.collection('goods')
.aggregate()
.group({
_id: null,
totalProfit: $.sum(
$.sum(['$price', '$cost'])
)
})
.end()
The result of the returned data is as follows: The total profit is 76
{ "_id": null, "totalProfit": 76 }
# variable operator
# let
Custom variables, and used in the specified expression, the returned result is the result of the expression.
# API Description
The syntax of let is as follows:
db.command.aggregate.let({
vars: {
<变量1>: <变量表达式>,
<变量2>: <变量表达式>,
...
},
in: <结果表达式>
})
Multiple variables can be defined in vars, the value of the variable is calculated from the variable expression, and the defined variable can only be accessed by the result expression in in.
When accessing custom variables in the result expression of in, precede the variable name with a double dollar sign ( $$ ) and enclose it in quotes.
# Sample code
Suppose the records representing the collection of goods goods are as follows: price represents the price of the goods, discount represents the discount rate of the goods, and cost represents the cost of the goods
{ "cost": -10, "discount": 0.95, "price": 100 }
{ "cost": -15, "discount": 0.98, "price": 1 }
{ "cost": -10, "discount": 1, "price": 10 }
With let, you can define and calculate the actual selling price of each item, and assign it to the custom variable priceTotal. Finally, priceTotal and cost are summed ( sum ) to get the profit of each item.
code show as below:
const $ = db.command.aggregate
let res = await db
.collection('goods')
.aggregate()
.project({
profit: $.let({
vars: {
priceTotal: $.multiply(['$price', '$discount'])
},
in: $.sum(['$$priceTotal', '$cost'])
})
})
.end()
The returned data results are as follows:
{ "profit": 85 }
{ "profit": -14.02 }
{ "profit": 0 }