how to get data in mongoose where last element in array?

Question

how to get data in mongoose where last element in array? I have data looks like this:

[
    {
        "_id" : ObjectId("5b56eb3deb869312d85a8e69"),
        "transactionStatus" : [ 
            {
                "status" : "pending",
                "createdAt" : ISODate("2018-07-24T09:02:53.347Z")
            }, 
            {
                "status" : "process",
                "createdAt" : ISODate("2018-07-24T09:02:53.347Z")
            }
        ]
    },
    {
        "_id" : ObjectId("5b56eb3deb869312d8589765"),
        "transactionStatus" : [ 
            {
                "status" : "pending",
                "createdAt" : ISODate("2018-07-24T09:02:53.347Z")
            },
            {
                "status" : "process",
                "createdAt" : ISODate("2018-07-24T09:03:30.347Z")
            },
            {
                "status" : "done",
                "createdAt" : ISODate("2018-07-24T09:04:22.347Z")
            }
        ]
    }
]

And, I want to get data above where last object transactionStatus.status = process, so the result should be:

{
    "_id" : ObjectId("5b56eb3deb869312d85a8e69"),
    "transactionStatus" : [ 
        {
            "status" : "pending",
            "createdAt" : ISODate("2018-07-24T09:02:53.347Z")
        }, 
        {
            "status" : "process",
            "createdAt" : ISODate("2018-07-24T09:02:53.347Z")
        }
    ]
}

how to do that with mongoose?

Possible duplicate of [MongoDB - Query on the last element of an array?](https://stackoverflow.com/questions/28680295/mongodb-query-on-the-last-element-of-an-array) — Ratan Uday Kumar, Jul 26 '18 at 05:50

mickl · Accepted Answer · 2018-07-26T05:43:01.920

1

You can use $expr (MongoDB 3.6+) inside of match. Using $let and $arrayElemAt passing -1 as second argument you can get the last element as a temporary variable and then you can compare the values:

db.col.aggregate([
    {
        $match: {
            $expr: {
                $let: {
                    vars: { last: { $arrayElemAt: [ "$transactionStatus", -1 ] } },
                    in: { $eq: [ "$$last.status", "process" ] } 
                }
            }
        }
    }
])

The same result can be achieved for lower versions of MongoDB using $addFields and $match. You can add $project then to remove that temporary field:

db.col.aggregate([
    {
        $addFields: {
            last: { $arrayElemAt: [ "$transactionStatus", -1 ] }
        }
    },
    {
        $match: { "last.status": "process" }
    },
    {
        $project: { last: 0 }
    }
])

edited Jul 26 '18 at 05:43

answered Jul 26 '18 at 05:24

mickl

48,568
9
60
89

thanks for the answer, $addFields worked very well :) – silvia zulinka Jul 26 '18 at 05:46
this will take lot of time for thousands of record. Using my solution and find() method your performance will be improved – Ratan Uday Kumar Jul 26 '18 at 06:19
You're reordering `transactionStatus` which is far beyond the scope of this question and there's no information that you can do it. I'd assume that the order matters here – mickl Jul 26 '18 at 06:28
i am talking about efficiency when thousands of documents exist it will take lot of time than find() method – Ratan Uday Kumar Jul 26 '18 at 08:59

Ratan Uday Kumar · Answer 2 · 2018-07-26T06:19:03.927

//Always update new status at Position 0 using $position operator

db.update({
    "_id": ObjectId("5b56eb3deb869312d85a8e69")
},
    {
        "$push": {
            "transactionStatus": {
                "$each": [
                    {
                        "status": "process",
                        "createdAt": ISODate("2018-07-24T09:02:53.347Z")
                    }
                ],
                "$position": 0
            }
        }
    }
)

//Your Query for checking first element status is process

db.find(
    {
        "transactionStatus.0.status": "process"
    }
)

refer $position, $each

how to get data in mongoose where last element in array?

2 Answers2