JQL 常用运算方法

uniCloud的云数据库，提供了一批强大的运算方法。这些方法是数据库执行的，而不是云函数执行的。

这些运算方法是与数据查询搭配使用的，它们可以对字段的值或字段的值的一部分进行运算，将运算后的结果返回给查询请求。

数据库运算方法，提供了比传统SQL更大强大和灵活的查询。可以实现更多功能、可以一次性查询出期待的结果。不必多次查库多次运算，那样不仅代码复杂，而且会造成多次查库性能下降；如果使用计费云空间，使用这些方法还可以减少数据库查询次数。

比如sum()方法，可以对多行记录的某个字段值求和、可以对单行记录的若干字段的值求和，如果字段是一个数组，还可以对数组的各项求和。

为方便书写，JQL内将数据库运算方法的用法进行了简化（相对于原始数据库运算方法写法而言），主要是参数摊平，以字符串方式表达。以下是可以在JQL中使用的数据库运算方法

数据库运算方法汇总

完整运算方法列表

运算方法	用途	JQL简化用法	说明
abs	返回一个数字的绝对值	abs(表达式)	-
add	将数字相加或将数字加在日期上。如果参数中的其中一个值是日期，那么其他值将被视为毫秒数加在该日期上	add(表达式1,表达式2)	-
ceil	向上取整	ceil(表达式)	-
divide	传入被除数和除数，求商	divide(表达式1,表达式2)	-
exp	取 e（自然对数的底数，欧拉数） 的 n 次方	exp(表达式)	-
floor	向下取整	floor(表达式)	-
ln	计算给定数字在自然对数值	ln(表达式)	-
log	计算给定数字在给定对数底下的 log 值	log(表达式1,表达式2)	-
log10	计算给定数字在对数底为 10 下的 log 值	log10(表达式)	-
mod	取模运算，第一个数字是被除数，第二个数字是除数	mod(表达式1,表达式2)	-
multiply	取传入的数字参数相乘的结果	multiply(表达式1,表达式2)	-
pow	求给定基数的指数次幂	pow(表达式1,表达式2)	-
sqrt	求平方根	sqrt(表达式1,表达式2)	-
subtract	将两个数字相减然后返回差值，或将两个日期相减然后返回相差的毫秒数，或将一个日期减去一个数字返回结果的日期。	subtract(表达式1,表达式2)	-
trunc	将数字截断为整形	trunc(表达式)	-
arrayElemAt	返回在指定数组下标的元素	arrayElemAt(表达式1,表达式2)	-
arrayToObject	将一个数组转换为对象	arrayToObject(表达式)	-
concatArrays	将多个数组拼接成一个数组	concatArrays(表达式1,表达式2)	-
filter	根据给定条件返回满足条件的数组的子集	filter(input,as,cond)	-
in	给定一个值和一个数组，如果值在数组中则返回 true，否则返回 false	in(表达式1,表达式2)	-
indexOfArray	在数组中找出等于给定值的第一个元素的下标，如果找不到则返回 -1	indexOfArray(表达式1,表达式2)	-
isArray	判断给定表达式是否是数组，返回布尔值	isArray(表达式)	-
map	类似 JavaScript Array 上的 map 方法，将给定数组的每个元素按给定转换方法转换后得出新的数组	map(input,as,in)	-
objectToArray	将一个对象转换为数组。方法把对象的每个键值对都变成输出数组的一个元素，元素形如 { k: <key>, v: <value> }	objectToArray(表达式)	-
range	返回一组生成的序列数字。给定开始值、结束值、非零的步长，range 会返回从开始值开始逐步增长、步长为给定步长、但不包括结束值的序列。	range(表达式1,表达式2)	-
reduce	类似 JavaScript 的 reduce 方法，应用一个表达式于数组各个元素然后归一成一个元素	reduce(input,initialValue,in)	-
reverseArray	返回给定数组的倒序形式	reverseArray(表达式)	-
size	返回数组长度	size(表达式)	-
slice	类似 JavaScritp 的 slice 方法。返回给定数组的指定子集	slice(表达式1,表达式2)	-
zip	把二维数组的第二维数组中的相同序号的元素分别拼装成一个新的数组进而组装成一个新的二维数组。	zip(inputs,useLongestLength,defaults)	-
and	给定多个表达式，and 仅在所有表达式都返回 true 时返回 true，否则返回 false	and(表达式1,表达式2)	-
not	给定一个表达式，如果表达式返回 true，则 not 返回 false，否则返回 true。注意表达式不能为逻辑表达式（and、or、nor、not）	not(表达式)	-
or	给定多个表达式，如果任意一个表达式返回 true，则 or 返回 true，否则返回 false	or(表达式1,表达式2)	-
cmp	给定两个值，返回其比较值。如果第一个值小于第二个值，返回 -1 如果第一个值大于第二个值，返回 1 如果两个值相等，返回 0	cmp(表达式1,表达式2)	-
eq	匹配两个值，如果相等则返回 true，否则返回 false	eq(表达式1,表达式2)	-
gt	匹配两个值，如果前者大于后者则返回 true，否则返回 false	gt(表达式1,表达式2)	-
gte	匹配两个值，如果前者大于或等于后者则返回 true，否则返回 false	gte(表达式1,表达式2)	-
lt	匹配两个值，如果前者小于后者则返回 true，否则返回 false	lt(表达式1,表达式2)	-
lte	匹配两个值，如果前者小于或等于后者则返回 true，否则返回 false	lte(表达式1,表达式2)	-
neq	匹配两个值，如果不相等则返回 true，否则返回 false	neq(表达式1,表达式2)	-
cond	计算布尔表达式1，成立返回表达式2，否则返回表达式3	cond(表达式1,表达式2,表达式3)	-
ifNull	计算给定的表达式，如果表达式结果为 null、undefined 或者不存在，那么返回一个替代值；否则返回原值。	ifNull(表达式1,表达式2)	-
switch	根据给定的 switch-case-default 计算返回值	switch(branches,default)	-
dateFromParts	给定日期的相关信息，构建并返回一个日期对象	dateFromParts(year,month,day,hour,minute,second,millisecond,timezone)	-
isoDateFromParts	给定日期的相关信息，构建并返回一个日期对象	isoDateFromParts(isoWeekYear,isoWeek,isoDayOfWeek,hour,minute,second,millisecond,timezone)	-
dateFromString	将一个日期/时间字符串转换为日期对象	dateFromString(dateString,format,timezone,onError,onNull)	-
dateToString	根据指定的表达式将日期对象格式化为符合要求的字符串	dateToString(date,format,timezone,onNull)	-
dayOfMonth	返回日期字段对应的天数（一个月中的哪一天），是一个介于 1 至 31 之间的数字	dayOfMonth(date,timezone)	-
dayOfWeek	返回日期字段对应的天数（一周中的第几天），是一个介于 1（周日）到 7（周六）之间的整数	dayOfWeek(date,timezone)	-
dayOfYear	返回日期字段对应的天数（一年中的第几天），是一个介于 1 到 366 之间的整数	dayOfYear(date,timezone)	-
hour	返回日期字段对应的小时数，是一个介于 0 到 23 之间的整数。	hour(date,timezone)	-
isoDayOfWeek	返回日期字段对应的 ISO 8601 标准的天数（一周中的第几天），是一个介于 1（周一）到 7（周日）之间的整数。	isoDayOfWeek(date,timezone)	-
isoWeek	返回日期字段对应的 ISO 8601 标准的周数（一年中的第几周），是一个介于 1 到 53 之间的整数。	isoWeek(date,timezone)	-
isoWeekYear	返回日期字段对应的 ISO 8601 标准的天数（一年中的第几天）	isoWeekYear(date,timezone)	-
millisecond	返回日期字段对应的毫秒数，是一个介于 0 到 999 之间的整数	millisecond(date,timezone)	-
minute	返回日期字段对应的分钟数，是一个介于 0 到 59 之间的整数	minute(date,timezone)	-
month	返回日期字段对应的月份，是一个介于 1 到 12 之间的整数	month(date,timezone)	-
second	返回日期字段对应的秒数，是一个介于 0 到 59 之间的整数，在特殊情况下（闰秒）可能等于 60	second(date,timezone)	-
week	返回日期字段对应的周数（一年中的第几周），是一个介于 0 到 53 之间的整数	week(date,timezone)	-
year	返回日期字段对应的年份	year(date,timezone)	-
timestampToDate	传入一个时间戳，返回对应的日期对象	timestampToDate(timestamp)	仅JQL字符串内支持，HBuilderX 3.1.0起支持
literal	直接返回一个值的字面量，不经过任何解析和处理	literal(表达式)	-
mergeObjects	将多个对象合并为单个对象	mergeObjects(表达式1,表达式2)	-
allElementsTrue	输入一个数组，或者数组字段的表达式。如果数组中所有元素均为真值，那么返回 true，否则返回 false。空数组永远返回 true	allElementsTrue(表达式1,表达式2)	-
anyElementTrue	输入一个数组，或者数组字段的表达式。如果数组中任意一个元素为真值，那么返回 true，否则返回 false。空数组永远返回 false	anyElementTrue(表达式1,表达式2)	-
setDifference	输入两个集合，输出只存在于第一个集合中的元素	setDifference(表达式1,表达式2)	-
setEquals	输入两个集合，判断两个集合中包含的元素是否相同（不考虑顺序、去重）	setEquals(表达式1,表达式2)	-
setIntersection	输入两个集合，输出两个集合的交集	setIntersection(表达式1,表达式2)	-
setIsSubset	输入两个集合，判断第一个集合是否是第二个集合的子集	setIsSubset(表达式1,表达式2)	-
setUnion	输入两个集合，输出两个集合的并集	setUnion(表达式1,表达式2)	-
concat	连接字符串，返回拼接后的字符串	concat(表达式1,表达式2)	-
indexOfBytes	在目标字符串中查找子字符串，并返回第一次出现的 UTF-8 的字节索引（从0开始）。如果不存在子字符串，返回 -1	indexOfBytes(表达式1,表达式2)	-
indexOfCP	在目标字符串中查找子字符串，并返回第一次出现的 UTF-8 的 code point 索引（从0开始）。如果不存在子字符串，返回 -1	indexOfCP(表达式1,表达式2)	-
split	按照分隔符分隔数组，并且删除分隔符，返回子字符串组成的数组。如果字符串无法找到分隔符进行分隔，返回原字符串作为数组的唯一元素	split(表达式1,表达式2)	-
strLenBytes	计算并返回指定字符串中 utf-8 编码的字节数量	strLenBytes(表达式)	-
strLenCP	计算并返回指定字符串的UTF-8 code points 数量	strLenCP(表达式)	-
strcasecmp	对两个字符串在不区分大小写的情况下进行大小比较，并返回比较的结果	strcasecmp(表达式1,表达式2)	-
substr	返回字符串从指定位置开始的指定长度的子字符串	substr(表达式1,表达式2)	-
substrBytes	返回字符串从指定位置开始的指定长度的子字符串。子字符串是由字符串中指定的 UTF-8 字节索引的字符开始，长度为指定的字节数	substrBytes(表达式1,表达式2)	-
substrCP	返回字符串从指定位置开始的指定长度的子字符串。子字符串是由字符串中指定的 UTF-8 字节索引的字符开始，长度为指定的字节数	substrCP(表达式1,表达式2)	-
toLower	将字符串转化为小写并返回	toLower(表达式)	-
toUpper	将字符串转化为大写并返回	toUpper(表达式)	-
addToSet	聚合运算符。向数组中添加值，如果数组中已存在该值，不执行任何操作。它只能在 group stage 中使用	addToSet(表达式)	-
avg	返回指定表达式对应数据的平均值	avg(表达式)	-
first	返回指定字段在一组集合的第一条记录对应的值。仅当这组集合是按照某种定义排序（ sort ）后，此操作才有意义	first(表达式)	-
last	返回指定字段在一组集合的最后一条记录对应的值。仅当这组集合是按照某种定义排序（ sort ）后，此操作才有意义。	last(表达式)	-
max	返回一组数值的最大值	max(表达式)	-
min	返回一组数值的最小值	min(表达式)	-
push	返回一组中表达式指定列与对应的值，一起组成的数组	push(表达式)	-
stdDevPop	返回一组字段对应值的标准差	stdDevPop(表达式)	-
stdDevSamp	计算输入值的样本标准偏差	stdDevSamp(表达式)	-
sum	在groupField内返回一组字段所有数值的总和，非groupField内返回一个数组所有元素的和	sum(表达式)	-
let	自定义变量，并且在指定表达式中使用，返回的结果是表达式的结果	let(vars,in)	-

