MongoDB, how to sort the dictionaries with key in side a array while keep the structure unchanged

Question

I am using an aggregation pipline and input from the last stage looks like this:

{'_id': ObjectId('62d58138718001537e33f59f'),
 'agentInstr': {'TextTT': '123',
                'InstrumentAlias': 'abc',
                'AccountId': '123'},
 'Transactions': [{'Position': -1.0, 'TransactionDateTime': datetime.datetime(2021, 12, 9, 17, 59, 15, 734000)}, 
                  {'Position': 0.0, 'TransactionDateTime': datetime.datetime(2021, 12, 9, 17, 57, 40, 961000)},
                  {'Position': 0.0, 'TransactionDateTime': datetime.datetime(2021, 12, 9, 17, 59, 27, 963000)}]}

I would like to add a new stage in the pipeline to sort the Transactions with the TransactionDateTime, but I don't know how to refers to the key, is there any possible way to sort them without modifying the structure of the document?

I tried {'$sort' : {'TransactionDateTime' : 1}} and {'$sort' : {'Transactions.TransactionDateTime' : 1}}, and they both don't work.

Answer 1

As mentioned in the comments, since MongoDB version 5.2 there is a "$sortArray" operator that could be used here. Even before that version there are lots of ways to do this and here's one way to sort "Transactions" in a single stage.

N.B.: There are more efficient sorting algorithms that could be implemented with, say, "$function" (see example at the bottom), but if the size of "Transactions" is not too large, perhaps the stage below is sufficient.

db.collection.aggregate([
  {
    "$set": {
      "Transactions": {
        "$reduce": {
          "input": "$Transactions",
          "initialValue": [],
          "in": {
            "$concatArrays": [
              {
                "$filter": {
                  "input": "$$value",
                  "as": "val",
                  "cond": {
                    "$lte": ["$$val.TransactionDateTime", "$$this.TransactionDateTime"]
                  }
                }
              },
              ["$$this"],
              {
                "$filter": {
                  "input": "$$value",
                  "as": "val",
                  "cond": {
                    "$gt": ["$$val.TransactionDateTime", "$$this.TransactionDateTime"]
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Try it on mongoplayground.net.

"$function" was introduced in MongoDB version 4.4 and can also be used (more efficiently?) to sort the "Transactions" array.

db.collection.aggregate([
  {
    "$set": {
      "Transactions": {
        "$function": {
          "body": "function(trans) {return trans.sort((a,b) => a.TransactionDateTime > b.TransactionDateTime)}",
          "args": [
            "$Transactions"
          ],
          "lang": "js"
        }
      }
    }
  }
])

Try it on mongoplayground.net.