Merge collections in MongoDB Version Less than 4.2 (or Amazon DocumentDB)

Reading Time: 4 minutes

Using MongoDB (a document database designed for ease of development and scaling) with the older version or using Amazon DocumentDB (a scalable, highly durable, and fully managed database service for operating mission-critical MongoDB workloads compatible with MongoDB version 3.6 and version 4.0), they have some limited features and one of the limitation of using them is that they don’t allow the direct merge of two or more collections. Let us discuss on how we can achieve this with the step by step guide. Let us see an example to merge two or more collections with these limited feature versions for more clarification.

For example, we will use the database example which has two collections –

beginnersbook

> db.beginnersbook.find().pretty()
{
	"_id" : ObjectId("61c19331f2ea1f93a0a74841"),
	"name" : "Chaitanya",
	"age" : 30,
	"email" : "admin@beginnersbook.com",
	"course" : [
		{
			"name" : "MongoDB",
			"duration" : 7
		},
		{
			"name" : "Java",
			"duration" : 30
		}
	]
}
{
	"_id" : ObjectId("61c19375f2ea1f93a0a74842"),
	"name" : "Vaibhav",
	"age" : 28,
	"email" : "vaibhav@beginnersbook.com",
	"course" : [
		{
			"name" : "MySQL",
			"duration" : 7
		},
		{
			"name" : "Java",
			"duration" : 30
		}
	]
}
{
	"_id" : ObjectId("61c193b1f2ea1f93a0a74843"),
	"name" : "Shruthi",
	"age" : 29,
	"email" : "shruthi@beginnersbook.com",
	"course" : [
		{
			"name" : "MySQL",
			"duration" : 7
		},
		{
			"name" : "C++",
			"duration" : 30
		}
	]
}

and favouritebook

> db.favouritebook.find().pretty()
{
	"_id" : ObjectId("61c19468f2ea1f93a0a74844"),
	"name" : "Deepti",
	"age" : 32,
	"email" : "deepti@favouritebook.com",
	"course" : [
		{
			"name" : "MicroServices",
			"duration" : 7
		},
		{
			"name" : "Spring Boot",
			"duration" : 30
		}
	]
}
{
	"_id" : ObjectId("61c194d2f2ea1f93a0a74845"),
	"name" : "Pallavi",
	"age" : 27,
	"email" : "pallavi@favouritebook.com",
	"course" : [
		{
			"name" : "Docker",
			"duration" : 7
		},
		{
			"name" : "JavaScript",
			"duration" : 30
		}
	]
}
{
	"_id" : ObjectId("61c19517f2ea1f93a0a74846"),
	"name" : "Sameer",
	"age" : 25,
	"email" : "sameer@favouritebook.com",
	"course" : [
		{
			"name" : "Hibernate",
			"duration" : 7
		},
		{
			"name" : "JDBC",
			"duration" : 30
		}
	]
}

We will merge these two collections into a new collection allBooks, without modifying the actual data of the original collections by using the concept of arrays within these versions.

Using these original collections – beginnersbook and favouritebook, we will make two new collections (just for the clarification) by contracting the multiple documents into a single array of documents with object Id as null.

beginnersbookToMerge

db.runCommand(
        {
          aggregate: "beginnersbookToMerge",
          pipeline: [
            {
              $group : {
                _id: null,
                docs: {
                  $push: {
                    _id: "$_id",
                    name: "$name",
                    age: "$age",
                    email: "$email",
                    course: "$course"
                  }
                }
              }
            },
            {
              $out: "beginnersbookToMerge"
            }
          ],
          cursor: { }
        }
);

> db.beginnersbookToMerge.find().pretty()
{
	"_id" : null,
	"docs" : [
		{
			"_id" : ObjectId("61c19331f2ea1f93a0a74841"),
			"name" : "Chaitanya",
			"age" : 30,
			"email" : "admin@beginnersbook.com",
			"course" : [
				{
					"name" : "MongoDB",
					"duration" : 7
				},
				{
					"name" : "Java",
					"duration" : 30
				}
			]
		},
		{
			"_id" : ObjectId("61c19375f2ea1f93a0a74842"),
			"name" : "Vaibhav",
			"age" : 28,
			"email" : "vaibhav@beginnersbook.com",
			"course" : [
				{
					"name" : "MySQL",
					"duration" : 7
				},
				{
					"name" : "Java",
					"duration" : 30
				}
			]
		},
		{
			"_id" : ObjectId("61c193b1f2ea1f93a0a74843"),
			"name" : "Shruthi",
			"age" : 29,
			"email" : "shruthi@beginnersbook.com",
			"course" : [
				{
					"name" : "MySQL",
					"duration" : 7
				},
				{
					"name" : "C++",
					"duration" : 30
				}
			]
		}
	]
}

