0

I'm trying to make a mongodb query byId that sorts the items in the 'history' array by the "updateDate" field and also shows only the first 2 items in the array.

{
    "_id" : ObjectId("63b5c0f016c75b6c2e36575f"),
    "history" : [
        {
            "adress" : [ "KKK" ],
            "updateDate" : ISODate("2023-01-04T15:09:52.121-03:00")
        },
        {
            "adress" : [ "YYY" ],
            "updateDate" : ISODate("2023-01-04T15:10:03.303-03:00")
        },
        {
            "adress" : [ "ZZZ" ],
            "updateDate" : ISODate("2023-01-04T15:12:08.160-03:00")
        }
    ]
}

I tried to do it like this, but it didn't work

db.collection.find(
{_id: ObjectId("63b5c0f016c75b6c2e36575f")}, 
{"history":{$slice: -2}}
)
.sort({"history.updateDate": -1})
Jerrold
  • 1

1 Answers1

0

If you are running MongoDB 5.2, you can use the sortArray aggregation: https://www.mongodb.com/docs/manual/reference/operator/aggregation/sortArray/

db.collection.aggregate( [
   { $match: { _id: ObjectId('63b5c0f016c75b6c2e36575f') } },
   { $project:
      {
          _id: 0,
          result: { $sortArray: { input: "$history", sortBy: { updateDate: 1 } } }
      }
   },
   { $slice: [ $history, -2 ] },
] )

Or with earlier versions of MongoDB, you can unwind then sort then group by ID:

db.collection.aggregate([ 
    { "$match" : { _id: ObjectId('63b5c0f016c75b6c2e36575f') } },
    { "$unwind" : "$history"} , 
    { "$sort" : { "history.updateDate" : 1}}, 
    { "$group" : { "history" : { "$push" : { "updateDate" : "$history.updateDate", "address": "$history.address"}} , "_id" : null}}
    { "$slice" : ["history", -2]}, 
])
rook218
  • 644
  • 7
  • 20
  • MongoDB 4.0 here... – Jerrold Jan 04 '23 at 19:33
  • You may need to unwind the records, sort them in the pipeline, then group the items back together by ID, like in this example: https://stackoverflow.com/a/17988911/10430070 – rook218 Jan 04 '23 at 19:36
  • @Jerrold if this answer helped you, please upvote it or mark it as the answer. It gives us both reputation, which tells the community that we are engaged with the solution-finding process and makes it easier to find answers the next time we need them – rook218 Jan 06 '23 at 13:48