以上操作符还可以组合使用

例：数据表article内有以下数据

{
  "_id": "1",
  "publish_date": 1611141512751,
  "content": "hello uniCloud content 01",
  "content": "hello uniCloud title 01",
}

{
  "_id": "2",
  "publish_date": 1611141512752,
  "content": "hello uniCloud content 02",
  "content": "hello uniCloud title 02",
}

{
  "_id": "3",
  "publish_date": 1611141512753,
  "content": "hello uniCloud content 03",
  "content": "hello uniCloud title 03",
}

可以通过以下查询将publish_date字段从时间戳转为2021-01-20形式，然后进行按天进行统计

const res = await db.collection('article')
.groupBy('dateToString(add(new Date(0),publish_date),"%Y-%m-%d","+0800") as publish_date_str')
.groupField('count(*) as total')
.get()

上述代码使用add方法将publish_date时间戳转为日期类型，再用dateToString将上一步的日期按照时区'+0800'（北京时间），格式化为4位年-2位月-2位日格式，完整格式化参数请参考dateToString。

上述代码执行结果为

res = {
  result: {
    data: [{
      publish_date_str: '2021-01-20',
      total: 3
    }]
  }
}

注意

运算方法中仅数据库字段可以直接去除引号作为变量书写，其他字符串仍要写成字符串形式

例：

数据库内有以下数据：

{
  "_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 ]
}

云函数内对以下数据中的sales字段取整

const db = uniCloud.database()
const $ = db.command.aggregate
let res = await db.collection('stats').aggregate()
  .project({
    truncated: $.map({
      input: '$sales',
      as: 'num',
      in: $.trunc('$$num'),
    })
  })
  .end()

JQL语法内同样功能的实现

const db = uniCloud.database()
const res = await db.collection('stats')
.field('map(sales,"num",trunc("$$num")) as truncated')
.get()

分组运算方法

分组运算方法是专用于统计汇总的数据库运算方法。它也是数据库的方法，而不是js的方法。

等同于mongoDB累计器操作符概念

groupField内可使用且仅能使用如下运算方法。

操作符	用途	用法	说明
addToSet	向数组中添加值，如果数组中已存在该值，不执行任何操作	addToSet(表达式)	-
avg	返回指定表达式对应数据的平均值	avg(表达式)	-
first	返回指定字段在一组集合的第一条记录对应的值。仅当这组集合是按照某种定义排序（ sort ）后，此操作才有意义	first(表达式)	-
last	返回指定字段在一组集合的最后一条记录对应的值。仅当这组集合是按照某种定义排序（ sort ）后，此操作才有意义。	last(表达式)	-
max	返回一组数值的最大值	max(表达式)	-
min	返回一组数值的最小值	min(表达式)	-
push	返回一组中表达式指定列与对应的值，一起组成的数组	push(表达式)	-
stdDevPop	返回一组字段对应值的标准差	stdDevPop(表达式)	-
stdDevSamp	计算输入值的样本标准偏差	stdDevSamp(表达式)	-
sum	返回一组字段所有数值的总和	sum(表达式)	-
mergeObjects	将一组对象合并为一个对象	mergeObjects(表达式)	在groupField内使用时仅接收一个参数

常用运算方法示例

以下列举常用的运算方法在 JQL 中的应用

算术运算方法

算术表达式对数字执行数学运算。一些算术表达式也可以支持日期算术。

abs

返回一个数字的绝对值。

示例集合 test 数据示例：

{ _id: 1, start: 5 }
{ _id: 2, start: -4 }
{ _id: 3, start: -9 }
{ _id: 4, start: 6 }

计算 start 的绝对值：

db.collection('test').field('abs(satrt) as absStart').get()

执行结果：

{ _id: 1, absStart: 5 }
{ _id: 2, absStart: 4 }
{ _id: 3, absStart: 9 }
{ _id: 4, absStart: 6 }

add

将数字相加或将数字加在日期上。如果参数中的其中一个值是日期，那么其他值将被视为毫秒数加在该日期上。

示例集合 test 数据示例：

{ _id: 1, price: 5, fee: 1, date: 1667804052630 }
{ _id: 2, price: 4, fee: 5, date: 1667804052630 }
{ _id: 3, price: 9, fee: 2, date: 1667804052630 }
{ _id: 4, price: 6, fee: 8, date: 1667804052630 }

示例：

加数字

db.collection('test').field('add(price, fee) as total').get()

执行结果：

{ _id: 1, total: 6 }
{ _id: 2, total: 9 }
{ _id: 3, total: 11 }
{ _id: 4, total: 14 }

在当前日期上加上1天

db.collection('test').field('add(date, 24 * 60 * 60 * 1000) as newDate').get()

执行结果：

{ _id: 1, newDate: 1667890452630 }
{ _id: 2, newDate: 1667890452630 }
{ _id: 3, newDate: 1667890452630 }
{ _id: 4, newDate: 1667890452630 }

ceil

向上取整。

集合 test 数据如下：

{ _id: 1, value: 9.25 }
{ _id: 2, value: 8.73 }
{ _id: 3, value: 4.32 }
{ _id: 4, value: -5.34 }

对 value 字段向上取整：

db.collection('test').field('ceil(value) as ceilingVaule').get()

执行结果：

{ _id: 1, value: 10 }
{ _id: 2, value: 10 }
{ _id: 3, value: 5 }
{ _id: 4, value: -5 }

divide

传入被除数和除数，求商。

示例集合 test 数据如下：

{ "_id" : 1, "name" : "A", "hours" : 80, "resources" : 7 }
{ "_id" : 2, "name" : "B", "hours" : 40, "resources" : 4 }

将hours字段除以8以计算工作日数：

db.collection('test').field('divide(hours, 8) as workdays').get()

执行结果：

{ "_id" : 1, "workdays" : 10 }
{ "_id" : 2, "workdays" : 8 }

floor

向下取整。

示例集合 test 数据如下：

{ _id: 1, value: 9.25 }
{ _id: 2, value: 8.73 }
{ _id: 3, value: 4.32 }
{ _id: 4, value: -5.34 }

对 value 进行向下取整：

db.collection('test').field('floor(value) as floorValue').get()

执行结果：

{ _id: 1, floorValue: 9 }
{ _id: 2, floorValue: 8 }
{ _id: 3, floorValue: 4 }
{ _id: 4, floorValue: -6 }

ln

计算给定数字在自然对数值。

示例集合 test 数据如下：

{ _id: 1, year: "2000", sales: 8700000 }
{ _id: 2, year: "2005", sales: 5000000 }
{ _id: 3, year: "2010", sales: 6250000 }

转换 test 数据示例如下：

db.collection('test').field('year as x, ln(sales) as y').get()

执行结果：

{ "_id" : 1, "x" : "2000", "y" : 15.978833583624812 }
{ "_id" : 2, "x" : "2005", "y" : 15.424948470398375 }
{ "_id" : 3, "x" : "2010", "y" : 15.648092021712584 }

log

计算给定数字在给定对数底下的 log 值。

示例集合 test 数据如下：

{ _id: 1, positiveInt: 5 }
{ _id: 2, positiveInt: 2 }
{ _id: 3, positiveInt: 23 }
{ _id: 4, positiveInt: 10 }

在计算中使用 $ log_2 $ 来确定表示的值所需的位数 positiveInt：

db.collection('test').field('floor(add(log(positiveInt, 2))) as bitsNeeded')

执行结果：

{ "_id" : 1, "bitsNeeded" : 3 }
{ "_id" : 2, "bitsNeeded" : 2 }
{ "_id" : 3, "bitsNeeded" : 5 }
{ "_id" : 4, "bitsNeeded" : 4 }

log10

计算给定数字在对数底为 10 下的 log 值。

示例集合 test 数据如下：

{ _id: 1, H3O: 0.0025 }
{ _id: 2, H3O: 0.001 }
{ _id: 3, H3O: 0.02 }

以下示例计算样品的pH值：

db.collection('test').field('multiply(1, log10(H3O)) as pH').get()

执行结果：

{ "_id" : 1, "pH" : 2.6020599913279625 }
{ "_id" : 2, "pH" : 3 }
{ "_id" : 3, "pH" : 1.6989700043360187 }

mod

取模运算，第一个数字是被除数，第二个数字是除数。

示例集合 test 数据如下：

{ "_id" : 1, "project" : "A", "hours" : 80, "tasks" : 7 }
{ "_id" : 2, "project" : "B", "hours" : 40, "tasks" : 4 }

使用 mod 表达式返回 hours 字段的其余部分除以 tasks 字段：

db.collection('test').field('mod(hours, tasks) as remainder').get()

执行结果：

{ "_id" : 1, "remainder" : 3 }
{ "_id" : 2, "remainder" : 0 }

multiply

取传入的数字参数相乘的结果。

示例集合 test 数据如下：

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity": 2 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity": 1 }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity": 10 }

使用 multiply 表达式将 price 和 quantity 字段相乘：

db.collection('test').field('multiply(price, quantity) as total').get()

执行结果：

{ "_id" : 1, "total" : 20 }
{ "_id" : 2, "total" : 20 }
{ "_id" : 3, "total" : 50 }

pow

求给定基数的指数次幂。

示例集合 test 数据如下：

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity": 2 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity": 1 }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity": 10 }

使用 multiply 表达式将 price 和 quantity 字段相乘：

db.collection('test').field('multiply(price, quantity) as total').get()

执行结果：

{ "_id" : 1, "total" : 20 }
{ "_id" : 2, "total" : 20 }
{ "_id" : 3, "total" : 50 }

sqrt

计算平方根。

示例集合 test 数据如下：

{ _id: 1, p1: { x: 5, y: 8 }, p2: { x: 0, y: 5} }
{ _id: 2, p1: { x: -2, y: 1 }, p2: { x: 1, y: 5} }
{ _id: 3, p1: { x: 4, y: 4 }, p2: { x: 4, y: 0} }

以下示例计算 p1 和 p2 之间的距离：

db.collection('test').field('sqrt(add(pow(subtract(p2.y, p1.y), 2), pow(subtract(p2.x, p1.x), 2))) as distance').get()

执行结果：

{ "_id" : 1, "distance" : 5.830951894845301 }
{ "_id" : 2, "distance" : 5 }
{ "_id" : 3, "distance" : 4 }

subtract

将两个数字相减然后返回差值，或将两个日期相减然后返回相差的毫秒数，或将一个日期减去一个数字返回结果的日期。

示例集合 test 数据如下：

{ "_id" : 1, "item" : "abc", "price" : 10, "fee" : 2, "discount" : 5, "date" : 1393660800000 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "fee" : 1, "discount" : 2, "date" : 1393664400000 }

减数字

db.collection('test').field('subtract(add(price, fee), discount) as total').get()

执行结果：

{ "_id" : 1, "item" : "abc", "total" : 7 }
{ "_id" : 2, "item" : "jkl", "total" : 19 }

从当前日期减去1天

db.collection('test').field('subtract(date, 24 * 60 * 60 * 1000) as dateDifference').get()

执行结果：

{ "_id" : 1, "item" : "abc", "dateDifference" : 1393747200000 }
{ "_id" : 2, "item" : "jkl", "dateDifference" : 1393750800000 }

trunc

将数字截断为整型。

示例集合 test 数据如下：

{ _id: 1, value: 19.25 }
{ _id: 2, value: 28.73 }
{ _id: 3, value: 34.32 }
{ _id: 4, value: -45.34 }

截断 value 为整数的信息：

db.collection('test').field('trunc(value) as truncatedValue').get()

执行结果：

{ "_id" : 1, "truncatedValue" : 19 }
{ "_id" : 2, "truncatedValue" : 28 }
{ "_id" : 3, "truncatedValue" : 34 }
{ "_id" : 4, "truncatedValue" : -45 }

数组运算方法

arrayElemAt

返回在指定数组下标的元素。

示例集合users包含以下文档：

{"_id":1,"name":"dave123",favorites:["chocolate","cake","butter","apples"]}
{"_id":2,"name":"li",favorites:["apples","pudding","pie"]}
{"_id":3,"name":"ahn",favorites:["pears","pecans","chocolate","cherries"]}
{"_id":4,"name":"ty",favorites:["ice cream"]}

