I'm trying to aggregate the following document to get the participants as an object in the nested array.
{
"name": "EXAMPLE",
"schedules": [
{
"schedule_id": "id1",
"participants": [
"participant_id1",
"participant_id2"
],
},
{
"schedule_id": "id2",
"participants": [
"participant_id1",
"participant_id2"
],
},
{
"schedule_id": "id3",
"participants": [
"participant_id1"
],
},
],
}
Therefore I wrote the following Pipeline:
[
{
$unwind: {
path: "$schedules",
includeArrayIndex: "index",
preserveNullAndEmptyArrays: true,
}
},
{
$unwind: {
path: "$schedules.participants",
includeArrayIndex: "index",
preserveNullAndEmptyArrays: true,
}
},
{
$lookup: {
from: "customers",
localField: "schedules.participants",
foreignField: "_id",
as: "participants",
}
},
{
$project: {
"participants.address": 0,
"participants.birthday": 0,
}
},
{
$unwind: {
path: "$participants",
preserveNullAndEmptyArrays: true,
}
},
{
$group:
{
_id: "$_id",
name: {
$first: "$name",
},
schedules: {
$first: "$schedules",
},
}
},
]
- The first step in this pipeline is to unwind the schedules array to get each individual schedule in a document.
- The second step is to unwind the participants, because I need the participant ids to do the lookup process in the third step.
- The third step is to lookup for the participant in the customers collection, the return will be a customer object.
- In the fourth step, I'll use project to remove unneccesary fields from the given participant.
- In the fifth step I use unwind again to get the single participant (I know $first operator could be used too)
- In the sixth step I'll group it
I am trying to add each participant from step 3 to the corresponding schedule object into the participants array, the document should be like this:
{
"name": "EXAMPLE",
"schedules": [
{
"schedule_id": "id1",
"participants": [
{
id: "id1",
"name": "name1"
},
{
id: "id2",
"name": "name2"
},
],
},
{
"schedule_id": "id2",
"participants": [
{
id: "id1",
"name": "name1"
},
{
id: "id2",
"name": "name2"
},
],
},
{
"schedule_id": "id3",
"participants": [
{
id: "id1",
"name": "name1"
},
],
},
],
}
CodePudding user response：
You have the right idea, you can just simplify your pipeline thus making it much easier to reconstruct, the second $unwind is redundant, dropping it will allow us to reconstruct the object using only 1 group stage. Which is obviously much simpler.
db.collection.aggregate([
{
$unwind: {
path: "$schedules",
includeArrayIndex: "index",
preserveNullAndEmptyArrays: true,
}
},
{
$lookup: {
from: "customers",
localField: "schedules.participants",
foreignField: "_id",
as: "participants",
}
},
{
$project: {
"participants.address": 0,
"participants.birthday": 0,
}
},
{
$group: {
_id: "$_id",
schedules: {
$push: {
schedule_id: "$schedules.schedule_id",
participants: "$participants"
}
},
name: {
$first: "$name"
}
}
}
])