Niclas Schumacher
Niclas Schumacher

Reputation: 232

Get document based on last item in arrays property

I need to retrieve orders, based on the status code on a property on the last element in an array.

My structure is as follows.

{
  "_id" : "3580bdba-4017-40af-939d-7391d70b3511",

  "IsPublic" : true,

  "MailReceiptDispatch" : {
    "TransferSupervisor" : {
      "TransferStatuses" : [{
          "TransferState" : 0,
          "TransferTime" : ISODate("2015-05-29T11:21:20.722Z")
        }, {
          "TransferState" : 1,
          "TransferTime" : ISODate("2015-05-29T11:54:10.013Z")
        }, {
          "TransferState" : 2,
          "TransferTime" : ISODate("2015-05-29T11:54:12.462Z")
        }],
      "IsTransferedLimitReached" : false,
      "LatestTransferingStatus" : {
        "TransferState" : 2,
        "TransferTime" : ISODate("2015-05-29T11:54:12.462Z")
      }
    },
    "ExceptionLog" : [],
    "HasWarningBeenSent" : false
  }
}

So I need to get the objects I need based on something like: {MailReceiptDispatch.TranferSupervisor.TransferStatuses.[Get last in array].TransferState : 2}`

I tried slice, but then I just retrieve the object. mI need to look at the objects property. ElemMatch does something that might be useful, but I can't figure out how to connect both slice and ElemMatch.

Upvotes: 1

Views: 70

Answers (1)

Neo-coder
Neo-coder

Reputation: 7840

Mongo Aggregation used to find out last TransferState, first you unwind TransferStatuses array and used either sort or max in aggregation to get latest TransferState as below :

1> Sort all TransferTime in aggregation :

 db.collectionName.aggregate({"$unwind":"$MailReceiptDispatch.TransferSupervisor.TransferStatuses"},
                             {"$sort":{"MailReceiptDispatch.TransferSupervisor.TransferStatuses.TransferTime":-1}},
                             {"$group":{"_id":"$_id","TransferState":{"$first":"$MailReceiptDispatch.TransferSupervisor.TransferStatuses.TransferState"},
                                         "TransferTime":{"$first":"$MailReceiptDispatch.TransferSupervisor.TransferStatuses.TransferTime"}}}
                             ).pretty()

2> Use max in aggregation as :

db.collectionName.aggregate({"$unwind":"$MailReceiptDispatch.TransferSupervisor.TransferStatuses"},
                            {"$group":{"_id":"$_id","TransferState":{"$max":"$MailReceiptDispatch.TransferSupervisor.TransferStatuses.TransferState"},
                                       "TransferTime":{"$max":"$MailReceiptDispatch.TransferSupervisor.TransferStatuses.TransferTime"}}}
                           ).pretty()

Upvotes: 1

Related Questions