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
}
]
}
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"
]
}
])
With MongoDB Compass, it allows to export the aggregation pipeline into a specific language. You can work the query in C# as BsonDocument.
[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; }
}
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();