Sorting nested array field with multiple same key

MongoDB Atlas
1

Here is the data collection that i have

[
  {
    "_id": 1,
    "name": "Order 1",
    "involved_teams": [
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: '2023-12-23'}},
      { important_date: { delivery_date: nil}},
    ]
  },
  {
    "_id": 2,
    "name": "Order 2",
    "involved_teams": [
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: '2023-12-22'}},
    ]
  },
  {
    "_id": 3,
    "name": "Order 3",
    "involved_teams": [
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: '2023-12-21'}},
      { important_date: { delivery_date: nil}},
    ]
  },
  {
    "_id": 4,
    "name": "Order 4",
    "involved_teams": [
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: '2023-12-20'}},
      { important_date: { delivery_date: '2023-12-20'}},
    ]
  },
]

How to sort the orders by ‘involved_teams.important_date.delivery_date’ field where a order can have multiple involved_teams and each team can have a delivery_date. During sorting i want to pick the max delivery date for each order.

Result i want

[
  {
    "_id": 4,
    "name": "Order 4",
    "involved_teams": [
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: '2023-12-20'}},
      { important_date: { delivery_date: '2023-12-20'}},
    ]
  },
  {
    "_id": 3,
    "name": "Order 3",
    "involved_teams": [
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: '2023-12-21'}},
      { important_date: { delivery_date: nil}},
    ]
  },
  {
    "_id": 2,
    "name": "Order 2",
    "involved_teams": [
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: '2023-12-22'}},
    ]
  },
  {
    "_id": 1,
    "name": "Order 1",
    "involved_teams": [
      { important_date: { delivery_date: nil}},
      { important_date: { delivery_date: '2023-12-23'}},
      { important_date: { delivery_date: nil}},
    ]
  },
]
2

You can order the items of an array when you $push an item to it. You need to use the $each and $sort modifiers to the $push operation.

2 Likes
3

This is the solution that i used for this problem

 sort_pipeline =
      [
        {"$unwind" => "$involved_teams"},
        {"$group" => {
          "_id" => "$_id",
          "date" => {"$max" => "$#{sort_by}"},
          "original" => { "$first" => "$$ROOT" }
        }},
        {"$sort" => {"date" => sort_direction}},
        {
          "$replaceRoot" => {
            "newRoot" => "$original"
          }
        }
      ]
db.orders.aggregate(sort_pipeline)

Thank you for solutions, If u have better and optimal solutions. Keep suggesting.