How to add objects into array from another collection

Hi,
I was working with the data and got stuck in a problem that might be small for you.
I want to merge the objects and append it to the Array here are sample collections

COLLECTION A

{
  "customerId": "111",
  "name": "Adil",
  "satisfactionLevels": [{
      "A": "Score 1",
      "B": "R",
      "C": 2,
      "D": 1
  }, {
      "A": "Score 2",
      "B": "S",
      "C": 2,
      "D": 2
  }]
  
}

COLLECTION B

{ 
  "customerId": "111",
  "sScore": "20.54",
  "intId": "1527"  
  
}

EXPECTED OUTPUT

{
  "customerId": "111",
  "name": "Adil",
  "satisfactionLevels": [{
      "A": "Score 1",
      "B": "R",
      "C": 2,
      "D": 1
  }, {
      "A": "Score 2",
      "B": "S",
      "C": 2,
      "D": 2
  }, { 
  "customerId": "111",
  "sScore": "20.54",
  "intId": "1527"  
  
}]
  
}

That’s the expected output how can I achieve this. Thanks in advance

Hello @Nabeel_Raza,

You can use $lookup pipeline stage to join collection B in A,
Example:

  {
    $lookup: {
      from: "colB",
      localField: "customerId",
      foreignField: "customerId",
      as: "colB"
    }
  }

For more information see $lookup documentation:

It will return array of object, to access object from zero index try $arrayElemAt operator, it will return object from zero index,

  {
    $addFields: {
      colB: { $arrayElemAt: ["$colB", 0] }
    }
  }

For more information see $arrayElemAt documentation:

I think you didn’t understand my question. I want the resultant output in whole. There will be more than one document having same customerId so for that can’t use your stagey. Secondly i want to append the array object which is already exists in Collection A.

@Nabeel_Raza,

To better answer this question it might help if you can clarify the following:

  1. Will there be multiple matches for customerId in COLLECTION1 (assuming yes)?
  2. Will there be multiple matches for customerId in COLLECTION2?
  3. Are you only looking to merge the satisfactionLevels arrays from each matching document in COLLECTION1?
  4. If there are multiple matches for customerId in COLLECTION2 what is expected to happen in the final document?
1 Like
  1. There will be ONE document against a single customerId in COLLECTION 1
  2. There will be MANY document against a single customerId in COLLECTION 2
  3. Yes, I want to merge them on the bases of customerId.
  4. if multiple matched then simply append those document to the “satisfactionLevels” array in COLLECTION 1.

The following pipeline using $project, $reduce and $concatArrays should produce the desired result:

// SETUP
db.coll1.drop();
db.coll1.createIndex({ customerId: 1 });
db.coll2.drop();
db.coll2.createIndex({ customerId: 1 });

db.coll1.insertMany([{
  "customerId": "111",
  "name": "Adil",
  "satisfactionLevels": [{
      "A": "Score 1",
      "B": "R",
      "C": 2,
      "D": 1
  }, {
      "A": "Score 2",
      "B": "S",
      "C": 2,
      "D": 2
  }]
  
},
{
  "customerId": "111",
  "name": "Adil",
  "satisfactionLevels": [{
      "A": "Score 3",
      "B": "R",
      "C": 5,
      "D": 6
  }, {
      "A": "Score 4",
      "B": "S",
      "C": 8,
      "D": 9
  }]
  
}]);

db.coll2.insert({ 
  "customerId": "111",
  "sScore": "20.54",
  "intId": "1527"    
})
// PIPELINE
db.coll2.aggregate([
{ $match: { customerId: "111" } },
{ $lookup: {
  from: "coll1",
  localField: "customerId",
  foreignField: "customerId",
  as: "colB"
}},
{ $project: {
    customerId: 1,
    name: 1,
    satisfactionLevels: { 
        $reduce: {
            input: "$colB.satisfactionLevels",
            initialValue: [],
            in: { $concatArrays: [ "$$value", "$$this" ] }
        }
    },
    sScore: 1,
    intId: 1
    
}}
]);

Thanks for the reply. You swap the collection. I need to add the items to collection A which already have field “satisfactionLevels”

EXPECTED OUTPUT
{
“customerId”: “111”,
“name”: “Adil”,
“satisfactionLevels”: [{
“A”: “Score 1”,
“B”: “R”,
“C”: 2,
“D”: 1
}, {
“A”: “Score 2”,
“B”: “S”,
“C”: 2,
“D”: 2
}, {
“customerId”: “111”,
“sScore”: “20.54”,
“intId”: “1527”

}]
  
}

@Nabeel_Raza,
I can understand your expected result for single matching document in collection B, can you provide expected result for 2 or more matching documents in collection B.

Assume that we have same document in collection B but the values are different.

@Nabeel_Raza,
If you modified my example to add another document to coll2:

db.coll2.insertMany([{ 
 "customerId": "111",
 "sScore": "20.54",
 "intId": "1527"    
},
{ 
 "customerId": "111",
 "sScore": "99.47",
 "intId": "1927"    
}])

The results of the pipeline would be 2 documents:

