MongoDB .NET Driver - $lookup result to one merged & grouped array

Question

I am new to MongoDB and unable to understand aggregate pipeline. I have a collection named Portfolio and here is a sample document:

{
    "_id" : "fjkhjkdshjkhkdsgfsadhfghgdsa",
    "Name" : "Alex's Portfolio",
    "Stocks" : [
        {
            "StockId" : NumberInt(5454125454796),
            "Quantity" : NumberInt(30),
            "InvestedValue" : "4124"
        },
        {
            "StockId" : NumberInt(5745454554541),
            "Quantity" : NumberInt(6),
            "InvestedValue" : "3048"
        }
    ],
    "MutualFunds" : [
        {
            "MutualFundId" : NumberInt(472546545646564),
            "Quantity" : "12",
            "InvestedValue" : "5233"
        }
    ]
}

And I have 2 master collections named Stocks:

[{
    "_id" : "d5043755-ff4d-47da-bbff-df838a79df3b",
    "StockId" : NumberInt(5454125454796),
    "Name" : "AB Industries Ltd.",
    "LastPrice" : "5.70",
    "LastPriceChange" : "0.27"
},
{
    "_id" : "49224732-f380-4e6f-a933-6fdb5b42107f",
    "StockId" : NumberInt(5745454554541),
    "Name" : "AB Industries 2 Ltd.",
    "LastPrice" : "5.70",
    "LastPriceChange" : "0.27"
}]

& Mutual Funds:

[{
    "_id" : "3d8a4baa-34e6-4e5b-b782-ec4eda213fc9",
    "MfId" : NumberInt(472546545646564),
    "Name" : "AB Industries Bond",
    "LastNAV" : "1036.747",
    "LastNAVChange" : "0.3298",
}]

I have tried using this as a reference, but didn't work in .NET Core 6 with MongoDB Driver.

Now, how can I achieve the below given JSON via aggregate pipeline:

{
  "Name": "Alex's Portfolio",
  "Securities": [
    {
      "StockId": 5454125454796,
      "Name": "AB Industries Ltd.",
      "LastPrice": 5.7,
      "LastPriceChange": 0.27,
      "Quantity": 30,
      "InvestedValue": 4124
    },
    {
      "StockId": 5745454554541,
      "Name": "AB Industries 2 Ltd.",
      "LastPrice": 5.7,
      "LastPriceChange": 0.27,
      "Quantity": 6,
      "InvestedValue": 3048
    }
  ],
  "Mfs": [
    {
      "MfId": 472546545646564,
      "Name": "AB Industries Bond",
      "LastNAV": 1036.747,
      "LastNAVChange": 0.3298,
      "Quantity": 12,
      "InvestedValue": 5233
    }
  ]
}

does the link works(exclude .net) ? – Tobok Sitanggang Mar 10 '23 at 13:52 — Tobok Sitanggang, Mar 10 '23 at 13:52

score 1 · Accepted Answer · answered Mar 10 '23 at 14:03

It seems like a complex, long query.

$lookup - Join "Securities" collection and return Securities array.
$lookup - Join "MutualFunds" collection and return Mfs array.
$set -

3.1. Set the Securities field to perform mapping by merging each object with the object from the Stocks array.

3.2. Set Mfs field to perform mapping by merging each object with the object from the MutualFunds array.
$unset - Remove unneeded fields.

