MongoDB: lookup: Merge related documents

In this post, I am going to explain how to merge the documents from two related collections.

 

'lookup' method is used to fetch the related documents and merge them together as single document.

 

Syntax

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

 

Let’s experiment with a many-to-many relation project-to-employees.

 

project collection 

db.project.insertMany([
	{
		"_id" : 1,
		"name" : "CPCS",
		"description" : "Cabin Pressure Control System",
		"empIds" : [1, 2, 3]
	},
	{
		"_id": 2,
		"name" : "NLP",
		"description": "Natural Language Processing",
		"empIds" : [3, 4]
	}
])

employee collection

db.employee.insertMany([
	{
		"_id" : 1,
		"name" : "Krishna Gurram",
		"pjtIds" : [1]
	},
	{
		"_id" : 2,
		"name" : "Sailaja PTR",
		"pjtIds" : [1]
	},
	{
		"_id" : 3,
		"name" : "Gopi Battu",
		"pjtIds" : [1, 2]
	},
	{
		"_id" : 4,
		"name" : "Harika Raghu",
		"pjtIds" : [2]
	}
])

Data from the collections

> db.project.find().pretty()
{
	"_id" : 1,
	"name" : "CPCS",
	"description" : "Cabin Pressure Control System",
	"empIds" : [
		1,
		2,
		3
	]
}
{
	"_id" : 2,
	"name" : "NLP",
	"description" : "Natural Language Processing",
	"empIds" : [
		3,
		4
	]
}
> 
> 
> db.employee.find().pretty()
{ "_id" : 1, "name" : "Krishna Gurram", "pjtIds" : [ 1 ] }
{ "_id" : 2, "name" : "Sailaja PTR", "pjtIds" : [ 1 ] }
{ "_id" : 3, "name" : "Gopi Battu", "pjtIds" : [ 1, 2 ] }
{ "_id" : 4, "name" : "Harika Raghu", "pjtIds" : [ 2 ] }

 

Get all the projects along with their employee details

db.project.aggregate([{$lookup : {
	"from" : "employee",
	"localField" : "empIds",
	"foreignField" : "_id",
	"as" : "employee_info"
}}]).pretty()

 

> db.project.aggregate([{$lookup : {
... "from" : "employee",
... "localField" : "empIds",
... "foreignField" : "_id",
... "as" : "employee_info"
... }}]).pretty()
{
	"_id" : 1,
	"name" : "CPCS",
	"description" : "Cabin Pressure Control System",
	"empIds" : [
		1,
		2,
		3
	],
	"employee_info" : [
		{
			"_id" : 1,
			"name" : "Krishna Gurram",
			"pjtIds" : [
				1
			]
		},
		{
			"_id" : 2,
			"name" : "Sailaja PTR",
			"pjtIds" : [
				1
			]
		},
		{
			"_id" : 3,
			"name" : "Gopi Battu",
			"pjtIds" : [
				1,
				2
			]
		}
	]
}
{
	"_id" : 2,
	"name" : "NLP",
	"description" : "Natural Language Processing",
	"empIds" : [
		3,
		4
	],
	"employee_info" : [
		{
			"_id" : 3,
			"name" : "Gopi Battu",
			"pjtIds" : [
				1,
				2
			]
		},
		{
			"_id" : 4,
			"name" : "Harika Raghu",
			"pjtIds" : [
				2
			]
		}
	]
}

 

Get all the employees along with their project details

db.employee.aggregate([{$lookup : {
	"from" : "project",
	"localField" : "pjtIds",
	"foreignField" : "_id",
	"as" : "projects"
}}]).pretty()

 

> db.employee.aggregate([{$lookup : {
... "from" : "project",
... "localField" : "pjtIds",
... "foreignField" : "_id",
... "as" : "projects"
... }}]).pretty()
{
	"_id" : 1,
	"name" : "Krishna Gurram",
	"pjtIds" : [
		1
	],
	"projects" : [
		{
			"_id" : 1,
			"name" : "CPCS",
			"description" : "Cabin Pressure Control System",
			"empIds" : [
				1,
				2,
				3
			]
		}
	]
}
{
	"_id" : 2,
	"name" : "Sailaja PTR",
	"pjtIds" : [
		1
	],
	"projects" : [
		{
			"_id" : 1,
			"name" : "CPCS",
			"description" : "Cabin Pressure Control System",
			"empIds" : [
				1,
				2,
				3
			]
		}
	]
}
{
	"_id" : 3,
	"name" : "Gopi Battu",
	"pjtIds" : [
		1,
		2
	],
	"projects" : [
		{
			"_id" : 1,
			"name" : "CPCS",
			"description" : "Cabin Pressure Control System",
			"empIds" : [
				1,
				2,
				3
			]
		},
		{
			"_id" : 2,
			"name" : "NLP",
			"description" : "Natural Language Processing",
			"empIds" : [
				3,
				4
			]
		}
	]
}
{
	"_id" : 4,
	"name" : "Harika Raghu",
	"pjtIds" : [
		2
	],
	"projects" : [
		{
			"_id" : 2,
			"name" : "NLP",
			"description" : "Natural Language Processing",
			"empIds" : [
				3,
				4
			]
		}
	]
}

 

 

 

 

 

 

 

 

 

 

Previous                                                    Next                                                    Home