Mongodb Lookup with sorting and grouping in C#

Question

I have the following db config:

db={
  "order": [
    {
      "id": 1,
      "version": 1
    },
    {
      "id": 1,
      "version": 2
    },
    {
      "id": 2,
      "version": 1
    },
    {
      "id": 2,
      "version": 2
    }    
  ],
  "orderDetail": [
    {
      "orderId": 1,
      "orderDate": new Date("2020-01-18T16:00:00Z")
    },
    {
      "orderId": 1,
      "orderDate": new Date("2020-01-11T16:00:00Z")
    },
    {
      "orderId": 1,
      "orderDate": new Date("2020-01-12T16:00:00Z")
    }
  ]
}

I'm using the fluent interface to perform a Lookup joining the orderDetails to the order collection (as shown in this post). Now that I have the join in place what's the best method to:

Sort the joined array such that the details are sorted by orderDate
Group the Orders (by OrderID) and sort by version to select the latest (largest Version #)

The workaround I implemented for #1 involves sorting the list after performing the lookup, but that's only because I wasn't able to apply a sort to the "as" of collection as part of the Lookup.

If anyone has any ideas, I'd appreciate it. Thanks!

score 0 · Answer 1 · answered Jan 22 '20 at 01:05

If you are using MongoDB v3.6 or higher, you can use the $lookup with uncorrelated subqueries to use the inner pipelines to archive what you want.

Join Conditions and Uncorrelated Sub-queries

Since you didn't provide what collections or fields you are using, I will give a generic example:

db.customers.aggregate([
   {
      $lookup: {
         from: "orders",
         let: { customer_id: "$_id" },
         pipeline: [
            { $match: { $expr: { $eq: [ "$customer_id",  "$$customer_id" ] } } },
            { $sort: { orderDate: -1 } }
         ],
         as: "orders"
       }
    }
]);

I hope that gives you a way to get where you want. =]

Mongodb Lookup with sorting and grouping in C#

1 Answers1