We are familiar with joins in SQL,Today we look into MongoDb to find
wheather similar functionlity exists in mongoDb.
We start with three collections "user" , "userInfo" & "service".
Lets first drop if those collection pre exists in our database.
db.customer.drop();
db.address.drop();
db.service.drop();
Service Collection:
db.service.insertMany([
{
id:1,
name:"amyzone prime video",
url:"www.example.com/services/prime"
},
{
name:"amyzone music",
url:"www.example.com/services/music"
},
{
name:"amyzone podcast",
url:"www.example.com/services/podcast"
}
])
Customer Collection:
db.customer.insertMany([
{
firstName: "Bobby",
lastName: "Rao",
dob: new Date("1983-09-21"),
country: "India",
email: "bobby.rao@example.com",
gender: "Male",
services:["amyzone prime video","amyzone music","amyzone podcast"],
id: 1,
},
{
firstName: "Jackie",
lastName: "Verma",
dob: new Date("1973-04-01"),
country: "India",
email: "jackie.verma@example.com",
gender: "Male",
services:["amyzone prime video"],
id: 2,
},
{
firstName: "Ruhi",
lastName: "Walkar",
dob: new Date("1987-01-07"),
country: "India",
email: "ruhi.walkar@example.com",
gender: "Female",
services:["amyzone music"],
id: 3,
},
{
firstName: "amy",
lastName: "jay",
dob: new Date("1997-05-17"),
country: "India",
email: "amy.jay@example.com",
gender: "Female",
id: 4,
},
]);
In this collection we have created id which will acts as primary key.
Our next collection is userInfo that will have userId which act as
foreign key.
Address Collection:
db.address.insertMany([
{
addressLine1:
"Bldg No: 786,Floor:8th Floor,Room No:1234",
addressLine2:
"Town:Vilaspur,City:Jamshedpur",
addressType: "billing",
status: "active",
userId: 1,
},
{
addressLine1:
"Bldg No: 007,Floor:15th Floor,Room No:5390",
addressLine2:
"Town:Rajapur,City:Mapuca",
addressType: "billing",
status: "active",
userId: 2,
},
{
addressLine1:
"Bldg No: 845,Floor:3rd Floor,Room No:6724",
addressLine2:
"Town:Vikramgad,City:Jaipur",
addressType: "shipping",
status: "active",
userId: 1,
},
{
addressLine1:
"Bldg No: 234,Floor:10th Floor,Room No:6712",
addressLine2:
"Town:Gounda Halli,City:Maysure",
addressType: "shipping",
status: "active",
userId: 2,
},
{
addressLine1:
"Bldg No: 123,Floor:6th Floor,Room No:7872",
addressLine2:
"Town:Martha Halli,City:Varangal",
addressType: "shipping",
status: "active",
userId: 3,
},
{
addressLine1:
"Bldg No: 123,Floor:6th Floor,Room No:7872",
addressLine2:
"Town:Martha Halli,City:Varangal",
addressType: "billing",
status: "active",
userId: 3,
},
{
addressLine1:
"Bldg No: 5612,Floor:5th Floor,Room No:1267",
addressLine2:
"Town:Laxmi Puri,City:Bhopal",
addressType: "shipping",
status: "inactive",
userId: 1,
},
{
addressLine1:
"Bldg No: 234,Floor:11th Floor,Room No:5672",
addressLine2:
"Town:Gopal Puram,City:Nehru Nagar",
addressType: "billing",
status: "active",
userId: 4,
},
{
addressLine1:
"Bldg No: 234,Floor:11th Floor,Room No:5672",
addressLine2:
"Town:Gopal Puram,City:Nehru Nagar",
addressType: "shipping",
status: "active",
userId: 4,
},
]);
Single Equality Join:
Now we will try to join two collections "customer" & "address" using common id/userId.
db.customer.aggregate([
{
$lookup: {
"from": "address",
"localField": "id",
"foreignField": "userId",
"as": "address"
}
}
]).pretty()
Here "customer" is collection which contain primary key "id",
while "address" contain foreign key "userId".So local field
is field from collection on which we are running aggregate
i.e. "customer".while foreignField is from collection whom
we are going to make a join i.e. "address" and that is "userId"
Output:
[
{
_id: ObjectId('6624d9f92521b29f3cef6359'),
firstName: 'Bobby',
lastName: 'Rao',
dob: ISODate('1983-09-21T00:00:00.000Z'),
country: 'India',
email: 'bobby.rao@example.com',
gender: 'Male',
services: [ 'amyzone prime video', 'amyzone music', 'amyzone podcast' ],
id: 1,
address: [
{
_id: ObjectId('6624da052521b29f3cef635d'),
addressLine1: 'Bldg No: 786,Floor:8th Floor,Room No:1234',
addressLine2: 'Town:Vilaspur,City:Jamshedpur',
addressType: 'billing',
status: 'active',
userId: 1
},
{
_id: ObjectId('6624da052521b29f3cef635f'),
addressLine1: 'Bldg No: 845,Floor:3rd Floor,Room No:6724',
addressLine2: 'Town:Vikramgad,City:Jaipur',
addressType: 'shipping',
status: 'active',
userId: 1
},
{
_id: ObjectId('6624da052521b29f3cef6363'),
addressLine1: 'Bldg No: 5612,Floor:5th Floor,Room No:1267',
addressLine2: 'Town:Laxmi Puri,City:Bhopal',
addressType: 'shipping',
status: 'inactive',
userId: 1
}
]
},
{
_id: ObjectId('6624d9f92521b29f3cef635a'),
firstName: 'Jackie',
lastName: 'Verma',
dob: ISODate('1973-04-01T00:00:00.000Z'),
country: 'India',
email: 'jackie.verma@example.com',
gender: 'Male',
services: [ 'amyzone prime video' ],
id: 2,
address: [
{
_id: ObjectId('6624da052521b29f3cef635e'),
addressLine1: 'Bldg No: 007,Floor:15th Floor,Room No:5390',
addressLine2: 'Town:Rajapur,City:Mapuca',
addressType: 'billing',
status: 'active',
userId: 2
},
{
_id: ObjectId('6624da052521b29f3cef6360'),
addressLine1: 'Bldg No: 234,Floor:10th Floor,Room No:6712',
addressLine2: 'Town:Gounda Halli,City:Maysure',
addressType: 'shipping',
status: 'active',
userId: 2
}
]
},
{
_id: ObjectId('6624d9f92521b29f3cef635b'),
firstName: 'Ruhi',
lastName: 'Walkar',
dob: ISODate('1987-01-07T00:00:00.000Z'),
country: 'India',
email: 'ruhi.walkar@example.com',
gender: 'Female',
services: [ 'amyzone music' ],
id: 3,
address: [
{
_id: ObjectId('6624da052521b29f3cef6361'),
addressLine1: 'Bldg No: 123,Floor:6th Floor,Room No:7872',
addressLine2: 'Town:Martha Halli,City:Varangal',
addressType: 'shipping',
status: 'active',
userId: 3
},
{
_id: ObjectId('6624da052521b29f3cef6362'),
addressLine1: 'Bldg No: 123,Floor:6th Floor,Room No:7872',
addressLine2: 'Town:Martha Halli,City:Varangal',
addressType: 'billing',
status: 'active',
userId: 3
}
]
},
{
_id: ObjectId('6624d9f92521b29f3cef635c'),
firstName: 'amy',
lastName: 'jay',
dob: ISODate('1997-05-17T00:00:00.000Z'),
country: 'India',
email: 'amy.jay@example.com',
gender: 'Female',
id: 4,
address: [
{
_id: ObjectId('6624da052521b29f3cef6364'),
addressLine1: 'Bldg No: 234,Floor:11th Floor,Room No:5672',
addressLine2: 'Town:Gopal Puram,City:Nehru Nagar',
addressType: 'billing',
status: 'active',
userId: 4
},
{
_id: ObjectId('6624da052521b29f3cef6365'),
addressLine1: 'Bldg No: 234,Floor:11th Floor,Room No:5672',
addressLine2: 'Town:Gopal Puram,City:Nehru Nagar',
addressType: 'shipping',
status: 'active',
userId: 4
}
]
}
]
Here both localField & foreignField are string that matches for equality.
Using Lookup on an Array:
We can also lookup where at one end their is array and other end their is an element.
db.customer.aggregate([
{
$lookup: {
"from": "service",
"localField": "services",
"foreignField": "name",
"as": "services"
}
}
]).pretty()
Output:
[
{
_id: ObjectId('6624d9f92521b29f3cef6359'),
firstName: 'Bobby',
lastName: 'Rao',
dob: ISODate('1983-09-21T00:00:00.000Z'),
country: 'India',
email: 'bobby.rao@example.com',
gender: 'Male',
id: 1,
services: [
{
_id: ObjectId('6624dc002521b29f3cef6366'),
id: 1,
name: 'amyzone prime video',
url: 'www.example.com/services/prime'
},
{
_id: ObjectId('6624dc002521b29f3cef6367'),
name: 'amyzone music',
url: 'www.example.com/services/music'
},
{
_id: ObjectId('6624dc002521b29f3cef6368'),
name: 'amyzone podcast',
url: 'www.example.com/services/podcast'
}
]
},
{
_id: ObjectId('6624d9f92521b29f3cef635a'),
firstName: 'Jackie',
lastName: 'Verma',
dob: ISODate('1973-04-01T00:00:00.000Z'),
country: 'India',
email: 'jackie.verma@example.com',
gender: 'Male',
id: 2,
services: [
{
_id: ObjectId('6624dc002521b29f3cef6366'),
id: 1,
name: 'amyzone prime video',
url: 'www.example.com/services/prime'
}
]
},
{
_id: ObjectId('6624d9f92521b29f3cef635b'),
firstName: 'Ruhi',
lastName: 'Walkar',
dob: ISODate('1987-01-07T00:00:00.000Z'),
country: 'India',
email: 'ruhi.walkar@example.com',
gender: 'Female',
id: 3,
services: [
{
_id: ObjectId('6624dc002521b29f3cef6367'),
name: 'amyzone music',
url: 'www.example.com/services/music'
}
]
},
{
_id: ObjectId('6624d9f92521b29f3cef635c'),
firstName: 'amy',
lastName: 'jay',
dob: ISODate('1997-05-17T00:00:00.000Z'),
country: 'India',
email: 'amy.jay@example.com',
gender: 'Female',
id: 4,
services: []
}
]
Now lets interchange roles of service & customer collection
db.service.aggregate([
{
$lookup: {
"from": "customer",
"localField": "name",
"foreignField": "services",
"as": "subscribers"
}
}
]).pretty()
Output:
[
{
_id: ObjectId('6624dc002521b29f3cef6366'),
id: 1,
name: 'amyzone prime video',
url: 'www.example.com/services/prime',
subscribers: [
{
_id: ObjectId('6624d9f92521b29f3cef6359'),
firstName: 'Bobby',
lastName: 'Rao',
dob: ISODate('1983-09-21T00:00:00.000Z'),
country: 'India',
email: 'bobby.rao@example.com',
gender: 'Male',
services: [ 'amyzone prime video', 'amyzone music', 'amyzone podcast' ],
id: 1
},
{
_id: ObjectId('6624d9f92521b29f3cef635a'),
firstName: 'Jackie',
lastName: 'Verma',
dob: ISODate('1973-04-01T00:00:00.000Z'),
country: 'India',
email: 'jackie.verma@example.com',
gender: 'Male',
services: [ 'amyzone prime video' ],
id: 2
}
]
},
{
_id: ObjectId('6624dc002521b29f3cef6367'),
name: 'amyzone music',
url: 'www.example.com/services/music',
subscribers: [
{
_id: ObjectId('6624d9f92521b29f3cef6359'),
firstName: 'Bobby',
lastName: 'Rao',
dob: ISODate('1983-09-21T00:00:00.000Z'),
country: 'India',
email: 'bobby.rao@example.com',
gender: 'Male',
services: [ 'amyzone prime video', 'amyzone music', 'amyzone podcast' ],
id: 1
},
{
_id: ObjectId('6624d9f92521b29f3cef635b'),
firstName: 'Ruhi',
lastName: 'Walkar',
dob: ISODate('1987-01-07T00:00:00.000Z'),
country: 'India',
email: 'ruhi.walkar@example.com',
gender: 'Female',
services: [ 'amyzone music' ],
id: 3
}
]
},
{
_id: ObjectId('6624dc002521b29f3cef6368'),
name: 'amyzone podcast',
url: 'www.example.com/services/podcast',
subscribers: [
{
_id: ObjectId('6624d9f92521b29f3cef6359'),
firstName: 'Bobby',
lastName: 'Rao',
dob: ISODate('1983-09-21T00:00:00.000Z'),
country: 'India',
email: 'bobby.rao@example.com',
gender: 'Male',
services: [ 'amyzone prime video', 'amyzone music', 'amyzone podcast' ],
id: 1
}
]
}
]
I have explored $lookup further in next article at
https://msdotnetbuddy.blogspot.com/2024/04/mongodbjoins-using-lookup-part-2.html
No comments:
Post a Comment