Hello, I’m having some issues getting my aggregation to work. I have a $lookup where i join two collections, in the joined one I there is nested arrays with objects that I want to match in. The matching part seems to work by I’m not getting the structure back that I’m expecting in the $group stage after $unwind .
Here is a mongo playground with sample data Mongo playground
The original joined document has a structure like this where I among others match on some of the approvalStatus fields and I get the correct ones. But i also want to keep the fields in the “parent” i.e version, allHaveSigned, isLatestVersion etc.
"agreements": [
{
"_id": {
"$oid": "612c8d394459ebd2483a0920"
},
"agreementVersion": [
{
"version": 1,
"allHaveSigned": false,
"_id": {
"$oid": "612c8d394459ebd2483a0921"
},
"approvalStatus": [
{
"_id": {
"$oid": "612c8d394459ebd2483a0922"
},
"publisherSignsFor": {
"$oid": "60801bef03a9cd5f4a097e76"
},
"signingPerson": {
"$oid": "60d06fc53a7fc65be3787df8"
},
"isSignedByPublisher": true
},
{
"_id": {
"$oid": "612c8d394459ebd2483a0923"
},
"publisherSignsFor": null,
"signingPerson": {
"$oid": "60d06fc53a7fc65be3787df8"
},
"isSignedByPublisher": false
},
{
"_id": {
"$oid": "612c8d394459ebd2483a0921"
},
"publisherSignsFor": {
"$oid": "60801bef03a9cd5f4a097e76"
},
"signingPerson": {
"$oid": "60d06fc53a7fc65be3787df9"
},
"isSignedByPublisher": true
}
],
"isLatestVersion": true,
"createdAt": {
"$date": "2021-08-30T07:48:09.979Z"
}
}
],
This is what my pipeline looks like
db.songs.aggregate([
{
"$lookup": {
"from": "agreements",
"localField": "agreements.songComposition",
"foreignField": "_id",
"as": "agreements.songComposition"
}
},
{
"$unwind": {
"path": "$agreements.songComposition",
"preserveNullAndEmptyArrays": false
}
},
{
"$unwind": {
"path": "$agreements.songComposition.agreementVersion",
"preserveNullAndEmptyArrays": false
}
},
{
"$unwind": {
"path": "$agreements.songComposition.agreementVersion.approvalStatus",
"preserveNullAndEmptyArrays": false
}
},
{
"$match": {
"$and": [
{
"agreements.songComposition.agreementVersion.isLatestVersion": true
},
{
"agreements.songComposition.agreementVersion.approvalStatus.signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
}
],
"$or": [
{
"agreements.songComposition.agreementVersion.approvalStatus.isApprovedByUser": false
},
{
"agreements.songComposition.agreementVersion.approvalStatus.isApprovedByUser": {
"$exists": false
}
}
]
}
},
{
"$facet": {
"songs": [
{
"$group": {
"_id": "$_id",
"title": {
"$first": "$title"
},
"artistName": {
"$first": "$artistName"
},
"agreements": {
"$push": "$agreements.songComposition"
},
"count": {
"$sum": 1
}
}
}
],
"countAgreements": [
{
"$group": {
"_id": null,
"count": {
"$sum": 1
}
}
}
]
}
},
{
"$project": {
"songs": 1,
"sumAgreements": {
"$arrayElemAt": [
"$countAgreements.count",
0
]
}
}
}
])
The result looks like this and I get two elements in the agreements array where it should only be one and I think it is because I cant join on the approvalStatus fields correctly or missing something else.
[
{
"songs": [
{
"_id": ObjectId("60debdaae7593406b682e45d"),
"agreements": [
{
"__v": 0,
"_id": ObjectId("612c8d394459ebd2483a0920"),
"addedAt": ISODate("2021-08-30T07:48:09.979Z"),
"agreementVersion": {
"_id": ObjectId("612c8d394459ebd2483a0921"),
"allHaveSigned": false,
"approvalStatus": {
"_id": ObjectId("612c8d394459ebd2483a0922"),
"isSignedByPublisher": true,
"publisherSignsFor": ObjectId("60801bef03a9cd5f4a097e76"),
"signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
},
"createdAt": ISODate("2021-08-30T07:48:09.979Z"),
"isLatestVersion": true,
"version": 1
}
},
{
"__v": 0,
"_id": ObjectId("612c8d394459ebd2483a0920"),
"addedAt": ISODate("2021-08-30T07:48:09.979Z"),
"agreementVersion": {
"_id": ObjectId("612c8d394459ebd2483a0921"),
"allHaveSigned": false,
"approvalStatus": {
"_id": ObjectId("612c8d394459ebd2483a0923"),
"isSignedByPublisher": false,
"publisherSignsFor": null,
"signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
},
"createdAt": ISODate("2021-08-30T07:48:09.979Z"),
"isLatestVersion": true,
"version": 1
}
}
],
"artistName": "My test artisters",
"count": 2,
"title": "Branches"
}
],
"sumAgreements": 2
}
]
The result I would like to have looks like this
[
{
"songs": [
{
"_id": ObjectId("60debdaae7593406b682e45d"),
"agreements": [
{
"__v": 0,
"_id": ObjectId("612c8d394459ebd2483a0920"),
"addedAt": ISODate("2021-08-30T07:48:09.979Z"),
"agreementVersion": [
{
"_id": ObjectId("612c8d394459ebd2483a0921"),
"allHaveSigned": false,
"approvalStatus": [
{
"_id": ObjectId("612c8d394459ebd2483a0922"),
"isSignedByPublisher": true,
"publisherSignsFor": ObjectId("60801bef03a9cd5f4a097e76"),
"signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
},
{"_id": ObjectId("612c8d394459ebd2483a0923"),
"isSignedByPublisher": false,
"publisherSignsFor": null,
"signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
},
],
"createdAt": ISODate("2021-08-30T07:48:09.979Z"),
"isLatestVersion": true,
"version": 1
}
]
},
],
"artistName": "My test artisters",
"count": 2,
"title": "Branches"
}
],
"sumAgreements": 2
}
]
Thankful for any help!
Going to get leveled up on the 
…and welcome to the community! I love it here. Have fun and I hope you find all the answers that you are looking for.