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-19 16:37:09Z

1

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

edited May 19, 2020 at 16:37

answered May 19, 2020 at 16:31

zishone

1,2441 gold badge10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sameesh Over a year ago

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.

zishone Over a year ago

Add quotations to dot notation paths: 'parent.field1'

Collectives™ on Stack Overflow

MongoDB Aggregation pipeline inner array mapping

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related