MongoDB Aggregation pipeline inner array mapping

Question

I am trying to perform a lookup operation between two collections like shown below,

first collection records:

{
field1: "FIELD",
title: "sometitle",
secondIds: [
  { 
   value: "nestedval1",
   secondId: "234
  }, 
  {
   value: "netedval2,
   secondId: "342"
  }
  ]
}

Second collection record

{
 id: "234", 
 secvalue: "somevalue"
},
{
 id: "342",
 secvalue: "anothervalue"
}

I am trying to get the output in the below format for matching field1 name "FIELD" inside the first collection.

{
 field1: "FIELD",
 title: "sometitle",
 secondIds: [
  {
   value: "nestedval1",
   secondId: "234",
   second: {
    id: "234",
    secvalue: "somevalue"
   }
  },
  {
   value: "nestedval2",
   secondId: "342",
   second: {
    id: "342",
    secvalue: "anothervalue"
    }
   }
 ]

}

for aggregation pipeline after matching operation, I still stuck at how to create a lookup operation for retrieving the second collection entry mapped with the first. Can it possible to do it or do have any other way to achieve it?

zishone · Accepted Answer · 2020-05-19T16:37:09.043

firstCollection.aggregate([
  { $unwind: '$secondIds' },           // Lets first separate the secondIds to as different documents
  {
    $lookup: {
      from: 'secondCollection',            // second collection name
      localField: 'secondIds.secondId',    // field in first collection after unwind
      foreignField: 'id',                  // field in second collection
      as: 'secondIds.second'               // field to attach values matched
    }
  },
  { $unwind: '$secondIds.second' },        // attached value after lookup will be an array, so let unwind to make it a object
  { $project: { 'secondIds.second._id': 0 } },  // remove _id
  {
    $group: {
      _id: {                              // grouper fields
        field1: "$field1",
        title: "$title",
      },
      secondIds: { $push: '$secondIds' } // group by pushing to an array
    }
  },
  {
    $project: {                 // projection
      _id: 0,
      field1: '$_id.field1',
      title: "$_id.title",
      secondIds: 1
    }
  }
]).pretty()

Explanations are in the comments

Thanks @zishone appreciate your help, apparently my real database structure my first record is also inside one parent record, parent record> first record > second record. So when I group the items like { parent.field1 : "$parent.field1" } it is giving error for Field path including "." , Is there any way to solve this, thanks. — Sameesh, May 19 '20 at 17:56

MongoDB Aggregation pipeline inner array mapping

1 Answers1