返回favorites数组中的第一个和最后一个元素：

db.collection('users').field('arrayElemAt(favorites, 0) as first, arrayElemAt(favorites, -1) as last').get()

执行结果：

{"_id":1,"first":"chocolate","last":"apples"}
{"_id":2,"first":"apples","last":"pie"}
{"_id":3,"first":"pears","last":"cherries"}
{"_id":4,"first":"ice cream","last":"ice cream"}

arrayToObject

将一个数组转换为对象。

示例集合inventory包含以下文档的集合：

{"_id":1,"item":"ABC1",dimensions:[{"k":"l","v":25},{"k":"w","v":10},{"k":"uom","v":"cm"}]}
{"_id":2,"item":"ABC2",dimensions:[["l",50],["w",25],["uom","cm"]]}
{"_id":3,"item":"ABC3",dimensions:[["l",25],["l","cm"],["l",50]]}

以下将dimensions字段作为对象返回：

db.collection('inventory').field('item, arrayToObject(dimensions) as dimensionsObject').get()

执行结果：

{"_id":1,"item":"ABC1","dimensionsObject":{"l":25,"w":10,"uom":"cm"}}
{"_id":2,"item":"ABC2","dimensionsObject":{"l":50,"w":25,"uom":"cm"}}
{"_id":3,"item":"ABC3","dimensionsObject":{"l":50}}

concatArrays

将多个数组拼接成一个数组。

示例集合warehouses包含以下文档：

{"_id":1,instock:["chocolate"],ordered:["butter","apples"]}
{"_id":2,instock:["apples","pudding","pie"]}
{"_id":3,instock:["pears","pecans"],ordered:["cherries"]}
{"_id":4,instock:["ice cream"],ordered:[]}

以下示例将instock和ordered数组串联在一起：

db.collection('warehouses').field('concatArrays(instock, ordered) as items').get()

执行结果：

{"_id":1,"items":["chocolate","butter","apples"]}
{"_id":2,"items":null}
{"_id":3,"items":["pears","pecans","cherries"]}
{"_id":4,"items":["ice cream"]}

filter

根据给定条件返回满足条件的数组的子集。

示例集合sales包含以下文档：

{_id:0,items:[{item_id:43,quantity:2,price:10},{item_id:2,quantity:1,price:240}]}
{_id:1,items:[{item_id:23,quantity:3,price:110},{item_id:103,quantity:4,price:5},{item_id:38,quantity:1,price:300}]}
{_id:2,items:[{item_id:4,quantity:1,price:23}]}

将items数组过滤为仅包含price大于或等于100的文档：

db.collection('sales').field('filter(items, "item", gte("$$item.price", 100)) as filterItems').get()

执行结果：

{"_id":0,"filterItems":[{"item_id":2,"quantity":1,"price":240}]}
{"_id":1,"filterItems":[{"item_id":23,"quantity":3,"price":110},{"item_id":38,"quantity":1,"price":300}]}
{"_id":2,"filterItems":[]}

in

给定一个值和一个数组，如果值在数组中则返回 true，否则返回 false。

示例集合fruit具有以下文档：

{"_id":1,"location":"24th Street","in_stock":["apples","oranges","bananas"]}
{"_id":2,"location":"36th Street","in_stock":["bananas","pears","grapes"]}
{"_id":3,"location":"82nd Street","in_stock":["cantaloupes","watermelons","apples"]}

判断in_stock数组中是否存在bananas字符串：

db.collection('fruit').field('location as storeLocation, in("bananas", in_stock) as hasHananas').get()

执行结果：

{"_id":1,"storeLocation":"24th Street","hasBananas":true}
{"_id":2,"storeLocation":"36th Street","hasBananas":true}
{"_id":3,"storeLocation":"82nd Street","hasBananas":false}

indexOfArray

在数组中找出等于给定值的第一个元素的下标，如果找不到则返回 -1。

示例集合inventory包含以下文档：

{"_id":1,"items":["one","two","three"]}
{"_id":2,"items":[1,2,3]}
{"_id":3,"items":[null,null,2]}
{"_id":4,"items":null}
{"_id":5,"amount":3}

查询数字2在每个items数组中所处的数组索引：

db.collection('inventory').field('indexOfArray(items, 2) as index').get()

执行结果：

{"_id":1,"index":"-1"}
{"_id":2,"index":"1"}
{"_id":3,"index":"2"}
{"_id":4,"index":null}
{"_id":5,"index":null}

isArray

判断给定表达式是否是数组，返回布尔值。

示例集合warehouses包含以下文档：

{"_id":1,instock:["chocolate"],ordered:["butter","apples"]}
{"_id":2,instock:["apples","pudding","pie"]}
{"_id":3,instock:["pears","pecans"],ordered:["cherries"]}
{"_id":4,instock:["ice cream"],ordered:[]}

在将两个字段连接在一起之前检查instock和ordered字段是否为数组：

db.collection('warehouses').field('cond(and(isArray(instock), isArray(ordered)), concatArrays(instock, ordered), "有字段不是数组类型") as items').get()

执行结果：

{"_id":1,"items":["chocolate","butter","apples"]}
{"_id":2,"items":"有字段不是数组类型"}
{"_id":3,"items":["pears","pecans","cherries"]}
{"_id":4,"items":["ice cream"]}

map

类似 JavaScript Array 上的 map 方法，将给定数组的每个元素按给定转换方法转换后得出新的数组。

示例集合grades为以下文档：

{_id:1,quizzes:[5,6,7]}
{_id:2,quizzes:[]}
{_id:3,quizzes:[3,8,9]}

将quizzes数组中的每个元素加1

db.collection('grades').field('map(quizzes, "grade", add("$$grade", 2)) as adjustedGrades').get()

执行结果：

{"_id":1,"adjustedGrades":[7,8,9]}
{"_id":2,"adjustedGrades":[]}
{"_id":3,"adjustedGrades":[5,10,11]}

objectToArray

将一个对象转换为数组。方法把对象的每个键值对都变成输出数组的一个元素，元素形如 { k: <key>, v: <value> }

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1",dimensions:{l:25,w:10,uom:"cm"}}
{"_id":2,"item":"ABC2",dimensions:{l:50,w:25,uom:"cm"}}
{"_id":3,"item":"XYZ1",dimensions:{l:70,w:75,uom:"cm"}}

将dimensions字段作为数组返回：

db.collection('inventory').field('item, objectToArray(dimensions) as dimensionsArray').get()

执行结果：

{"_id":1,"item":"ABC1","dimensionsArray":[{"k":"l","v":25},{"k":"w","v":10},{"k":"uom","v":"cm"}]}
{"_id":2,"item":"ABC2","dimensionsArray":[{"k":"l","v":50},{"k":"w","v":25},{"k":"uom","v":"cm"}]}
{"_id":3,"item":"XYZ1","dimensionsArray":[{"k":"l","v":70},{"k":"w","v":75},{"k":"uom","v":"cm"}]}

range

返回一组生成的序列数字。给定开始值、结束值、非零的步长，range 会返回从开始值开始逐步增长、步长为给定步长、但不包括结束值的序列。

示例集合distances包含以下文档：

{_id:0,city:"San Jose",distance:42}
{_id:1,city:"Sacramento",distance:88}
{_id:2,city:"Reno",distance:218}
{_id:3,city:"Los Angeles",distance:383}

一名骑自行车的人正计划从旧金山骑车前往集合中列出的每个城市，并希望每25英里停下来休息一下。

以下操作来确定每个行程的停止点。

db.collection('distances').field('city, range(0, distance, 25) as restStops').get()

该操作返回以下内容：

{"city":"San Jose","restStops":[0,25]}
{"city":"Sacramento","restStops":[0,25,50,75]}
{"city":"Reno","restStops":[0,25,50,75,100,125,150,175,200]}
{"city":"Los Angeles","restStops":[0,25,50,75,100,125,150,175,200,225,250,275,300,325,350,375]}

reduce

类似 JavaScript 的 reduce 方法，应用一个表达式于数组各个元素然后归一成一个元素。

示例集合clothes包含以下文档：

{"_id":1,"productId":"ts1","description":"T-Shirt","color":"black","size":"M","price":20,"discounts":[0.5,0.1]}
{"_id":2,"productId":"j1","description":"Jeans","color":"blue","size":"36","price":40,"discounts":[0.25,0.15,0.05]}
{"_id":3,"productId":"s1","description":"Shorts","color":"beige","size":"32","price":30,"discounts":[0.15,0.05]}
{"_id":4,"productId":"ts2","description":"Cool T-Shirt","color":"White","size":"L","price":25,"discounts":[0.3]}
{"_id":5,"productId":"j2","description":"Designer Jeans","color":"blue","size":"30","price":80,"discounts":[0.1,0.25]}

每个文档包含一个discounts数组，其中包含每个项目当前可用的优惠券。 如果每个折扣都可以一次应用于该产品，则可以通过reduce对discounts数组中的每个元素应用以下公式来计算最低价格 （1-折扣）*价格。

db.collection('clothes').field('reduce(discounts, price, multiply("$$vaule", subtract(1, "$$this")) as discountedPrice').get()

该操作返回以下内容：

{"_id":ObjectId("57c893067054e6e47674ce01"),"discountedPrice":9}
{"_id":ObjectId("57c9932b7054e6e47674ce12"),"discountedPrice":24.224999999999998}
{"_id":ObjectId("57c993457054e6e47674ce13"),"discountedPrice":24.224999999999998}
{"_id":ObjectId("57c993687054e6e47674ce14"),"discountedPrice":17.5}
{"_id":ObjectId("57c993837054e6e47674ce15"),"discountedPrice":54}

reverseArray

返回给定数组的倒序形式。

示例集合users包含以下文档：

{"_id":1,"name":"dave123","favorites":["chocolate","cake","butter","apples"]}
{"_id":2,"name":"li","favorites":["apples","pudding","pie"]}
{"_id":3,"name":"ahn","favorites":[]}{"_id":4,"name":"ty"}

下面的示例favorites以相反的顺序返回一个包含数组元素的数组：

db.collection('users').field('name, reverseArray(favorites) as reverseFavorites').get()

执行结果：

{"_id":1,"name":"dave123","reverseFavorites":["apples","butter","cake","chocolate"]}
{"_id":2,"name":"li","reverseFavorites":["pie","pudding","apples"]}
{"_id":3,"name":"ahn","reverseFavorites":[]}{"_id":4,"name":"ty","reverseFavorites":null}

size

返回数组长度。

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1","description":"product 1",colors:["blue","black","red"]}
{"_id":2,"item":"ABC2","description":"product 2",colors:["purple"]}
{"_id":3,"item":"XYZ1","description":"product 3",colors:[]}
{"_id":4,"item":"ZZZ1","description":"product 4 - missing colors"}
{"_id":5,"item":"ZZZ2","description":"product 5 - colors is string",colors:"blue,red"}

返回colors数组中的元素数：

db.collection('inventory').field('item, cond(isArray(colors), size(colors), "None") as numberOfColors').get()

该操作返回以下内容：

{"_id":1,"item":"ABC1","numberOfColors":3}
{"_id":2,"item":"ABC2","numberOfColors":1}
{"_id":3,"item":"XYZ1","numberOfColors":0}
{"_id":4,"item":"ZZZ1","numberOfColors":"None"}
{"_id":5,"item":"ZZZ2","numberOfColors":"None"}

slice

类似 JavaScritp 的 slice 方法。返回给定数组的指定子集。

示例集合users包含以下文档：

{"_id":1,"name":"dave123",favorites:["chocolate","cake","butter","apples"]}
{"_id":2,"name":"li",favorites:["apples","pudding","pie"]}
{"_id":3,"name":"ahn",favorites:["pears","pecans","chocolate","cherries"]}
{"_id":4,"name":"ty",favorites:["ice cream"]}

为每个用户最多返回favorites数组中的前三个元素：

db.collection('users').field('name, slice(favorites, 3) as threeFavorites').get()

执行结果：

{"_id":1,"name":"dave123","threeFavorites":["chocolate","cake","butter"]}
{"_id":2,"name":"li","threeFavorites":["apples","pudding","pie"]}
{"_id":3,"name":"ahn","threeFavorites":["pears","pecans","chocolate"]}
{"_id":4,"name":"ty","threeFavorites":["ice cream"]}

zip

