Mongodb using redact with $eq and $arrayElemAt for the inner document array

Question

My documents are structured like this:

{
    "_id" : ObjectId("57c93af8bf501124df658a0e"),
    "friends" : [
        {   "userid" : 14, "xxxx" : "xxx"   },
        {   "userid" : 13,  "xxx" : "xxx"   }
    ]
},{
    "_id" : ObjectId("57c93af8bf501124df658a0e"),
    "friends" : [
        {   "userid" : 1, "xxxx" : "xxx"    },
        {   "userid" : 14,  "xxx" : "xxx"   }
    ]
}

I need to retrieve the document which has the userid as 14 in the last element of the friends array. The desired output is as below:

{
    "_id" : ObjectId("57c93af8bf501124df658a0e"),
    "friends" : [
        {   "userid" : 14,  "xxx" : "xxx"   }
    ]
}

I tried to use the $slice operator to get last element of array in mongodb as recommended; but I need to do this on the sub-documents.

How can I structure the query for "friends.user" $eq to 14 inside the $redact using the "$arrayElemAt" at -1?

Answer 1

Run the following aggregation pipeline to get the desired result:

db.collection.aggregate([
    { "$match": { "friends.userid": 14 } },
    { 
       "$redact": {
            "$cond": {
                "if": { "$eq": [{ "$arrayElemAt": [ "$friends.userid", -1 ] }, 14 ] },
                "then": "$$KEEP",
                "else": "$$PRUNE"
            }
        }
    } 
])