CODEWITHSUNDEEP
mongodbaggregation-framework
Jimmy Nelle
Jimmy Nelle

Reputation: 73

MongoDB Aggregation: How to put results from lookup into a nested array?

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",
        
      },
      
    }
  },
  
]
  1. The first step in this pipeline is to unwind the schedules array to get each individual schedule in a document.
  2. The second step is to unwind the participants, because I need the participant ids to do the lookup process in the third step.
  3. The third step is to lookup for the participant in the customers collection, the return will be a customer object.
  4. In the fourth step, I'll use project to remove unneccesary fields from the given participant.
  5. In the fifth step I use unwind again to get the single participant (I know $first operator could be used too)
  6. 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"
          },
          
        ],
        
      },
      
    ], 
  }

Upvotes: 3

Views: 73

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

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"
      }
    }
  }
])

Mongo Playground

Upvotes: 2

Related Questions