把二维数组的第二维数组中的相同序号的元素分别拼装成一个新的数组进而组装成一个新的二维数组。

示例集合matrices包含以下文档：

{matrix:[[1,2],[2,3],[3,4]]}
{matrix:[[8,7],[7,6],[5,4]]}

计算此集合中每个3x2矩阵的转置：

db.collection('matrices').field('zip(arrayElemAt(matrix, 0), arrayElemAt(matrix, 1), arrayElemAt(matrix, 2))').get()

执行返回：

{"transposed":[[1,2,3],[2,3,4]]}
{"transposed":[[8,7,5],[7,6,4]]}

布尔运算方法

布尔表达式将其参数表达式计算为布尔值，并返回一个布尔值作为结果。

and

给定多个表达式，and 仅在所有表达式都返回 true 时返回 true，否则返回 false。

接受任意数量的参数表达式。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

判断qty字段是否大于100并小于250：

db.collection('inventory').field('item, qty, and(gt(qty, 100), lt(qty, 250)) as result').get()

执行结果：

{"_id":1,"item":"abc1","qty":300,"result":false}
{"_id":2,"item":"abc2","qty":200,"result":true}
{"_id":3,"item":"xyz1","qty":250,"result":false}
{"_id":4,"item":"VWZ1","qty":300,"result":false}
{"_id":5,"item":"VWZ2","qty":180,"result":true}

not

给定一个表达式，如果表达式返回 true，则 not 返回 false，否则返回 true。注意表达式不能为逻辑表达式（and、or、nor、not）。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

判断qty字段是否不大于250：

db.collection('inventory').field('item, not(gt(qty, 250)) as result').get()

执行结果：

{"_id":1,"item":"abc1","result":false}
{"_id":2,"item":"abc2","result":true}
{"_id":3,"item":"xyz1","result":true}
{"_id":4,"item":"VWZ1","result":false}
{"_id":5,"item":"VWZ2","result":true}

or

给定多个表达式，如果任意一个表达式返回 true，则 or 返回 true，否则返回 false。

接受任意数量的参数表达式。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

判断qty字段是否大于250或小于200：

db.collection('inventory').field('item, or(gt(qty, 250), lt(qty, 200)) as result').get()

执行结果：

{"_id":1,"item":"abc1","result":true}
{"_id":2,"item":"abc2","result":false}
{"_id":3,"item":"xyz1","result":false}
{"_id":4,"item":"VWZ1","result":true}
{"_id":5,"item":"VWZ2","result":true}

比较运算方法

比较表达式返回一个布尔值，除了 cmp，它返回一个数字。

cmp

给定两个值，返回其比较值。如果第一个值小于第二个值，返回 -1 如果第一个值大于第二个值，返回 1 如果两个值相等，返回 0。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

qty值与250进行比较：

db.collection('inventory').field('item, qty, cmp(qty, 250) as cmpTo250').get()

执行结果：

{"_id": 1,"item":"abc1","qty":300,"cmpTo250":1}
{"_id": 2,"item":"abc2","qty":200,"cmpTo250":-1}
{"_id": 3,"item":"xyz1","qty":250,"cmpTo250":0}
{"_id": 4,"item":"VWZ1","qty":300,"cmpTo250":1}
{"_id": 5,"item":"VWZ2","qty":180,"cmpTo250":-1}

eq

匹配两个值，如果相等则返回 true，否则返回 false。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

判断qty是否等于250：

db.collection('inventory').field('item, qty, eq(qty, 250) as qtyEq250').get()

执行结果：

{"id": 1,"item":"abc1","qty":300,"qtyEq250":false}
{"id": 2,"item":"abc2","qty":200,"qtyEq250":false}
{"id": 3,"item":"xyz1","qty":250,"qtyEq250":true}
{"id": 4,"item":"VWZ1","qty":300,"qtyEq250":false}
{"id": 5,"item":"VWZ2","qty":180,"qtyEq250":false}

gt

匹配两个值，如果前者大于后者则返回 true，否则返回 false。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

判断qty是否大于250：

db.collection('inventory').field('item, qty, gt(qty, 250) as qtyGt250').get()

执行结果：

{"item":"abc1","qty":300,"qtyGt250":true}
{"item":"abc2","qty":200,"qtyGt250":false}
{"item":"xyz1","qty":250,"qtyGt250":false}
{"item":"VWZ1","qty":300,"qtyGt250":true}
{"item":"VWZ2","qty":180,"qtyGt250":false}

gte

匹配两个值，如果前者大于或等于后者则返回 true，否则返回 false。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

判断qty是否大于或等于250：

db.collection('inventory').field('item, qty, gte(qty, 250) as qtyGte250').get()

执行结果：

{"item":"abc1","qty":300,"qtyGte250":true}
{"item":"abc2","qty":200,"qtyGte250":false}
{"item":"xyz1","qty":250,"qtyGte250":true}
{"item":"VWZ1","qty":300,"qtyGte250":true}
{"item":"VWZ2","qty":180,"qtyGte250":false}

lt

匹配两个值，如果前者小于后者则返回 true，否则返回 false。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

判断qty是否小于250：

db.collection('inventory').field('item, qty, lt(qty, 250) as qtyLt250').get()

执行结果：

{"_id": 1,"item":"abc1","qty":300,"qtyLt250":false}
{"_id": 2,"item":"abc2","qty":200,"qtyLt250":true}
{"_id": 3,"item":"xyz1","qty":250,"qtyLt250":false}
{"_id": 4,"item":"VWZ1","qty":300,"qtyLt250":false}
{"_id": 5,"item":"VWZ2","qty":180,"qtyLt250":true}

lte

匹配两个值，如果前者小于或等于后者则返回 true，否则返回 false。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

判断qty是否小于或等于250：

db.collection('inventory').field('item, qty, lte(qty, 250) as qtyLte250').get()

执行结果：

{"_id": 1,"item":"abc1","qty":300,"qtyLte250":false}
{"_id": 2,"item":"abc2","qty":200,"qtyLte250":true}
{"_id": 3,"item":"xyz1","qty":250,"qtyLte250":true}
{"_id": 4,"item":"VWZ1","qty":300,"qtyLte250":false}
{"_id": 5,"item":"VWZ2","qty":180,"qtyLte250":true}

neq

匹配两个值，如果不相等则返回 true，否则返回 false.

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:"product 2",qty:200}
{"_id":3,"item":"xyz1",description:"product 3",qty:250}
{"_id":4,"item":"VWZ1",description:"product 4",qty:300}
{"_id":5,"item":"VWZ2",description:"product 5",qty:180}

判断qty是否不等于250：

db.collection('inventory').field('item, qty, neq(qty, 250) as qtyNe250').get()

执行结果：

{"_id": 1,"item":"abc1","qty":300,"qtyNe250":true}
{"_id": 2,"item":"abc2","qty":200,"qtyNe250":true}
{"_id": 3,"item":"xyz1","qty":250,"qtyNe250":false}
{"_id": 4,"item":"VWZ1","qty":300,"qtyNe250":true}
{"_id": 5,"item":"VWZ2","qty":180,"qtyNe250":true}

条件运算方法

cond

计算布尔表达式1，成立返回表达式2，否则返回表达式3。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",qty:300}
{"_id":2,"item":"abc2",qty:200}
{"_id":3,"item":"xyz1",qty:250}

如果 qty 值大于等于250，将discount设置为30，否则设置为20：

db.collection('inventory').field('item, cond(gte(qty, 250), 30, 20) as discount').get()

执行结果：

{"_id":1,"item":"abc1","discount":30}
{"_id":2,"item":"abc2","discount":20}
{"_id":3,"item":"xyz1","discount":30}

ifNull

计算给定的表达式，如果表达式结果为 null、undefined 或者不存在，那么返回一个替代值；否则返回原值。

示例集合inventory包含以下文档：

{"_id":1,"item":"abc1",description:"product 1",qty:300}
{"_id":2,"item":"abc2",description:null,qty:200}
{"_id":3,"item":"xyz1",qty:250}

如果description字段为空或不存在返回"Unspecified"字符串， 否则返回description字段值：

db.collection('inventory').field('item, ifNull(description, "Unspecified") as description').get()

执行结果：

{"_id":1,"item":"abc1","description":"product 1"}
{"_id":2,"item":"abc2","description":"Unspecified"}
{"_id":3,"item":"xyz1","description":"Unspecified"}

switch

根据给定的 switch-case-default 计算返回值。

示例集合grades包含以下文档：

{"_id":1,"name":"Susan Wilkes","scores":[87,86,78]}
{"_id":2,"name":"Bob Hanna","scores":[71,64,81]}
{"_id":3,"name":"James Torrelio","scores":[91,84,97]}

根据每个学生的平均分数显示特定的消息。

db.collection('grades').field('name, switch([{case: gte(avg(scores), 90), then: "Doing great!"}, {case: and(gte(avg(scores), 80), lt(avg(scores), 90)), then: "Doing pretty well."}, {case: lt(avg(scores), 80), then: "Needs improvement."}], "No scores found.")').get()

该操作返回以下内容：

{"_id":1,"name":"Susan Wilkes","summary":"Doing pretty well."}
{"_id":2,"name":"Bob Hanna","summary":"Needs improvement."}
{"_id":3,"name":"James Torrelio","summary":"Doing great!"}

日期运算方法

dateFromParts

给定日期的相关信息，构建并返回一个日期对象。

从提供的输入字段构造三个日期对象：

db.collection('sales').field('dateFromParts(2017, 2, 8, 12) as date, isoDateFromParts(2017, 6, 3, 12) as date_iso, dateFromParts(2016, 12, 31, 23, 46, 12, "America/New_York") as date_timezone').get()

执行结果：

{"_id":1,"date":"2017-02-08T12:00:00Z","date_iso":"2017-02-08T12:00:00Z","date_timezone":"2017-01-01T04:46:12Z"}

dateFromString

将一个日期/时间字符串转换为日期对象。

示例集合logmessages包含以下集合。

{_id:1,date:"2017-02-08T12:10:40.787",timezone:"America/New_York",message:"Step 1: Started"}
{_id:2,date:"2017-02-08",timezone:"-05:00",message:"Step 1: Ended"}
{_id:3,message:" Step 1: Ended "}
{_id:4,date:"2017-02-09",timezone:"Europe/London",message:"Step 2: Started"}
{_id:5,date:"2017-02-09T03:35:02.055",timezone:"+0530",message:"Step 2: In Progress"}

将date值转换为日期对象：

db.collection('logmessages').field('dateFromString(date, "%Y-%m-%dT%H:%M:%S.%LZ", "America/New_York") as date').get()

执行结果：

{"_id":1,"date": "2017-02-08T17:10:40.787Z"}
{"_id":2,"date": "2017-02-08T05:00:00Z"}
{"_id":3,"date":null}
{"_id":4,"date": "2017-02-09T05:00:00Z"}
{"_id":5,"date": "2017-02-09T08:35:02.055Z"}

该timezone参数也可以通过一个文档字段，而不是硬编码参数提供的。例如：

db.collection('logmessages').field('dateFromString(date, "%Y-%m-%dT%H:%M:%S.%LZ", timezone) as date').get()

执行结果：

{"_id":1,"date":"2017-02-08T17:10:40.787Z"}
{"_id":2,"date":"2017-02-08T05:00:00Z"}
{"_id":3,"date":null}
{"_id":4,"date":"2017-02-09T00:00:00Z"}
{"_id":5,"date":"2017-02-08T22:05:02.055Z"}

如果集合中带有不可解析的日期字符串，将引发错误 onError，可以用 onError 参数以其原始字符串形式返回无效日期：

示例集合dates如下：

{"_id":1,"date":"2017-02-08T12:10:40.787",timezone:"America/New_York"}
{"_id":2,"date":"20177-02-09T03:35:02.055",timezone:"America/New_York"}

db.collection('dates').field('dateFromString(date, "%Y-%m-%dT%H:%M:%S.%L", timezone, date)')

执行结果：

{"_id":1,"date": "2017-02-08T17:10:40.787Z"}
{"_id":2,"date": "20177-02-09T03:35:02.055"}

如果集合中带有null日期字符串，可以使用 onNull 参数返回一个日期：

示例集合dates如下：

{"_id":1,"date":"2017-02-08T12:10:40.787",timezone:"America/New_York"}
{"_id":2,"date":null,timezone:"America/New_York"}

