mongodb - Using $slice operator to get last element of array

Question

How to get the last element of an array based on a condition in mongodb?

I am unable to use slice. Here is my input:

{ "1" : { "relevancy" : [  "Y" ] } }
{ "1" : { "relevancy" : [  "Y",  "Y" ] } }
{ "1" : { "relevancy" : [  "N" ] } }
{ "1" : { "relevancy" : [  "Y",  "Y" ] } }
{ "1" : { "relevancy" : [  "Y",  "N" ] } }
{ "1" : { "relevancy" : [  "N" ] } }
{ "1" : { "relevancy" : [  "Y",  "N" ] } }

I want to count the number of rows having "Y" as last element of the "relevancy" array.

With the input records above, it should be 3.

Answer 1

As you would be aware by now, $slice is only used in projection to limit the array elements returned in the results. So you would be stuck with processing the list programmatically with results from a find().

A better approach is to use aggregate. But first let's consider how $slice is used:

> db.collection.find({},{ relevancy: {$slice: -1} })
{ "_id" : ObjectId("530824b95f44eac1068b45c0"), "relevancy" : [  "Y" ] }
{ "_id" : ObjectId("530824b95f44eac1068b45c2"), "relevancy" : [  "Y" ] }
{ "_id" : ObjectId("530824b95f44eac1068b45c3"), "relevancy" : [  "N" ] }
{ "_id" : ObjectId("530824b95f44eac1068b45c4"), "relevancy" : [  "Y" ] }
{ "_id" : ObjectId("530824b95f44eac1068b45c6"), "relevancy" : [  "N" ] }
{ "_id" : ObjectId("530824b95f44eac1068b45c7"), "relevancy" : [  "N" ] }
{ "_id" : ObjectId("530824b95f44eac1068b45c8"), "relevancy" : [  "N" ] }

So you get the last array element, but you are stuck with looping the results as you can't match the last element value. You might as well have just done this in code.

Now let's look at aggregate :

db.collection.aggregate([
    // Match things so we get rid of the documents that will never match, but it will
    // still keep some of course since they are arrays, that *may* contain "N"
    { "$match": { "relevancy": "Y" } },

    // De-normalizes the array
    { "$unwind": "$relevancy" },

    // The order of the array is retained, so just look for the $last by _id
    { "$group": { "_id": "$_id", "relevancy": { "$last": "$relevancy" } }},

    // Match only the records with the results you want
    { "$match": { "relevancy": "Y" }},

    // Oh, and maintain the original _id order [ funny thing about $last ]
    { "$sort": { "_id": 1 } }
])

Even if this would be your first usage of aggregate(), I encourage you to learn it. It is perhaps your most useful problem solving tool. Certainly has been for me. Put each step in once at a time if you are learning.

Also not sure on your document form, all the 1: { ... } sub-document notation appears to be a mistake but you should clear that up or adjust the code above to reference "1.relevancy" instead. I hope your documents actually look more like this though:

{ "relevancy" : [  "Y" ] , "_id" : ObjectId("530824b95f44eac1068b45c0") }
{ "relevancy" : [  "Y",  "Y" ] , "_id" : ObjectId("530824b95f44eac1068b45c2") }
{ "relevancy" : [  "N" ], "_id" : ObjectId("530824b95f44eac1068b45c3") }
{ "relevancy" : [  "Y",  "Y" ], "_id" : ObjectId("530824b95f44eac1068b45c4") }
{ "relevancy" : [  "Y",  "N" ], "_id" : ObjectId("530824b95f44eac1068b45c6") }
{ "relevancy" : [  "N" ], "_id" : ObjectId("530824b95f44eac1068b45c7") }
{ "relevancy" : [  "Y",  "N" ], "_id" : ObjectId("530824b95f44eac1068b45c8") }

---

MongoDB 3.2.x and upwards

Of course MongoDB 3.2 introduces an "aggregation" operator for $slice and an even better $arrayElemAt operator that removes the need for any $unwind and $group processing. After the initial $match query you just make a "logical match" with $redact:

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

That is going to do the inspection on the last element of the array when deciding whether to $$KEEP or $$PRUNE the documents from the returned results.

If you still wanted the "projection" then you can actually add the $slice:

db.collection.aggregate([
    { "$match": { "relevancy": "Y" } },
    { "$redact": {
        "$cond": {
            "if": { "$eq": [{ "$arrayElemAt": [ "$relevancy", -1 ], "Y" ] },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }},
    { "$project": { "relevancy": { "$slice": [ "$relevancy", -1 ] } } }
])

Or the alternate approach of:

db.collection.aggregate([
    { "$match": { "relevancy": "Y" } },
    { "$project": { "relevancy": { "$slice": [ "$relevancy", -1 ] } } },
    { "$match": { "relevancy": "Y" } }
])

But is probably less costly to do the $redact first and "then" do any re-shaping in `$project.