mongodb查询返回计数?

Question

我有两个集合Course和File。这是我从Course收集的数据

[{
_id: 1,
name: "Course1",
price: "100"
},
{
_id: 2,
name: "Course2",
price: "200"
}]

这是我的数据从文件收集

[{
_id: 1,
name: "File1",
isFree: true,
courseId: 1
}
{
_id: 2,
name: "File2",
isFree: false,
courseId: 1
}
{
_id: 3,
name: "File3",
isFree: false,
courseId: 1
}]

我想检索课程1与Course name,Count of files which are free,Count of files which are not free。

我的尝试:

Course.aggregate([
{$lookup:{
from:"files",
localField: "_id",
foreignField: "courseId",
as: "files"
}},
])

这是我的响应，如果我执行上面的查询:

[
{
"_id": "6161bdcc0aada09d81598840",
"name": "Course1",
"files": [
{
"_id": "6166a0814ad3f56b71232b9c",
"name": "File1.jpeg",
"isFree":true
},
{
"_id": "6166a0814ad3f56b71232b9d",
"name": "File2.jpeg",
"isFree":false
},
{
"_id": "6166a0814ad3f56b71232b9v",
"name": "File3.jpeg",
"isFree":false
}
]
}
]

我希望我的输出为:

[
{
"_id": "6161bdcc0aada09d81598840",
"name": "Course1",
"files": {"free": 1,"paid": 2}
}
]

Answer 1

您可以尝试使用管道查找，

• $lookup带管道，在let中传递courseId
• $matchcourseIdcondition
• $groupbyisFeee并得到总数
• $project显示k(键)和v(值)格式的必填字段
• $cond检查isFree是否为true然后返回"free"否则返回"已付款">
• $addFields将更新files数组
• $arrayToObject将files键值数组转换为对象
• $ifNull检查属性是否不存在，然后设置0

await Course.aggregate([
{
$lookup: {
from: "files",
let: { courseId: "$_id" },
pipeline: [
{ $match: { $expr: { $eq: ["$$courseId", "$courseId"] } } },
{
$group: {
_id: "$isFree",
count: { $sum: 1 }
}
},
{
$project: {
_id: 0,
k: { $cond: ["$_id", "free", "paid"] },
v: "$count"
}
}
],
as: "files"
}
},
{
$addFields: {
files: { $arrayToObject: "$files" }
}
},
{
$addFields: {
"files.free": { $ifNull: ["$files.free", 0] },
"files.paid": { $ifNull: ["$files.paid", 0] }
}
}
])

游乐场

Answer 2

尝试如下:

db.course.aggregate([
{
$lookup: {
from: "files",
localField: "_id",
foreignField: "courseId",
as: "files",
},
},
{
$unwind: {
path: "$files",
preserveNullAndEmptyArrays: true,
},
},
{
$group: {
_id: "$_id",
name: {
$first: "$name",
},
price: {
$first: "$price",
},
free: {
$sum: {
$cond: [
{
$eq: ["$files.isFree", true],
},
1,
0,
],
},
},
paid: {
$sum: {
$cond: [
{
$eq: ["$files.isFree", false],
},
1,
0,
],
},
},
},
},
{
$addFields: {
"files.free": "$free",
"files.paid": "$paid",
},
},
{
$project: {
_id: 1,
name: 1,
files: 1,
price: 1,
},
},
]);