db.collection('dates').field('dateFromString(date, "%Y-%m-%dT%H:%M:%S.%L", timezone, date, new Date(0))')

执行结果：

{"_id":1,"date": "2017-02-08T17:10:40.787Z"}
{"_id":2,"date": "1970-01-01T00:00:00Z"}

dateToString

根据指定的表达式将日期对象格式化为符合要求的字符串。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

将date字段格式化后返回：

db.collection('sales').field('dateToString(date, "%Y-%m-%d") as yearMonthDayUTC, dateToString(date, "%H:%M:%S:%L%z", "America/New_York") as timewithOffsetNY, dateToString(date, "%H:%M:%S:%L%z", "+04:30") as timewithOffset430, dateToString(date, "%Z", "America/New_York") as minutesOffsetNY, dateToString(date, "%Z", "+04:30") as minutesOffset430').get()

执行结果：

{"_id":1,"yearMonthDayUTC":"2014-01-01","timewithOffsetNY":"03:15:39:736-0500","timewithOffset430":"12:45:39:736+0430","minutesOffsetNY":"-300","minutesOffset430":"270"}

dayOfMonth

返回日期字段对应的天数（一个月中的哪一天），是一个介于 1 至 31 之间的数字。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

执行结果：

{"_id":1,"year":2014,"month":1,"day":1,"hour":8,"minutes":15,"seconds":39,"milliseconds":736,"dayOfYear":1,"dayOfWeek":4,"week":0}

dayOfWeek

返回日期字段对应的天数（一周中的第几天），是一个介于 1（周日）到 7（周六）之间的整数。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

执行结果：

{"_id":1,"year":2014,"month":1,"day":1,"hour":8,"minutes":15,"seconds":39,"milliseconds":736,"dayOfYear":1,"dayOfWeek":4,"week":0}

dayOfYear

返回日期字段对应的天数（一年中的第几天），是一个介于 1 到 366 之间的整数。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

执行结果：

{"_id":1,"year":2014,"month":1,"day":1,"hour":8,"minutes":15,"seconds":39,"milliseconds":736,"dayOfYear":1,"dayOfWeek":4,"week":0}

hour

返回日期字段对应的小时数，是一个介于 0 到 23 之间的整数。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

执行结果：

{"_id":1,"year":2014,"month":1,"day":1,"hour":8,"minutes":15,"seconds":39,"milliseconds":736,"dayOfYear":1,"dayOfWeek":4,"week":0}

isoDayOfWeek

返回日期字段对应的 ISO 8601 标准的天数（一周中的第几天），是一个介于 1（周一）到 7（周日）之间的整数。

示例集合birthdays包含以下文档：

{"_id":1,"name":"Betty","birthday":"1993-09-21T00:00:00Z"}
{"_id":2,"name":"Veronica","birthday":"1981-11-07T00:00:00Z"}

从birthday字段中返回一周中的第几天：

db.collection('dates').field('name, isoDayOfWeek(birthday) as dayOfWeek').get()

执行结果：

{"name":"Betty","dayOfWeek":2}{"name":"Veronica","dayOfWeek":6}

isoWeek

返回日期字段对应的 ISO 8601 标准的周数（一年中的第几周），是一个介于 1 到 53 之间的整数。

示例集合deliveries包含以下文档：

{"_id":1,"date":"2006-10-24T00:00:00Z","city":"Boston"}
{"_id":2,"date":"2011-08-18T00:00:00Z","city":"Detroit"}

返回每个date字段的星期数。

db.collection('deliveries').field('city, isoWeek(date) as weekNumber').get()

执行结果：

{"city":"Boston","weekNumber":43}{"city":"Detroit","weekNumber":33}

isoWeekYear

返回日期字段对应的 ISO 8601 标准的天数（一年中的第几天）。

示例集合anniversaries包含以下文档：

{"_id":1,"date":"2016-01-01T00:00:00Z"}
{"_id":2,"date":"2016-01-04T00:00:00Z"}
{"_id":3,"date":"2015-01-01T00:00:00Z"}
{"_id":4,"date":"2014-04-21T00:00:00Z"}

返回date字段ISO 8601格式的年份。

db.collection('anniversaries').field('isoWeekYear(date) as yearNumber').get()

执行结果：

{"_id":1,"yearNumber":2015}
{"_id":2,"yearNumber":2016}
{"_id":3,"yearNumber":2015}
{"_id":4,"yearNumber":2014}

millisecond

返回日期字段对应的毫秒数，是一个介于 0 到 999 之间的整数。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

minute

返回日期字段对应的分钟数，是一个介于 0 到 59 之间的整数。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

month

返回日期字段对应的月份，是一个介于 1 到 12 之间的整数。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

second

返回日期字段对应的秒数，是一个介于 0 到 59 之间的整数，在特殊情况下（闰秒）可能等于 60。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

week

返回日期字段对应的周数（一年中的第几周），是一个介于 0 到 53 之间的整数。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

year

返回日期字段对应的年份。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:15:39.736Z"}

解析date字段：

db.collection('sales').field('year(date) as year, month(date) as month, dayOfMonth(date) as day, hour(date) as hour, minutes(date) as minutes, second(date) as seconds, millisecond(date) as milliseconds, dayOfYear(date) as dayOfYear, dayOfWeek(date) as dayOfWeek, week(date) as week').get()

timestampToDate

传入一个时间戳，返回对应的日期对象。

仅JQL字符串内支持，HBuilderX 3.1.0起支持

示例集合sales包含以下文档：

{"_id":1,"item":"abc","date": 1664697600000}

返回 date字段的日期对象：

db.collection('sales').field('item, timestampToDate(date) as date').get()

执行结果：

{"_id":1,"item":"abc","date": "2022-10-02T08:00:00.000Z"}

文字运算方法

literal

直接返回一个值的字面量，不经过任何解析和处理。

将 $ 视为字面量

示例集合records包含以下文档：

{"_id":1,"item":"abc123",price:"$2.50"}
{"_id":2,"item":"xyz123",price:"1"}
{"_id":3,"item":"ijk123",price:"$1"}

判断 price 是否等于字符串 $1:

db.collection('records').field('eq(price, literal($1)) as costsOneDollar').get()

执行结果：

{"_id":1,"costsOneDollar":false}
{"_id":2,"costsOneDollar":false}
{"_id":3,"costsOneDollar":true}

对象运算方法

mergeObjects

将多个对象合并为单个对象。

注意：如果要合并的文档包含相同的字段名称，则结果文档中的字段将会覆盖上次合并的文档中的值。

示例集合orders包含以下文档：

{"_id":1,"item":"abc","price":12,"ordered":2}
{"_id":2,"item":"jkl","price":20,"ordered":1}

另外一个示例集合items包含以下文档：

{"_id":1,"item":"abc",description:"product 1","instock":120}
{"_id":2,"item":"def",description:"product 2","instock":80}
{"_id":3,"item":"jkl",description:"product 3","instock":60}

合并 items 与 orders

先通过 lookup 将两表进行关联，之后使用 mergeObjects 表达式在 replaceRoot 阶段合并两表。

db.collection('orders').aggregate().lookup({
    from: "items",
    let: {
        item: "$item"
    },
    pipeline: $.pipeline().match(db.command.expr($.and([
        $.eq(['$item', '$$item'])
    ]))).done(),
    as: 'fromItems'
}).replaceRoot({
    newRoot: $.mergeObjects([ $.arrayElemAt(['$fromItems', 0]), '$$ROOT' ])
}).project({
    fromItems: 0
}).end()

执行返回：

{"_id":1,"item":"abc","description":"product 1","instock":120,"price":12,"ordered":2}
{"_id":2,"item":"jkl","description":"product 3","instock":60,"price":20,"ordered":1}

在groupBy中使用

示例集合sales包含以下文档：

{_id:1,year:2017,item:"A",quantity:{"2017Q1":500,"2017Q2":500}}
{_id:2,year:2016,item:"A",quantity:{"2016Q1":400,"2016Q2":300,"2016Q3":0,"2016Q4":0}}
{_id:3,year:2017,item:"B",quantity:{"2017Q1":300}}
{_id:4,year:2016,item:"B",quantity:{"2016Q3":100,"2016Q4":250}}

按照 item 进行分组，mergeObjects 仅接受单个对象操作。

db.collection('sales').groupBy('item').groupField('mergeObjects(quantity) as mergedSales').get()

执行结果：

{"_id":"B","mergedSales":{"2017Q1":300,"2016Q3":100,"2016Q4":250}}
{"_id":"A","mergedSales":{"2017Q1":500,"2017Q2":500,"2016Q1":400,"2016Q2":300,"2016Q3":0,"2016Q4":0}}

objectToArray

将文档转换为代表键值对的文档数组。

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1",dimensions:{l:25,w:10,uom:"cm"}}
{"_id":2,"item":"ABC2",dimensions:{l:50,w:25,uom:"cm"}}
{"_id":3,"item":"XYZ1",dimensions:{l:70,w:75,uom:"cm"}}

将dimensions字段作为数组返回：

db.collection('inventory').field('item, objectToArray(dimensions) as dimensionsArray').get()

执行结果：

{"_id":1,"item":"ABC1","dimensions":[{"k":"l","v":25},{"k":"w","v":10},{"k":"uom","v":"cm"}]}
{"_id":2,"item":"ABC2","dimensions":[{"k":"l","v":50},{"k":"w","v":25},{"k":"uom","v":"cm"}]}
{"_id":3,"item":"XYZ1","dimensions":[{"k":"l","v":70},{"k":"w","v":75},{"k":"uom","v":"cm"}]}

集合运算方法

Set 表达式对数组执行 set 操作，将数组视为 sets。 Set 表达式忽略每个输入数组中的重复条目和元素的顺序。

如果 set 操作返回一个 set，则该操作会过滤掉结果中的重复项，以输出仅包含唯一条目的 array。输出 array 中元素的顺序未指定。

如果集合包含嵌套的 array 元素，则 set 表达式不会下降到嵌套的 array 中，而是在顶层level 处计算 array。

allElementsTrue

输入一个数组，或者数组字段的表达式。如果数组中所有元素均为真值，那么返回 true，否则返回 false。空数组永远返回 true。 示例集合survey使用以下文档：

{"_id":1,"responses":[true]}
{"_id":2,"responses":[true,false]}
{"_id":3,"responses":[]}
{"_id":4,"responses":[1,true,"seven"]}
{"_id":5,"responses":[0]}
{"_id":6,"responses":[[]]}
{"_id":7,"responses":[[0]]}
{"_id":8,"responses":[[false]]}
{"_id":9,"responses":[null]}
{"_id":10,"responses":[undefined]}

判断responses数组是否仅包含计算结果为true的值：

db.collection('survey').field('responses, allElementsTrue(responses) as isAllTrue').get()

执行结果：

{"responses":[true],"isAllTrue":true}
{"responses":[true,false],"isAllTrue":false}
{"responses":[],"isAllTrue":true}
{"responses":[1,true,"seven"],"isAllTrue":true}
{"responses":[0],"isAllTrue":false}
{"responses":[[]],"isAllTrue":true}
{"responses":[[0]],"isAllTrue":true}
{"responses":[[false]],"isAllTrue":true}
{"responses":[null],"isAllTrue":false}
{"responses":[undefined],"isAllTrue":false}

anyElementTrue

输入一个数组，或者数组字段的表达式。如果数组中任意一个元素为真值，那么返回 true，否则返回 false。空数组永远返回 false。

示例集合survey包含以下文档：

{"_id":1,"responses":[true]}
{"_id":2,"responses":[true,false]}
{"_id":3,"responses":[]}
{"_id":4,"responses":[1,true,"seven"]}
{"_id":5,"responses":[0]}
{"_id":6,"responses":[[]]}
{"_id":7,"responses":[[0]]}
{"_id":8,"responses":[[false]]}
{"_id":9,"responses":[null]}
{"_id":10,"responses":[undefined]}

判断responses数组是否包含任何计算结果为true：

db.collection('survey').field('responses, anyElementTrue(responses) as isAnyTrue').get()

执行结果：

{"responses":[true],"isAnyTrue":true}
{"responses":[true,false],"isAnyTrue":true}
{"responses":[],"isAnyTrue":false}
{"responses":[1,true,"seven"],"isAnyTrue":true}
{"responses":[0],"isAnyTrue":false}
{"responses":[[]],"isAnyTrue":true}
{"responses":[[0]],"isAnyTrue":true}
{"responses":[[false]],"isAnyTrue":true}
{"responses":[null],"isAnyTrue":false}
{"responses":[undefined],"isAnyTrue":false}

