Getting 2nd to the last data in the document - Mongodb

Question

Im trying to get the second to the last data in a document using mongodb query. I am a little confuse how will I mix $group and $sort in this kind of case. Below is the data that I am working with

{
    '_id' : ObjectId('60d9681765077a71ae158625'),
    'room': '301',
    'startaccomodation':ISODate('2021-08-05T06:11:36.007Z'),
    'endaccomodation':ISODate('2021-08-10T06:11:36.007Z'),
     "foodorder" : [ 
                {
                    "_id" : ObjectId("6113a84a1f5f405630c0bd3f"),
                    "order" : "Food"
                }, 
                {
                    "_id" : ObjectId("6113a84a1f5f405630c0bd3e"),
                   "order" : "Dessert"
                }, 
                {
                    "_id" : ObjectId("6113a87c1f5f405630c0bd56"),
                    "order" : "Softdrinks"
                }, 
                {
                    "_id" : ObjectId("6113a8b09a74a76baed7d39d"),
                    "order" : "Chocolate"
                }
            ]
},

{
    '_id' : ObjectId('60dbf391e2759909d52d1917'),
    'room': '302',
    'startaccomodation':ISODate('2021-08-07T06:11:36.007Z'),
    'endaccomodation':ISODate('2021-08-09T06:11:36.007Z'),
   "foodorder" : [ 
                {
                    "_id" : ObjectId("60e44ff08e6a8f4052ac38e3"),
                    "order":"Water"
                }, 
                {
                    "_id" : ObjectId("60e44ff08e6a8f4052ac38ef"),
                    "order":"Pasta"
                }, 
                {
                    "_id" : ObjectId("60e4a63052a1e8554303e335"),
                    "order":"Cake"
                }
            ]
},



{
    '_id' : ObjectId('60dbf61d54b7c46bfa1b7954'),
    'room': '303',
    'startaccomodation':ISODate('2021-08-02T06:11:36.007Z'),
    'endaccomodation':ISODate('2021-08-05T06:11:36.007Z'),
    "foodorder" : [ 
                {
                    "_id" : ObjectId("60e44ff4e3a11a09559d5b49"),
                    "order":"Candy"
                }, 
                {
               

               "_id" : ObjectId("60e44ff4e3a11a09559d5b55"),
                "order":"Chocolate"
            },
            {
                "_id" : ObjectId("60dd0d7b1410931155f0bdd0"),
                "order":"Food"
            },
            {
                "_id" : ObjectId("60dd0e04c02ff023ab091cd3"),
                "order":"Drinks"
            },
            {
                "_id" : ObjectId("60e44ff4e3a11a09559d5b4a"),
                "order":"Sandwich"
            }
        ]

},

What I want to achieve is getting the last food order and also the second to the last food order. Below is the expected output

{
    "room":"301",
    "lastorder":"Chocolate",
    "secondToLastorder":"Softdrinks"

},
{
    "room":"302",
    "lastorder":"Cake",
    "secondToLastorder":"Pasta" 

},
{
    "room":"303",
    "lastorder":"Sandwich",
    "secondToLastorder":"Drinks"

},

Thanks in advance!

Answer 1

db.collection.aggregate([
  {
    $project: {
      room: 1,
      lastorder: {
        $last: { $slice: [ "$foodorder.order", -1 ] }
      },
      secondToLastorder: {
        $first: { $slice: [ "$foodorder.order", -2 ] }
      }
    }
  }
])

mongoplayground