Hi everyone. I am the sole developer at my work and my knowledge on most dev topics is as good as the google searches will return. I am stuck on performing more complex data operations in the application that I am converting from sql. I want to create some sub collections(?) based on properties that are in the main collection.
- PayTypes - sourced from a single property in an Array.
- CustomerNames - combined from two properties (CustomerName and CustomerAddlName).
- CompanyNames - single property.
- CarrierNames - sourced from one property.
The main collection is a couple thousand documents like this:
{
"id": "601c2696fedceb27201dda46",
"vin": "3GYFNEEY8AS1111",
"mailDetails": [
{
"adminDetails": [
{
"receivedDate": "2020-10-19T00:00:00Z",
"enteredBy": "scottu",
"completedDate": "2020-12-08",
"updatedDate": "2020-12-08",
"modifiedBy": "michaelw",
"filed": "H",
"incomingCarrier": "UPS",
"incomingTrackingNumber": "1Z088WA8019711111"
}
],
"companyInfo": [
{
"companyName": "LOVEL FORD",
"companyAddress": "966 E EISENHOWER",
"companyCity": "FORT COLLINS",
"companyState": "CO",
"companyZip": "80537"
}
],
"customerInfo": [
{
"customerName": "HARRISON, FORD",
"addlCustomerName": "HARRISON, MICHELLE",
"customerAddress": "991 ANTELOPE BLVD",
"customerCity": "ALBIN",
"customerState": "WY",
"customerZip": "82053"
}
],
"comments": [
{
"commentDate": "2020-10-19",
"notes": null
}
],
"paymentDetails": [
{
"paymentDate": "2020-10-19T00:00:00Z",
"payType": "Sales Tax",
"checkNumber": "11036290",
"checkAmount": 507.8
},
{
"paymentDate": "2020-10-19T00:00:00Z",
"payType": "Title Fee",
"checkNumber": "11036289",
"checkAmount": 27.2
}
],
"signatureReqList": [
{
"letterDate": "2020-11-07",
"shippedDate": "2020-11-09",
"outgoingTracking": null,
"outgoingCarrier": "USPS",
"letterPath": null
}
],
"vinInspectList": [
{
"letterDate": "2020-11-07",
"shippedDate": "2020-11-09",
"outgoingTracking": null,
"outgoingCarrier": "USPS",
"letterPath": null
}
],
"titleRequestList": null,
"correctLetterList": null,
"rtsList": null,
"lienReleaseList": null,
"mpvChecklistList": null,
"moneyReqList": [
{
"amountRequested": 18,
"letterDate": "2020-11-07",
"shippedDate": "2020-11-09",
"outgoingTracking": null,
"outgoingCarrier": "USPS",
"letterPath": null
}
]
}
]
}
In my C#, I have the MongoCollection ok and I am getting a list of PayTypes like this:
private readonly IMongoCollection<MailItem> _mailcollection;
private readonly List<string> _paymentTypeCollection;
IMongoCollection<MailItem> _letters = database.GetCollection<MailItem>(settings.MailCollection);
_mailcollection = _letters;
var filter = new BsonDocument();
_paymentTypeCollection = _mailcollection.Distinct<string>("ItemDetails.Payments.PayType", filter).ToList();
To get the other collections, I tried some code from SO using Aggregate and tried using AggregateToCollection(), but I was not able to convert their code/ documentation to my application and I think its because AggregateToCollection returns null, which seems counterintuitive to the method name…
Tried pipelines with Aggregate, but I get a invalid key error for whatever value I try in the sort and just using $match doesnt return anything so I am at a loss there.
PipelineDefinition<MailItem, PayTypes> pipeline = new BsonDocument[]
{
new BsonDocument { { "$match", new BsonDocument("ItemDetails.Payments.PayType", "{$ne: null}") } },
new BsonDocument { { "$sort", new BsonDocument("ItemDetails.Payments.PayType", "PayType") } }
};
var cur = _mailcollection.Aggregate(pipeline);
_paymentTypeCollection = cur.ToList();
I tried linq syntax, which suggests I can use ‘Any’ for it to traverse the arrays but I couldn’t get the function to not give me the red line of death. Tried this one, but since the properties are nested arrays, It’s not getting the 1 and 2 if there are any, but still returns 0.
var _paytypesGroup = _mailcollection.AsQueryable()
.Where(mc => mc.MailDetails[0].PaymentDetails[0] != null)
.GroupBy(s => new { s.MailDetails[0].PaymentDetails[0].PayType })
.Select(n => new PayTypes
{
PayType = n.Key.PayType,
}).ToList();
I am at a loss and at the end of google searching/ code hacking. What is the best way to go about this?
Thanks in advance,