setDifference

输入两个集合，输出只存在于第一个集合中的元素

示例集合experiments包含以下文档：

{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}
{"_id":9,"A":[],"B":["red"]}

比较A,B数组，返回只有在数组B中的值：

db.collection('experiments').field('A, B, setDifference(B, A) as inBOnly').get()

执行结果：

{"A":["red","blue"],"B":["red","blue"],"inBOnly":[]}
{"A":["red","blue"],"B":["blue","red","blue"],"inBOnly":[]}
{"A":["red","blue"],"B":["red","blue","green"],"inBOnly":["green"]}
{"A":["red","blue"],"B":["green","red"],"inBOnly":["green"]}
{"A":["red","blue"],"B":[],"inBOnly":[]}
{"A":["red","blue"],"B":[["red"],["blue"]],"inBOnly":[["red"],["blue"]]}
{"A":["red","blue"],"B":[["red","blue"]],"inBOnly":[["red","blue"]]}
{"A":[],"B":[],"inBOnly":[]}
{"A":[],"B":["red"],"inBOnly":["red"]}

setEquals

输入两个集合，判断两个集合中包含的元素是否相同（不考虑顺序、去重）

示例集合experiments包含以下文档：

{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}{"_id":9,"A":[],"B":["red"]}

判断A数组和B数组是否包含相同的元素：

db.collection('experiments').field('A, B, setEquals(A, B) as sameElements').get()

执行结果：

{"A":["red","blue"],"B":["red","blue"],"sameElements":true}
{"A":["red","blue"],"B":["blue","red","blue"],"sameElements":true}
{"A":["red","blue"],"B":["red","blue","green"],"sameElements":false}
{"A":["red","blue"],"B":["green","red"],"sameElements":false}
{"A":["red","blue"],"B":[],"sameElements":false}
{"A":["red","blue"],"B":[["red"],["blue"]],"sameElements":false}
{"A":["red","blue"],"B":[["red","blue"]],"sameElements":false}
{"A":[],"B":[],"sameElements":true}{"A":[],"B":["red"],"sameElements":false}

setIntersection

输入两个集合，输出两个集合的交集

示例集合experiments包含以下文档：

{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}{"_id":9,"A":[],"B":["red"]}

返回A数组和B数组共同的元素数组：

db.collection('experiments').field('A, B, setIntersection(A, B) as commonToBoth').get()

执行结果：

{"A":["red","blue"],"B":["red","blue"],"commonToBoth":["blue","red"]}
{"A":["red","blue"],"B":["blue","red","blue"],"commonToBoth":["blue","red"]}
{"A":["red","blue"],"B":["red","blue","green"],"commonToBoth":["blue","red"]}
{"A":["red","blue"],"B":["green","red"],"commonToBoth":["red"]}
{"A":["red","blue"],"B":[],"commonToBoth":[]}
{"A":["red","blue"],"B":[["red"],["blue"]],"commonToBoth":[]}
{"A":["red","blue"],"B":[["red","blue"]],"commonToBoth":[]}
{"A":[],"B":[],"commonToBoth":[]}{"A":[],"B":["red"],"commonToBoth":[]}

setIsSubset

输入两个集合，判断第一个集合是否是第二个集合的子集。

示例集合experiments包含以下文档：

{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}{"_id":9,"A":[],"B":["red"]}

判断A数组是否为数组的子集B：

db.collection('experiments').field('A, B, setIsSubset(A, B) as AisSubset').get()

执行结果：

{"A":["red","blue"],"B":["red","blue"],"AisSubset":true}
{"A":["red","blue"],"B":["blue","red","blue"],"AisSubset":true}
{"A":["red","blue"],"B":["red","blue","green"],"AisSubset":true}
{"A":["red","blue"],"B":["green","red"],"AisSubset":false}
{"A":["red","blue"],"B":[],"AisSubset":false}
{"A":["red","blue"],"B":[["red"],["blue"]],"AisSubset":false}
{"A":["red","blue"],"B":[["red","blue"]],"AisSubset":false}
{"A":[],"B":[],"AisSubset":true}{"A":[],"B":["red"],"AisSubset":true}

setUnion

输入两个集合，输出两个集合的并集。

示例集合experiments包含以下文档：

{"_id":1,"A":["red","blue"],"B":["red","blue"]}
{"_id":2,"A":["red","blue"],"B":["blue","red","blue"]}
{"_id":3,"A":["red","blue"],"B":["red","blue","green"]}
{"_id":4,"A":["red","blue"],"B":["green","red"]}
{"_id":5,"A":["red","blue"],"B":[]}
{"_id":6,"A":["red","blue"],"B":[["red"],["blue"]]}
{"_id":7,"A":["red","blue"],"B":[["red","blue"]]}
{"_id":8,"A":[],"B":[]}{"_id":9,"A":[],"B":["red"]}

返回在A数组或B数组或两者中都找到的元素数组：

db.collection('experiments').field('A, B, setUnion(A, B) as allValues').get()

执行结果：

{"A":["red","blue"],"B":["red","blue"],"allValues":["blue","red"]}
{"A":["red","blue"],"B":["blue","red","blue"],"allValues":["blue","red"]}
{"A":["red","blue"],"B":["red","blue","green"],"allValues":["blue","red","green"]}
{"A":["red","blue"],"B":["green","red"],"allValues":["blue","red","green"]}
{"A":["red","blue"],"B":[],"allValues":["blue","red"]}
{"A":["red","blue"],"B":[["red"],["blue"]],"allValues":["blue","red",["red"],["blue"]]}
{"A":["red","blue"],"B":[["red","blue"]],"allValues":["blue","red",["red","blue"]]}
{"A":[],"B":[],"allValues":[]}{"A":[],"B":["red"],"allValues":["red"]}

字符串运算方法

字符串表达式（除外 concat）仅对ASCII字符字符串具有明确定义的行为。

concat 行为是明确定义的，与所使用的字符无关。

concat

连接任意数量的字符串。

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}

将item字段和description用“-”定界符的字段连接起来：

db.collection('inventory').field('concat(item, "-", "description") as itemDescription').get()

执行结果：

{"_id":1,"itemDescription":"ABC1 - product 1"}
{"_id":2,"itemDescription":"ABC2 - product 2"}
{"_id":3,"itemDescription":null}

indexOfBytes

在目标字符串中查找子字符串，并返回第一次出现的 UTF-8 的字节索引（从0开始）。如果不存在子字符串，返回 -1

示例集合inventory包含以下文档：

{"_id":1,"item":"foo"}
{"_id":2,"item":"fóofoo"}
{"_id":3,"item":"the foo bar"}
{"_id":4,"item":"hello world fóo"}
{"_id":5,"item":null}{"_id":6,"amount":3}

检索每个项目中字符串foo所在的索引：

db.collection('inventory').field('indexOfBytes(item, "foo") as bytyLocation').get()

执行结果：

{"_id":1,"byteLocation":"0"}
{"_id":2,"byteLocation":"4"}
{"_id":3,"byteLocation":"4"}
{"_id":4,"byteLocation":"-1"}
{"_id":5,"byteLocation":null}
{"_id":6,"byteLocation":null}

indexOfCP

在目标字符串中查找子字符串，并返回第一次出现的 UTF-8 的 code point 索引（从0开始）。如果不存在子字符串，返回 -1

示例集合inventory包含以下文档：

{"_id":1,"item":"foo"}
{"_id":2,"item":"fóofoo"}
{"_id":3,"item":"the foo bar"}
{"_id":4,"item":"hello world fóo"}
{"_id":5,"item":null}
{"_id":6,"amount":3}

返回foo每个item字符串中字符串所在的代码点索引：

db.collection('inventory').field('indexOfCP(item, "foo") as cpLocation').get()

执行结果：

{"_id":1,"cpLocation":"0"}
{"_id":2,"cpLocation":"3"}
{"_id":3,"cpLocation":"4"}
{"_id":4,"cpLocation":"-1"}
{"_id":5,"cpLocation":null}
{"_id":6,"cpLocation":null}

split

按照分隔符分隔数组，并且删除分隔符，返回子字符串组成的数组。如果字符串无法找到分隔符进行分隔，返回原字符串作为数组的唯一元素

示例集合deliveries包含以下文档：

{"_id":1,"city":"Berkeley, CA","qty":648}
{"_id":2,"city":"Bend, OR","qty":491}
{"_id":3,"city":"Kensington, CA","qty":233}

通过分割创建字符串数组city字段，使用,作为分隔符。

db.collection('deliveries').field('split(city, ", ") as city_state').get()

执行结果：

{"_id":1,"city_state":["Berkeley", "CA"]}
{"_id":2,"city_state":["Bend", "OR"]}
{"_id":3,"city_state":["Kensington", "CA"]}

strLenBytes

计算并返回指定字符串中 utf-8 编码的字节数量。

示例集合food包含以下文档：

{"_id":1,"name":"apple"}
{"_id":2,"name":"banana"}
{"_id":3,"name":"éclair"}
{"_id":4,"name":"hamburger"}
{"_id":5,"name":"jalapeño"}
{"_id":6,"name":"pizza"}
{"_id":7,"name":"tacos"}
{"_id":8,"name":"寿司"}

计算每个name值的length：

db.collection('food').field('name, strLenBytes(name) as length').get()

执行结果：

{"_id":1,"name":"apple","length":5}
{"_id":2,"name":"banana","length":6}
{"_id":3,"name":"éclair","length":7}
{"_id":4,"name":"hamburger","length":9}
{"_id":5,"name":"jalapeño","length":9}
{"_id":6,"name":"pizza","length":5}
{"_id":7,"name":"tacos","length":5}
{"_id":8,"name":"寿司","length":6}

_id: 3 和 _id: 5 的文档每个都包含一个变音符号（分别为 é 和 ñ），需要两个字节进行编码。

_id: 8 的文档包含两个日语字符，每个字符使用三个字节进行编码。

这使得长度大于具有 _id: 3、_id: 5 和 _id: 8 的文档的名称中的字符数。

strLenCP

计算并返回指定字符串的UTF-8 code points 数量。

示例集合food包含以下文档：

{"_id":1,"name":"apple"}
{"_id":2,"name":"banana"}
{"_id":3,"name":"éclair"}
{"_id":4,"name":"hamburger"}
{"_id":5,"name":"jalapeño"}
{"_id":6,"name":"pizza"}
{"_id":7,"name":"tacos"}
{"_id":8,"name":"寿司"}

计算每个name值的length：

db.collection('food').field('name, strLenCP(name) as length').get()

执行结果：

{"_id":1,"name":"apple","length":5}
{"_id":2,"name":"banana","length":6}
{"_id":3,"name":"éclair","length":6}
{"_id":4,"name":"hamburger","length":9}
{"_id":5,"name":"jalapeño","length":8}
{"_id":6,"name":"pizza","length":5}
{"_id":7,"name":"tacos","length":5}
{"_id":8,"name":"寿司","length":2}

strcasecmp

对两个字符串在不区分大小写的情况下进行大小比较，并返回比较的结果

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}

对quarter字段值与字符串“13q4”进行不区分大小写的比较：

db.collection('inventory').field('item, strcasecmp(quarter, "13q4") as comparisonResult').get()

执行结果：

{"_id":1,"item":"ABC1","comparisonResult":-1}
{"_id":2,"item":"ABC2","comparisonResult":0}
{"_id":3,"item":"XYZ1","comparisonResult":1}

substr

返回字符串从指定位置开始的指定长度的子字符串。

不推荐使用，推荐使用substrBytes或substrCP

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}

将quarter分为 yearSubstring 和 QuarterSubstring：

db.collection('inventory').field('item, substr(quarter, 0, 2) as yearSubstring, substr(quarter, 2, -1) as quarterSubtring')

执行结果：

{"_id":1,"item":"ABC1","yearSubstring":"13","quarterSubtring":"Q1"}
{"_id":2,"item":"ABC2","yearSubstring":"13","quarterSubtring":"Q4"}
{"_id":3,"item":"XYZ1","yearSubstring":"14","quarterSubtring":"Q2"}

substrBytes

返回字符串从指定位置开始的指定长度的子字符串。子字符串是由字符串中指定的 UTF-8 字节索引的字符开始，长度为指定的字节数。

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}