{ 
    "_id" : ObjectId("5ff6fe56378146f0309052c4"), 
    "customerId" : "111", 
    "sScore" : "20.54", 
    "intId" : "1527", 
    "satisfactionLevels" : [
        {
            "A" : "Score 1", 
            "B" : "R", 
            "C" : 2.0, 
            "D" : 1.0
        }, 
        {
            "A" : "Score 2", 
            "B" : "S", 
            "C" : 2.0, 
            "D" : 2.0
        }, 
        {
            "A" : "Score 3", 
            "B" : "R", 
            "C" : 5.0, 
            "D" : 6.0
        }, 
        {
            "A" : "Score 4", 
            "B" : "S", 
            "C" : 8.0, 
            "D" : 9.0
        }
    ]
}
{ 
    "_id" : ObjectId("5ff6fe56378146f0309052c5"), 
    "customerId" : "111", 
    "sScore" : "99.47", 
    "intId" : "1927", 
    "satisfactionLevels" : [
        {
            "A" : "Score 1", 
            "B" : "R", 
            "C" : 2.0, 
            "D" : 1.0
        }, 
        {
            "A" : "Score 2", 
            "B" : "S", 
            "C" : 2.0, 
            "D" : 2.0
        }, 
        {
            "A" : "Score 3", 
            "B" : "R", 
            "C" : 5.0, 
            "D" : 6.0
        }, 
        {
            "A" : "Score 4", 
            "B" : "S", 
            "C" : 8.0, 
            "D" : 9.0
        }
    ]
}

What would the expected result be given this scenario if you were returning a single document?

You’ve listed "customerId" twice in your sample output, however you cannot duplicate fields withina document like that (only 1 field can be called "customerId" in a single document at the same level).

But the Collection 1 is the main Collection which should be on the top and all it’s fields should be in the output. And you made the Collection B as the main collection.

The query should starts with this

db.coll1.aggregate([
{ $match: { customerId: “111” } },
{ $lookup: {
from: “coll2”,
localField: “customerId”,
foreignField: “customerId”,
as: “colB”
}},

Okay, can you share expected result when your collection have below data:

Collection A:

Summary
  {
  "customerId": "111",
  "name": "Adil",
  "satisfactionLevels": [{
      "A": "Score 1",
      "B": "R",
      "C": 2,
      "D": 1
  }, {
      "A": "Score 2",
      "B": "S",
      "C": 2,
      "D": 2
  }] 
}

Collection B:

Summary
{ 
  "customerId": "111",
  "sScore": "20.54",
  "intId": "1527"  
},
{ 
  "customerId": "111",
  "sScore": "50.54",
  "intId": "1528"  
}

We got your start query.

What will be the result document as per these inputs?

Collection A

{
“customerId”: “111”,
“name”: “Adil”,
“satisfactionLevels”: [{
“A”: “Score 1”,
“B”: “R”,
“C”: 2,
“D”: 1
}, {
“A”: “Score 2”,
“B”: “S”,
“C”: 2,
“D”: 2
}]}

Collection B

//1
{
“customerId”: “111”,
“sScore”: “20.54”,
“intId”: “1527”
}
//2
{
“customerId”: “111”,
“sScore”: “99.47”,
“intId”: “1927”
}

Result:

{
  "customerId": "111",
  "name": "Adil",
  "satisfactionLevels": [{
      "A": "Score 1",
      "B": "R",
      "C": 2,
      "D": 1
  }, {
      "A": "Score 2",
      "B": "S",
      "C": 2,
      "D": 2
  },
  { 
	"customerId": "111",
	"sScore": "20.54",
	"intId": "1527"    
   },
  { 
	"customerId": "111",
	"sScore": "99.47",
	"intId": "1927"    
	}

  
  ]
  
}

The implementation of your query, just need to add $project stage and concat both arrays using $concatArrays operator.

db.colA.aggregate([
  {
    $match: {
      customerId: "111"
    }
  },
  {
    $lookup: {
      from: "colB",
      localField: "customerId",
      foreignField: "customerId",
      as: "colB"
    }
  },
  {
    $project: {
      customerId: 1,
      name: 1,
      satisfactionLevels: {
        $concatArrays: ["$satisfactionLevels", "$colB"]
      }
    }
  }
])

Playground

Oh yeah that was the correct operator. Thanks Turivishal for helping me.
Great efforts (y)

Except you don’t want $project you want $addFields (or its alias $set) otherwise you lose all the fields that already existed in the original document in Collection A. So last stage should be:

{$set:{
    satisfactionLevels: { $concatArrays: ["$satisfactionLevels", "$colB"] }
 } }
1 Like

Thank you for your reply, There is no need of colB array field in result because it is already concat in satisfactionLevels, there are 2 options:
First: either we need to remove that field using colB: "$$REMOVE" in $set or $addFields stage,

{
  $addFields: {
    colB: "$$REMOVE",
    satisfactionLevels: { $concatArrays: ["$satisfactionLevels", "$colB"] }
  }
}

Second: Either we can use $project stage and specify required fields for result,

I am not sure which is the best, is there any performance issue when we use First? Is First good, is Second good or both are equal in performance, please share your thoughts.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.