运行以下聚合管道以获得所需结果:
db.students.aggregate([
{
"$project": {
"name": 1,
"age": 1,
"participant": {
"$size": {
"$ifNull" : [
{
"$setIntersection" : [
{
"$map": {
"input": "$courses",
"as": "el",
"in": {
"$eq": [ "$$el.name", "Databases" ]
}
}
},
[true]
]
},
[]
]
}
}
}
}
])
输出:
{
"result" : [
{
"_id" : ObjectId("564f1bb67d3c273d063cd216"),
"name" : "Alice",
"age" : 25,
"participant" : 1
},
{
"_id" : ObjectId("564f1bb67d3c273d063cd217"),
"name" : "Bob",
"age" : 22,
"participant" : 0
},
{
"_id" : ObjectId("564f1bb67d3c273d063cd218"),
"name" : "Carol",
"age" : 19,
"participant" : 1
},
{
"_id" : ObjectId("564f1bb67d3c273d063cd219"),
"name" : "Dave",
"age" : 18,
"participant" : 0
}
],
"ok" : 1
}
上述管道仅使用一个步骤,即
$project
,其中通过一系列嵌套运算符创建了新字段
participant
。
关键操作是深度嵌套的
$map
运算符,其本质上创建一个新的数组字段,该字段保存作为子表达式中逻辑评估结果的值,并将其应用于数组的每个元素。让我们仅通过执行带有
$map
部分的管道来演示此操作:
db.students.aggregate([
{
"$project": {
"name": 1,
"age": 1,
"participant": {
"$map": {
"input": "$courses",
"as": "el",
"in": {
"$eq": [ "$$el.name", "Databases" ]
}
}
}
}
}
])
输出
{
"result" : [
{
"_id" : ObjectId("564f1bb67d3c273d063cd216"),
"name" : "Alice",
"age" : 25,
"participant" : [
true,
false
]
},
{
"_id" : ObjectId("564f1bb67d3c273d063cd217"),
"name" : "Bob",
"age" : 22,
"participant" : [
false
]
},
{
"_id" : ObjectId("564f1bb67d3c273d063cd218"),
"name" : "Carol",
"age" : 19,
"participant" : [
true
]
},
{
"_id" : ObjectId("564f1bb67d3c273d063cd219"),
"name" : "Dave",
"age" : 18,
"participant" : null
}
],
"ok" : 1
}
通过引入$setIntersection
运算符进一步探测数组,该运算符返回一个包含所有输入集合中出现的元素的集合。因此,在上面的示例中,您需要获取一个结果数组,其中true表示用户参加了数据库课程,否则将返回一个空或null数组。让我们看看如何添加该运算符会影响先前的结果:
db.students.aggregate([
{
"$project": {
"name": 1,
"age": 1,
"participant": {
"$setIntersection" : [
{
"$map": {
"input": "$courses",
"as": "el",
"in": {
"$eq": [ "$$el.name", "Databases" ]
}
}
},
[true]
]
}
}
}
])
输出:
{
"result" : [
{
"_id" : ObjectId("564f1bb67d3c273d063cd216"),
"name" : "Alice",
"age" : 25,
"participant" : [
true
]
},
{
"_id" : ObjectId("564f1bb67d3c273d063cd217"),
"name" : "Bob",
"age" : 22,
"participant" : []
},
{
"_id" : ObjectId("564f1bb67d3c273d063cd218"),
"name" : "Carol",
"age" : 19,
"participant" : [
true
]
},
{
"_id" : ObjectId("564f1bb67d3c273d063cd219"),
"name" : "Dave",
"age" : 18,
"participant" : null
}
],
"ok" : 1
}
为了处理空值,应用
$ifNull
运算符,相当于SQL中的coalesce命令,用于将null值替换为空数组:
db.students.aggregate([
{
"$project": {
"name": 1,
"age": 1,
"participant": {
"$ifNull" : [
{
"$setIntersection" : [
{
"$map": {
"input": "$courses",
"as": "el",
"in": {
"$eq": [ "$$el.name", "Databases" ]
}
}
},
[true]
]
},
[]
]
}
}
}
])
接下来,您可以使用$size
操作符将$ifNull
操作符包装在一起,以返回参与者数组中的元素数量,并产生如上所示的最终输出。