将quarter（仅包含单字节 US-ASCII 字符）分隔为 yearSubstring 和 QuarterSubstring。

QuarterSubstring 字段表示来自 yearSubstring 之后的指定字节索引的字符串的其余部分。

它是通过使用 strLenBytes 从字符串的长度中减去字节索引来计算的。

db.collection('inventory').field('item, substrBytes(quarter, 0, 2) as yearSubstring, substrBytes(quarter, 2, subtract(strLenBytes(quarter), 2)) as quarterSubtring').get()

执行结果：

{"_id":1,"item":"ABC1","yearSubstring":"13","quarterSubtring":"Q1"}
{"_id":2,"item":"ABC2","yearSubstring":"13","quarterSubtring":"Q4"}
{"_id":3,"item":"XYZ1","yearSubstring":"14","quarterSubtring":"Q2"}

substrCP

返回字符串从指定位置开始的指定长度的子字符串。子字符串是由字符串中指定的 UTF-8 字节索引的字符开始，长度为指定的字节数

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1",quarter:"13Q1","description":"product 1"}
{"_id":2,"item":"ABC2",quarter:"13Q4","description":"product 2"}
{"_id":3,"item":"XYZ1",quarter:"14Q2","description":null}

将quarter分隔为 yearSubstring 和 QuarterSubstring。

QuarterSubstring 字段表示来自 yearSubstring 之后的指定字节索引的字符串的其余部分。

它是通过使用 strLenCP 从字符串的长度中减去字节索引来计算的。

db.collection('inventory').field('item, substrCP(quarter, 0, 2) as yearSubstring, substrCP(quarter, 2, subtract(strLenCP(quarter), 2))').get()

执行结果：

{"_id":1,"item":"ABC1","yearSubstring":"13","quarterSubtring":"Q1"}
{"_id":2,"item":"ABC2","yearSubstring":"13","quarterSubtring":"Q4"}
{"_id":3,"item":"XYZ1","yearSubstring":"14","quarterSubtring":"Q2"}

toLower

将字符串转换为小写。接受单个参数表达式。

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1",quarter:"13Q1","description":"PRODUCT 1"}
{"_id":2,"item":"abc2",quarter:"13Q4","description":"Product 2"}
{"_id":3,"item":"xyz1",quarter:"14Q2","description":null}

返回小写item和小写description值：

db.collection('inventory').field('toLower(item) as item, toLower(description) as description').get()

执行结果：

{"_id":1,"item":"abc1","description":"product 1"}
{"_id":2,"item":"abc2","description":"product 2"}
{"_id":3,"item":"xyz1","description":""}

toUpper

将字符串转换为大写。接受单个参数表达式。

示例集合inventory包含以下文档：

{"_id":1,"item":"ABC1",quarter:"13Q1","description":"PRODUCT 1"}
{"_id":2,"item":"abc2",quarter:"13Q4","description":"Product 2"}
{"_id":3,"item":"xyz1",quarter:"14Q2","description":null}

返回大写item和大写description值：

db.collection('inventory').field('toUpper(item) as item, toUpper(description) as description').get()

执行结果：

{"_id":1,"item":"ABC1","description":"PRODUCT 1"}
{"_id":2,"item":"ABC2","description":"PRODUCT 2"}
{"_id":3,"item":"XYZ1","description":""}

分组(groupBy)运算方法

addToSet

向数组中添加值，如果数组中已存在该值，不执行任何操作。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:12:00Z"}

计算每组出售的唯一商品的列表：

db.collection('sales').groupBy('dayOfYear(date) as day, year(date) as year').groupField('addToSet(item) as itemsSold').get()

执行结果：

{"day":46,"year":2014,"itemsSold":["xyz","abc"]}
{"day":34,"year":2014,"itemsSold":["xyz","jkl"]}
{"day":1,"year":2014,"itemsSold":["abc"]}

avg

返回指定表达式对应数据的平均值。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:12:00Z"}

按item字段对文档进行分组，并计算每个分组的平均数量和平均数量。

db.collection('sales').groupBy('item').groupField('avg(multiply(price, quantity)) as avgAmount, avg(quantity) as avgQuantity').get()

执行结果：

{"_id":"xyz","avgAmount":37.5,"avgQuantity":7.5}
{"_id":"jkl","avgAmount":20,"avgQuantity":1}
{"_id":"abc","avgAmount":60,"avgQuantity":6}

first

返回指定字段在一组集合的第一条记录对应的值。仅当这组集合是按照某种定义排序（ sort ）后，此操作才有意义。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}
{"_id":6,"item":"xyz","price":5,"quantity":5,"date":"2014-02-15T12:05:10Z"}
{"_id":7,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T14:12:12Z"}

按字段item分组，并计算每个物料的首次销售日期：

const $ = db.command.aggregate

db.collection('sales').aggregate().sort({item: 1, date: 1}).group({
    _id: "$item",
    firstSalesDate: $.first('$date')
}).end()

执行结果：

{"_id":"xyz","firstSalesDate":"2014-02-03T09:05:00Z"}
{"_id":"jkl","firstSalesDate":"2014-02-03T09:00:00Z"}
{"_id":"abc","firstSalesDate":"2014-01-01T08:00:00Z"}

last

返回指定字段在一组集合的最后一条记录对应的值。仅当这组集合是按照某种定义排序（ sort ）后，此操作才有意义。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","date":"2014-01-01T08:00:00Z","price":10,"quantity":2}
{"_id":2,"item":"jkl","date":"2014-02-03T09:00:00Z","price":20,"quantity":1}
{"_id":3,"item":"xyz","date":"2014-02-03T09:05:00Z","price":5,"quantity":5}
{"_id":4,"item":"abc","date":"2014-02-15T08:00:00Z","price":10,"quantity":10}
{"_id":5,"item":"xyz","date":"2014-02-15T09:05:00Z","price":5,"quantity":10}
{"_id":6,"item":"xyz","date":"2014-02-15T12:05:10Z","price":5,"quantity":5}
{"_id":7,"item":"xyz","date":"2014-02-15T14:12:12Z","price":5,"quantity":10}

按照item和date对文档进行排序，然后按item字段对现在已排序的文档进行分组，并计算每个物料的最后销售日期：

const $ = db.command.aggregate

db.collection('sales').aggregate().sort({item: 1, date: 1}).group({
    _id: "$item",
    lastSalesDate: $.last('$date')
}).end()

执行结果：

{"_id":"xyz","lastSalesDate":"2014-02-15T14:12:12Z"}
{"_id":"jkl","lastSalesDate":"2014-02-03T09:00:00Z"}
{"_id":"abc","lastSalesDate":"2014-02-15T08:00:00Z"}

max

返回一组数值的最大值。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}

按字段item分组，并计算每组文件的最大总量和最大数量。

db.collection('sales').groupBy('item').groupField('max(multiply(price, quantity)) as maxTotalAmount, max(quantity) as maxQuantity').get()

执行结果：

{"_id":"xyz","maxTotalAmount":50,"maxQuantity":10}
{"_id":"jkl","maxTotalAmount":20,"maxQuantity":1}
{"_id":"abc","maxTotalAmount":100,"maxQuantity":10}

min

返回一组数值的最小值。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}

按item字段对文档进行分组，并计算每次分组的最小数量和最小数量。

db.collection('sales').groupBy('item').groupField('min(quantity) as minQuantity').get()

执行结果：

{"item":"xyz","minQuantity":5}
{"item":"jkl","minQuantity":1}
{"item":"abc","minQuantity":2}

push