db.Portfolio.aggregate([
  {
    "$lookup": {
      "from": "Securities",
      "localField": "Stocks.StockId",
      "foreignField": "StockId",
      "as": "Securities"
    }
  },
  {
    "$lookup": {
      "from": "MutualFunds",
      "localField": "MutualFunds.MutualFundId",
      "foreignField": "MfId",
      "as": "Mfs"
    }
  },
  {
    $set: {
      "Securities": {
        $map: {
          input: "$Securities",
          as: "s",
          in: {
            $mergeObjects: [
              "$$s",
              {
                $first: {
                  $filter: {
                    input: "$Stocks",
                    cond: {
                      $eq: [
                        "$$s.StockId",
                        "$$this.StockId"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      },
      "Mfs": {
        $map: {
          input: "$Mfs",
          as: "m",
          in: {
            $mergeObjects: [
              "$$m",
              {
                $first: {
                  $filter: {
                    input: "$MutualFunds",
                    cond: {
                      $eq: [
                        "$$m.MutualFundId",
                        "$$this.MfId"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: [
      "Stocks",
      "MutualFunds",
      "Securities._id",
      "Mfs._id",
      "Mfs.MutualFundId"
    ]
  }
])

Demo @ Mongo Playground

With MongoDB Compass, it allows to export the aggregation pipeline into a specific language. You can work the query in C# as BsonDocument.

Construct the output model classes.

[BsonNoId]
[BsonIgnoreExtraElements]
public class PortfolioOutputModel
{
    public string Name { get; set; }
    public List<Security> Securities { get; set; }
    public List<MutualFund> Mfs { get; set; }
}

public class Security
{
    public long StockId { get; set;}
    public string Name { get; set;}
    public decimal LastPrice { get; set;}
    public decimal LastPriceChange { get; set;}
    public int Quantity { get; set;}
    public int InvestedValue { get; set;}
}

public class MutualFund
{
    public long MfId { get; set; }
    public string Name { get; set; }
    public decimal LastNAV { get; set; }
    public decimal LastNAVChange { get; set; }
    public int Quantity { get; set; }
    public int InvestedValue { get; set; }
}

Execute the aggregation pipeline.

MongoClientSettings settings = MongoClientSettings.FromConnectionString(
    "Your Connection string"
);


MongoClient _client = new MongoClient(settings);

IMongoDatabase _db = _client.GetDatabase("Your Database name");
IMongoCollection<BsonDocument> _collection = _db.GetCollection<BsonDocument>("Portfolio");

BsonDocument[] pipeline = new BsonDocument[]
{
    new BsonDocument("$lookup",
    new BsonDocument
        {
            { "from", "Securities" },
            { "localField", "Stocks.StockId" },
            { "foreignField", "StockId" },
            { "as", "Securities" }
        }),
    new BsonDocument("$lookup",
    new BsonDocument
        {
            { "from", "MutualFunds" },
            { "localField", "MutualFunds.MutualFundId" },
            { "foreignField", "MfId" },
            { "as", "Mfs" }
        }),
    new BsonDocument("$set",
    new BsonDocument
        {
            { "Securities",
    new BsonDocument("$map",
    new BsonDocument
                {
                    { "input", "$Securities" },
                    { "as", "s" },
                    { "in",
    new BsonDocument("$mergeObjects",
    new BsonArray
                        {
                            "$$s",
                            new BsonDocument("$first",
                            new BsonDocument("$filter",
                            new BsonDocument
                                    {
                                        { "input", "$Stocks" },
                                        { "cond",
                            new BsonDocument("$eq",
                            new BsonArray
                                            {
                                                "$$s.StockId",
                                                "$$this.StockId"
                                            }) }
                                    }))
                        }) }
                }) },
            { "Mfs",
    new BsonDocument("$map",
    new BsonDocument
                {
                    { "input", "$Mfs" },
                    { "as", "m" },
                    { "in",
    new BsonDocument("$mergeObjects",
    new BsonArray
                        {
                            "$$m",
                            new BsonDocument("$first",
                            new BsonDocument("$filter",
                            new BsonDocument
                                    {
                                        { "input", "$MutualFunds" },
                                        { "cond",
                            new BsonDocument("$eq",
                            new BsonArray
                                            {
                                                "$$m.MutualFundId",
                                                "$$this.MfId"
                                            }) }
                                    }))
                        }) }
                }) }
        }),
    new BsonDocument("$unset",
    new BsonArray
        {
            "Stocks",
            "MutualFunds",
            "Securities._id",
            "Mfs._id",
            "Mfs.MutualFundId"
        })
};

List<PortfolioOutputModel> result = (await _collection.AggregateAsync<PortfolioOutputModel>(pipeline))
                .ToList();

MongoDB .NET Driver - $lookup result to one merged & grouped array

1 Answers1