MongoDB Aggregation Array to Object Id with Three Collections (Many-to-One-to-One) using Lookup

Views: 354
Comments: 0
Like/Unlike: 0
Posted On: 30-Aug-2021 05:40 

Share:   fb twitter linkedin
Rahul M...
Moderator
38 Points
19 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:

  1. 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 }
    ])
  2. items
    db.items.insert( [
       { "_id" : 1, "name" : "almonds", "vendorId" : 1, "price" : 10 },
       { "_id" : 2, "name" : "pecans", "vendorId" : 2, "price" : 30 }
    ])
  3. 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:

  1. $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,
            }]
    }
  2. $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,
            }
    }
  3. $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,
            }
    }
  4. $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,
            }
    }
  5. $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,
            }]
    }
  6. $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,
            }
    }
    }
  7. $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

 

0 Comments
  
 Log In to Chat