返回一组中表达式指定列与对应的值，一起组成的数组。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}
{"_id":6,"item":"xyz","price":5,"quantity":5,"date":"2014-02-15T12:05:10Z"}
{"_id":7,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T14:12:12Z"}

按date字段的日期和年份分组，并计算每组的物料清单和销售数量：

db.collection('sales').groupBy('dayOfYear(date) as day, year(date) as year').groupField('push({item: item, quantity: quantity}) as itemsSold').get()

执行结果：

{"day":46,"year":2014,"itemsSold":[{"item":"abc","quantity":10},{"item":"xyz","quantity":10},{"item":"xyz","quantity":5},{"item":"xyz","quantity":10}]}
{"day":34,"year":2014,"itemsSold":[{"item":"jkl","quantity":1},{"item":"xyz","quantity":5}]}
{"day":1,"year":2014,"itemsSold":[{"item":"abc","quantity":2}]}

stdDevPop

返回一组字段对应值的标准差。

示例集合users包含以下文档：

{"_id":1,"name":"dave123","quiz":1,"score":85}
{"_id":2,"name":"dave2","quiz":1,"score":90}
{"_id":3,"name":"ahn","quiz":1,"score":71}
{"_id":4,"name":"li","quiz":2,"score":96}
{"_id":5,"name":"annT","quiz":2,"score":77}
{"_id":6,"name":"ty","quiz":2,"score":82}

计算每个测验的标准偏差：

db.collection('users').groupBy('quiz').groupField('stdDevPop(score) as stdDev').get()

执行结果：

{"quiz":2,"stdDev":8.04155872120988}
{"quiz":1,"stdDev":8.04155872120988}

stdDevSamp

计算输入值的样本标准偏差。

示例集合users包含以下文档：

{_id:0,username:"user0",age:20}
{_id:1,username:"user1",age:42}
{_id:2,username:"user2",age:28}
...

为了计算用户样本的标准差，首先使用sample管道对100个用户进行采样，然后使用stdDevSamp计算样本用户的标准差。

db.collection('users').aggregate()
    .sample({size: 100})
    .group({
        _id: null,
        ageStdDev:{$stdDevSamp:"$age"}
    })
    .end()

该操作返回如下结果：

{"_id":null,"ageStdDev":7.811258386185771}

sum

返回一组字段所有数值的总和。

示例集合sales包含以下文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":"2014-01-01T08:00:00Z"}
{"_id":2,"item":"jkl","price":20,"quantity":1,"date":"2014-02-03T09:00:00Z"}
{"_id":3,"item":"xyz","price":5,"quantity":5,"date":"2014-02-03T09:05:00Z"}
{"_id":4,"item":"abc","price":10,"quantity":10,"date":"2014-02-15T08:00:00Z"}
{"_id":5,"item":"xyz","price":5,"quantity":10,"date":"2014-02-15T09:05:00Z"}

按照date字段的日期和年份对文档进行分组，并计算每组文档的总数和计数。

db.collection('sales').groupBy('dayOfYear(date) as day, year(date) as year').groupField('sum(multiply(price, quantity)) as totalAmount, sum(1) as count').get()

执行结果：

{"_id":{"day":46,"year":2014},"totalAmount":150,"count":2}
{"_id":{"day":34,"year":2014},"totalAmount":45,"count":2}
{"_id":{"day":1,"year":2014},"totalAmount":20,"count":1}

mergeObjects

将多个文档合并为一个文档。

注意：如果要合并的文档包含相同的字段名称，则结果文档中的字段将会覆盖上次合并的文档中的值。

示例集合sales包含以下文档：

{_id:1,year:2017,item:"A",quantity:{"2017Q1":500,"2017Q2":500}}
{_id:2,year:2016,item:"A",quantity:{"2016Q1":400,"2016Q2":300,"2016Q3":0,"2016Q4":0}}
{_id:3,year:2017,item:"B",quantity:{"2017Q1":300}}
{_id:4,year:2016,item:"B",quantity:{"2016Q3":100,"2016Q4":250}}

按照 item 进行分组，mergeObjects 仅接受单个对象操作。

db.collection('sales').groupBy('item').groupField('mergeObjects(quantity) as mergedSales').get()

执行结果：

{"_id":"B","mergedSales":{"2017Q1":300,"2016Q3":100,"2016Q4":250}}
{"_id":"A","mergedSales":{"2017Q1":500,"2017Q2":500,"2016Q1":400,"2016Q2":300,"2016Q3":0,"2016Q4":0}}

商城示例

Schema

// 商品表 goods
{
  "name": {
    "bsonType": "string",
    "description": "商品名称"
  },
  "category": {
    "bsonType": "string",
    "description": "商品分类"
  },
  "price": {
    "bsonType": "number",
    "description": "商品价格"
  },
  "stock": {
    "bsonType": "number",
    "description": "库存"
  }
}

// 订单表 orders
{
  "goodsId": {
    "bsonType": "string",
    "description": "商品ID",
    "foreignKey": "goods._id"
  },
  "buyNum": {
    "bsonType": "number",
    "description": "购买数量"
  },
  "userId": {
    "bsonType": "string",
    "description": "用户ID",
    "foreignKey": "users._id"
  },
  "createDate": {
    "bsonType": "timestamp",
    "description": "购买时间"
  }
}

// 用户表 users
{
  "name": {
    "bsonType": "string",
    "description": "用户名"
  }
}

示例数据

// goods
[
  {
    "_id": "6363666826e8170001b62baa",
    "category": "手机",
    "name": "iPhone 14 512G",
    "price": 8488,
    "stock": 100
  },
  {
    "_id": "6363666f3aeb530001925bb6",
    "category": "手机",
    "name": "iPhone 13 Pro Max 128G",
    "price": 9688,
    "stock": 20
  },
  {
    "_id": "63636678a899370001a22627",
    "category": "手机",
    "name": "华为 Meta P50",
    "price": 7699,
    "stock": 50
  },
  {
    "_id": "63636681ff2a54000133afd7",
    "category": "手机",
    "name": "小米 12 Ultra",
    "price": 4599,
    "stock": 200
  },
  {
    "_id": "6363668cd69bc10001765c5f",
    "category": "电脑",
    "name": "红米 Redmibook 15寸 256G",
    "price": 3999,
    "stock": 100
  },
  {
    "_id": "6363669353a7f30001b46c84",
    "category": "电脑",
    "name": "Macbook Pro 512G",
    "price": 10499,
    "stock": 100
  },
  {
    "_id": "6363669a26e8170001b63213",
    "category": "电脑",
    "name": "Macbook Air 128G",
    "price": 7699,
    "stock": 100
  }
]

// orders
[
  {
    "_id": "6364b2e8b653d6000163c4f3",
    "buyNum": 2,
    "createDate": 1664769600000,
    "goodsId": "6363666826e8170001b62baa",
    "userId": "6364cc82b653d6000167f848"
  },
  {
    "_id": "6364b2e8b653d6000163c4f4",
    "buyNum": 1,
    "createDate": 1664773200000,
    "goodsId": "6363668cd69bc10001765c5f",
    "userId": "6364cc82b653d6000167f848"
  },
  {
    "_id": "6364b2e8b653d6000163c4f5",
    "buyNum": 2,
    "createDate": 1664758800000,
    "goodsId": "6363669353a7f30001b46c84",
    "userId": "6364cc82b653d6000167f849"
  },
  {
    "_id": "6364b2e8b653d6000163c4f6",
    "buyNum": 1,
    "createDate": 1664769600000,
    "goodsId": "63636678a899370001a22627",
    "userId": "6364cc82b653d6000167f848"
  },
  {
    "_id": "6364b2e8b653d6000163c4f7",
    "buyNum": 3,
    "createDate": 1664769600000,
    "goodsId": "63636681ff2a54000133afd7",
    "userId": "6364cc82b653d6000167f848"
  },
  {
    "_id": "6364b2e8b653d6000163c4f8",
    "buyNum": 1,
    "createDate": 1664769600000,
    "goodsId": "6363668cd69bc10001765c5f",
    "userId": "6364cc82b653d6000167f849"
  },
  {
    "_id": "6364b2e8b653d6000163c4f9",
    "buyNum": 3,
    "createDate": 1664683200000,
    "goodsId": "6363669353a7f30001b46c84",
    "userId": "6364cc82b653d6000167f848"
  },
  {
    "_id": "6364b2e8b653d6000163c4fa",
    "buyNum": 2,
    "createDate": 1664679600000,
    "goodsId": "6363666f3aeb530001925bb6",
    "userId": "6364cc82b653d6000167f848"
  },
  {
    "_id": "6364b2e8b653d6000163c4fb",
    "buyNum": 1,
    "createDate": 1664697600000,
    "goodsId": "63636678a899370001a22627",
    "userId": "6364cc82b653d6000167f849"
  }
]

// users
[
  {
    "_id": "6364cc82b653d6000167f847",
    "name": "张三"
  },
  {
    "_id": "6364cc82b653d6000167f848",
    "name": "李四"
  },
  {
    "_id": "6364cc82b653d6000167f849",
    "name": "王五"
  }
]

按照商品的分类统计商品数量及库存

在本示例中使用 group 与 groupField，对应 Aggregate 阶段的 $group 与 $project 阶段。

将商品分类 category 字段用 groupBy 进行分组，在 groupField 中使用 sum 表达式对商品数量(goodsNumber)加1，库存(stock)同样也用 sum 表达式计算库存总和。

// JQL
db.collection('goods').groupBy('category').groupField('sum(1) as goodsNumber, sum(stock) as stock').get()

相比于上面 JQL 方式，下面的 Aggregate 就略显复杂一些，但大致逻辑是一致的。

在 Aggregate 第一阶段，按照商品分类进行分组，同时组内添加商品数量(goodsNumber)与库存(stock)字段，计算与上面计算方式一样，将结果传递至下一阶段。

第二阶段中，使用 $project 管道对字段修整，移除 _id 字段，增加 category 字段，保留 goodsNumber, stock 字段，如果使用 JQL 方式将不需要手动对字段修整。

// JQL Aggregate
const $ = db.command.aggregate
db.collection('goods').aggregate().group({
    _id: "$category",
    goodsNumber: $.sum(1),
    stock: $.sum('$stock')
}).project({
    _id: false,
    category: "$_id",
    goodsNumber: 1,
    stock: 1
}).end()

执行结果

[
    {
        "category": "手机",
        "goodsNumber": 4,
        "stock": 370
    },
    {
        "category": "电脑",
        "goodsNumber": 3,
        "stock": 300
    }
]

查询各分类下商品价格超过5000元的商品数量

使用 where 先初步筛选商品金额大于5000的商品，对筛选后的商品按照分类(category)使用 groupBy 分组，在 groupField 中使用 sum 表达式对商品数量(goodsNumber)加1。

// JQL
db.collection('goods').where('price > 5000').groupBy('category').groupField('sum(1) as goodsNumber').get()

在阶段一中使用 $match 管道按照商品金额筛选，将结果传递至下一阶段。

第二阶段中使用 $group 管道按照商品分类(category)分组，同时组内添加 goodsNumber 字段，使用 sum 表达式对数量加1。

第三阶段中使用 $project 管道对字段进行修整，移除 _id 字段，添加 category 字段值为上一阶段传递来的商品分类(category)，保留 goodsNumber 字段。

// JQL Aggregate
db.collection('goods').aggregate().match({
    price: $.gt(5000)
}).group({
    _id: '$category',
    goodsNumber: $.sum(1)
}).project({
    _id: 0,
    category: "$_id",
    goodsNumber: 1
}).end()

执行结果

[
  {
    "category": "手机",
    "goodsNumber": 3
  },
  {
    "category": "电脑",
    "goodsNumber": 2
  }
]

查询价格最高的商品和最低的商品

第一阶段使用 $sort 管道将 goods 表按照 price 倒叙排列，将结果传递到下一阶段。

第二阶段使用 $group 管道按照 category 分组，由于在第一阶段已经按照 price 倒叙排列了，所以在组内添加了 maxGoodsName, maxGoodsPrice 字段使用 $first 表达式来选择第一个文档，及 price 最大的一个表示价格最高的商品，相反，minGoodsName, minGoodsPrice 字段使用 $last 表达式来选择最后一个文档，代表价格最低的商品。

第三阶段使用 $project 管道对数据修整，移除 _id 字段，添加 category 字段值为上一阶段传递来的商品分类(category)，maxGoods 为价格最高的商品，minGoods 为价格最低的商品。

// JQL Aggregate
db.collection('goods').aggregate().sort({
    price: -1
}).group({
    _id: "$category",
    maxGoodsName: {$first: "$name"},
    maxGoodsPrice: {$first: "$price"},
    minGoodsName: {$last: "$name"},
    minGoodsPrice: {$last: "$price"}
}).project({
    _id: 0,
    category: "$_id",
    maxGoods: {
        name: "$maxGoodsName",
        price: "$maxGoodsPrice"
    },
    minGoods: {
        name: "$minGoodsName",
        price: "$minGoodsPrice"
    }
}).end()

执行结果

[
    {
        "category": "电脑",
        "maxGoods": {
            "name": "Macbook Pro 512G",
            "price": 10499
        },
        "minGoods": {
            "name": "红米 Redmibook 15寸 256G",
            "price": 3999
        }
    },
    {
        "category": "手机",
        "maxGoods": {
            "name": "iPhone 13 Pro Max 128G",
            "price": 9688
        },
        "minGoods": {
            "name": "小米 12 Ultra",
            "price": 4599
        }
    }
]

查询某天的平均客单价

第一阶段使用 $match 管道筛选某一天的订单，将结果传递至下一阶段

第二阶段使用 $group 管道使用 avg 表达式计算平均客单价

// JQL Aggregate
const $ = db.command.aggregate

db.collection('orders').aggregate().match({
    createDate: $.and($.gt(new Date('2022-10-03T00:00:00.000Z')), $.lt(new Date('2022-10-04T00:00:00.000Z')))
}).group({
    _id: null,
    avgPrice: $.avg('$price')
}).end()

执行结果

[
  {
    "_id": null,
    "avgPrice": 11364
  }
]

查询最受欢迎的商品

// JQL
const orders = db.collection('orders').getTemp()
db.collection(orders, 'goods')
    .foreignKey('orders.goodsId')
    .field('arrayElemAt(goodsId, 0) as goods, buyNum')
    .groupBy('goods._id as goodsId')
    .groupField('sum(buyNum) as count, first(goods.name) as name, first(goods.category) as category')
    .orderBy('count desc')
    .get()

// JQL Aggregate
db.collection('orders').aggregate().lookup({
    from: "goods",
    let: {
        goodsId: "$goodsId"
    },
    pipeline: $.pipeline()
        .match(db.command.expr(
            db.command.eq(['$_id', '$$goodsId'])
        )).done(),
    as: "goods"
}).unwind('$goods').group({
    _id: "$goods._id",
    count: $.sum("$buyNum"),
    name: $.first("$goods.name"),
    category: $.first("$goods.category")
}).sort({
    count: -1
}).end()

执行结果

[
  {
    "_id": "6363669353a7f30001b46c84",
    "category": "电脑",
    "count": 5,
    "name": "Macbook Pro 512G"
  },
  {
    "_id": "63636681ff2a54000133afd7",
    "category": "手机",
    "count": 3,
    "name": "小米 12 Ultra"
  },
  {
    "_id": "63636678a899370001a22627",
    "category": "手机",
    "count": 2,
    "name": "华为 Meta P50"
  },
  {
    "_id": "6363668cd69bc10001765c5f",
    "category": "电脑",
    "count": 2,
    "name": "红米 Redmibook 15寸 256G"
  },
  {
    "_id": "6363666826e8170001b62baa",
    "category": "手机",
    "count": 2,
    "name": "iPhone 14 512G"
  },
  {
    "_id": "6363666f3aeb530001925bb6",
    "category": "手机",
    "count": 2,
    "name": "iPhone 13 Pro Max 128G"
  }
]

统计购买手机的用户

// JQL
const orders = db.collection('orders').getTemp()
db.collection(orders, 'goods', 'users')
    .where('goodsId.category=="手机"')
    .field('arrayElemAt(goodsId, 0) as goods, arrayElemAt(userId, 0) as user')
    .groupBy('goods.category as category')
    .groupField('addToSet(user) as users')
    .get()

// Aggregate
db.collection('orders').aggregate().lookup({
    from: "goods",
    let: {
        goodsId: "$goodsId"
    },
    pipeline: $.pipeline().match(db.command.expr(
        db.command.eq(['$_id', '$$goodsId'])
    )).done(),
    as: "goods"
}).match({
    'goods.category': "手机"
}).unwind("$goods").lookup({
    from: "users",
    let: {
        userId: "$userId"
    },
    pipeline: $.pipeline().match(db.command.expr(
        db.command.eq(['$_id', '$$userId'])
    )).done(),
    as: "user"
}).unwind('$user').group({
    _id: "$goods.category",
    users: {$addToSet: "$user"}
}).end()

执行结果

[
  {
    "category": "手机",
    "users": [
      {
        "_id": "6364cc82b653d6000167f849",
        "name": "王五"
      },
      {
        "_id": "6364cc82b653d6000167f848",
        "name": "李四"
      }
    ]
  }
]