favouritebookToMerge

db.runCommand(
        {
          aggregate: "favouritebookToMerge",
          pipeline: [
            {
              $group : {
                _id: null,
                docs: {
                  $push: {
                    _id: "$_id",
                    name: "$name",
                    age: "$age",
                    email: "$email",
                    course: "$course"
                  }
                }
              }
            },
            {
              $out: "favouritebookToMerge"
            }
          ],
          cursor: { }
        }
);

> db.favouritebookToMerge.find().pretty()
{
	"_id" : null,
	"docs" : [
		{
			"_id" : ObjectId("61c19468f2ea1f93a0a74844"),
			"name" : "Deepti",
			"age" : 32,
			"email" : "deepti@favouritebook.com",
			"course" : [
				{
					"name" : "MicroServices",
					"duration" : 7
				},
				{
					"name" : "Spring Boot",
					"duration" : 30
				}
			]
		},
		{
			"_id" : ObjectId("61c194d2f2ea1f93a0a74845"),
			"name" : "Pallavi",
			"age" : 27,
			"email" : "pallavi@favouritebook.com",
			"course" : [
				{
					"name" : "Docker",
					"duration" : 7
				},
				{
					"name" : "JavaScript",
					"duration" : 30
				}
			]
		},
		{
			"_id" : ObjectId("61c19517f2ea1f93a0a74846"),
			"name" : "Sameer",
			"age" : 25,
			"email" : "sameer@favouritebook.com",
			"course" : [
				{
					"name" : "Hibernate",
					"duration" : 7
				},
				{
					"name" : "JDBC",
					"duration" : 30
				}
			]
		}
	]
}

And finally, here’s the trick to merge them –

db.runCommand(
         {
          aggregate: "beginnersbookToMerge",
          pipeline:[
            {
              $lookup: {
                    from: "favouritebookToMerge",
                    localField : "_id",
                    foreignField : "_id",
                    as: "favouritebookToMerge"
              }
            },
            {
              $unwind: "$favouritebookToMerge"
            },
            {
              $project: { union: { $concatArrays: [ "$docs",
                                                    "$favouritebookToMerge.docs"] }
              }
            },
            {
              $unwind: '$union'
            },
            {
              $replaceRoot: { newRoot: '$union' }
            },
            {
              $out: "allBooks"
            }
          ],
          cursor: { }
        }
);

The merge will result into a single collection allBooks with all the documents present in the original collections.

> db.allBooks.find().pretty()
{
	"_id" : ObjectId("61c19331f2ea1f93a0a74841"),
	"name" : "Chaitanya",
	"age" : 30,
	"email" : "admin@beginnersbook.com",
	"course" : [
		{
			"name" : "MongoDB",
			"duration" : 7
		},
		{
			"name" : "Java",
			"duration" : 30
		}
	]
}
{
	"_id" : ObjectId("61c19375f2ea1f93a0a74842"),
	"name" : "Vaibhav",
	"age" : 28,
	"email" : "vaibhav@beginnersbook.com",
	"course" : [
		{
			"name" : "MySQL",
			"duration" : 7
		},
		{
			"name" : "Java",
			"duration" : 30
		}
	]
}
{
	"_id" : ObjectId("61c193b1f2ea1f93a0a74843"),
	"name" : "Shruthi",
	"age" : 29,
	"email" : "shruthi@beginnersbook.com",
	"course" : [
		{
			"name" : "MySQL",
			"duration" : 7
		},
		{
			"name" : "C++",
			"duration" : 30
		}
	]
}
{
	"_id" : ObjectId("61c19468f2ea1f93a0a74844"),
	"name" : "Deepti",
	"age" : 32,
	"email" : "deepti@favouritebook.com",
	"course" : [
		{
			"name" : "MicroServices",
			"duration" : 7
		},
		{
			"name" : "Spring Boot",
			"duration" : 30
		}
	]
}
{
	"_id" : ObjectId("61c194d2f2ea1f93a0a74845"),
	"name" : "Pallavi",
	"age" : 27,
	"email" : "pallavi@favouritebook.com",
	"course" : [
		{
			"name" : "Docker",
			"duration" : 7
		},
		{
			"name" : "JavaScript",
			"duration" : 30
		}
	]
}
{
	"_id" : ObjectId("61c19517f2ea1f93a0a74846"),
	"name" : "Sameer",
	"age" : 25,
	"email" : "sameer@favouritebook.com",
	"course" : [
		{
			"name" : "Hibernate",
			"duration" : 7
		},
		{
			"name" : "JDBC",
			"duration" : 30
		}
	]
}

Finally, you can now drop the extra collections made during the merge – beginnersbookToMerge and favouritebookToMerge.

Hope this will help lot of us. Thank you!