MongoDB Aggregation Array to Object Id with Three Collections (Many-to-One-to-One) using Lookup
Rahul M...
4916
Points
27
Posts
In this article, we will solve complex aggregation with three collections. These three collections are having relationship as many-to-one-to-one. We will see uses of different pipelines like - $lookup, $unwind, $group, $replaceRoot and many more using MongoDB.Driver.
Example
Suppose we have following three collections:
- orders
db.orders.insert( [
{ "_id" : 1, "items" : [{ "itemId" : 1, "itemCount" : 1 }, { "itemId" : 2, "itemCount" : 1 }], "totalPrice" : 40 },
{ "_id" : 2, "items" : [{ "itemId" : 2 , "itemCount" : 1 }], "totalPrice" : 30 }
]) - items
db.items.insert( [
{ "_id" : 1, "name" : "almonds", "vendorId" : 1, "price" : 10 },
{ "_id" : 2, "name" : "pecans", "vendorId" : 2, "price" : 30 }
]) - vendors
db.vendors.insert( [
{ "_id" : 1, "name" : "abc" },
{ "_id" : 2, "name" : "xyz" }
])
The goal is to write an aggregation that return our orders with the associated items with vedors details as follows:
[{
"_id": 1,
"items": [
{
"_id": 1,
"name": "almonds",
"vendor": {
"_id": 1,
"name": "abc"
},
"price": 10
"itemCount": 1,
},
{
"_id": 2,
"name": "almonds",
"vendor": {
"_id": 2,
"name": "xyz"
},
"price": 30
"itemCount": 1,
}
],
"totalPrice": 40
}]
Here is entire pipeline Shell command for above example output:
db.getCollection('orders').aggregate([
{
$lookup: {
from: 'items',
localField: 'items.itemId',
foreignField: '_id',
as: 'items'
}
},
{ $unwind: '$items' },
{
$lookup: {
from: 'vendors',
localField: 'items.vendorId',
foreignField: '_id',
as: 'items.vendor'
}
},
{ $unwind: '$items.vendor' },
{
$group: {
_id: '$_id',
root: { $mergeObjects: '$$ROOT' },
items: { $push: '$items' }
}
},
{
$replaceRoot: {
newRoot: {
$mergeObjects: ['$root', '$$ROOT']
}
}
},
{
$project: {
root: 0
}
}
]);
And following are the MongoDB.driver c# code:
var group = new BsonDocument
{
{ "_id", "$_id" },
{ "root", new BsonDocument{ { "$mergeObjects", "$$ROOT" } } },
{ "items", new BsonDocument{ { "$push", "$items" } } }
};
var orders = mongoDBService.GetCollection("orders").Aggregate()
.Lookup("items", "items.itemId", "_id", @as: "items")
.Unwind("items", new AggregateUnwindOptions<ItemDetail>() { PreserveNullAndEmptyArrays = true })
.Lookup("vendors", "items.vendorId", "_id", @as: "items.vendor")
.Unwind("items.vendor", new AggregateUnwindOptions<VendorDetail>() { PreserveNullAndEmptyArrays = true })
.Group(group)
.ReplaceRoot<object>("{$mergeObjects:['$root', '$$ROOT']}")
.Project("{root:0}")
.As<OrderDetail>().ToEnumerable();Where
- Order=> entity model class for collection 'orders'
- Item=> entity model class for collection 'items'
- Vendor=> entity model class for collection 'vendors'
- OrderDetail=>
public class OrderDetail: Order
{
public List<ItemDetail> Items { get; set; }
} - ItemDetail=>
public class ItemDetail : Item
{
public Vendor Vendor { get; set; }
} - Packages=>
using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Bson.Serialization.Conventions;
using MongoDB.Driver;
using System.Collections.Generic;
using System.Linq;
Explanation for different pipeline stages:
As we can see the aggregation that accomplishes this goal having seven stages. Let’s understand each stage one by one:
- $lookup with items
It's aggregation with items. It will generate list of items as:
{
"_id": 1,
"items": [
{
"_id": 1,
"name": "almonds",
"vendorId": 1,
"price": 10
"itemCount": 1,
}]
} - $unwind with items
As we have one-to-one relationship with vendor we need to add this stage will result as:
{
"_id": 1,
"items":{
"_id": 1,
"name": "almonds",
"vendorId": 1,
"price": 10
"itemCount": 1,
}
} - $lookup with vendors
It will get vedor detail as:
{
"_id": 1,
"items":
{
"_id": 1,
"name": "almonds",
"vendor": [{
"_id": 1,
"name": "abc"
}],
"price": 10
"itemCount": 1,
}
} - $unwind with vendors
It will make one-to-one relation between items and vendors as:
{
"_id": 1,
"items":
{
"_id": 1,
"name": "almonds",
"vendor": {
"_id": 1,
"name": "abc"
},
"price": 10
"itemCount": 1,
}
} - $group with orders._id and create separate root say 'root'
Now, it's time to group items with respect to orders._id. And it will create separate root say 'root' as:
{
"root" : {
"_id": 1,
"items":
{
"_id": 1,
"name": "almonds",
"vendor": {
"_id": 1,
"name": "abc"
},
"price": 10
"itemCount": 1,
}
},
"items" : [{
"_id": 1,
"name": "almonds",
"vendor": {
"_id": 1,
"name": "abc"
},
"price": 10
"itemCount": 1,
}]
} - $replaceRoot with '$$ROOT' and merge items object
The next step in our pipeline is to replace our root document with the root object merged with the actual root document. This will override the items object in root with our newly grouped together items array as:
{
"_id": 1,
"items" : [{
"_id": 1,
"name": "almonds",
"vendor": {
"_id": 1,
"name": "abc"
},
"price": 10
"itemCount": 1,
}],
"root" : {
"_id": 1,
"items":
{
"_id": 1,
"name": "almonds",
"vendor": {
"_id": 1,
"name": "abc"
},
"price": 10
"itemCount": 1,
}
}
} - $project with newRoot
An unfortunate side effect of above merger is that our resulting document still has a root object filled with superfluous data. As a final piece of housecleaning, let’s remove that field with this step and final output will be as:
{
"_id": 1,
"items" : [{
"_id": 1,
"name": "almonds",
"vendor": {
"_id": 1,
"name": "abc"
},
"price": 10
"itemCount": 1,
}],
...
}
Conclusion
In this article, we saw uses of different pipeline stages to achieve the result. If any one has better approach to achieve above output result or any suggestion for optimization, please let me know.
References
- https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#use--lookup-with-an-array
- https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/
1 Comments
great...
edx
25-Sep-2021 at 04:46