I have two collections Course and File.
This is my data from Course collection
[{
_id: 1,
name: "Course1",
price: "100"
},
{
_id: 2,
name: "Course2",
price: "200"
}]
This is my data from files collection
[{
_id: 1,
name: "File1",
isFree: true,
courseId: 1
}
{
_id: 2,
name: "File2",
isFree: false,
courseId: 1
}
{
_id: 3,
name: "File3",
isFree: false,
courseId: 1
}]
I want to retrieve Course 1 with Course name,Count of files which are free,Count of files which are not free.
My attempt:
Course.aggregate([
{$lookup:{
from:"files",
localField: "_id",
foreignField: "courseId",
as: "files"
}},
])
This is my response If I execute my above query:
[
{
"_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
}
]
}
]
I want my output as:
[
{
"_id": "6161bdcc0aada09d81598840",
"name": "Course1",
"files": {"free": 1,"paid": 2}
}
]
You can try lookup with pipeline,
$lookup with pipeline, pass courseId in let$match courseId condition$group by isFeee and get total count$project to show required fields in k(key) and v(value) format$cond to check if isFree true then return "free" otherwise return "paid"$addFields to update files array$arrayToObject convert files key-value array to an object$ifNull to check if property is not exists then set 0await 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] }
}
}
])
Try following:
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,
},
},
]);