Mongodb aggregation match ends with using field value

Question

note: I'm using Mongodb 4 and I must use aggregation, because this is a step of a bigger aggregation

Problem

How to find in a collection documents that contains fields that ends with value from another field in same document ?

Let's start with this collection:

db.regextest.insert([
{"first":"Pizza", "second" : "Pizza"},
{"first":"Pizza", "second" : "not pizza"},
{"first":"Pizza", "second" : "not pizza"}
])

and an example query for exact match:

db.regextest.aggregate([
{
    $match :  { $expr: { $eq: [ "$first" ,"$second" ] }   }    }
])

I will get a single document

{
    "_id" : ObjectId("5c49d44329ea754dc48b5ace"),
    "first" : "Pizza",    "second" : "Pizza"
}

And this is good.

But how to do the same, but with endsWith?

I've openend another question for start with here that uses indexOfBytes . But indexOf return only first match, and not last one

Edit: I've found an acceptable answer (with a lot of custom logic, my hope is Mongodb team will solve this), here the solution:

db.regextest.aggregate([
    {
        $addFields : {
            "tmpContains" : { $indexOfBytes: [ "$first", { $ifNull : [ "$second" , 0] }  ] }
        }
    },
    {
        $match: { "tmpContains" : { $gt : -1 } }
    },
    {
        $addFields : {
            "firstLen" : { $strLenBytes: "$first" }
        }
    },
    {
        $addFields : {
            "secondLen" : { $strLenBytes: "$second" }
        }
    },
    {
        $addFields : {
            "diffLen" : { $abs: { $subtract : [ "$firstLen", "$secondLen"] } }
        }
    },
    {
        $addFields : {
           "res" : { $substr:  [ "$first", "$diffLen", "$firstLen"] }
        }
    },
    {
        $match : { $expr : { $eq: [ "$res" , "$second" ] }}
    }
])

But indexOf return only first match, and not last one How? It will return the documents contain that stiring — Ashh

mickl mickl · Accepted Answer · 2019-01-29T21:28:54

As you know the length of both fields ($strLenBytes) you can use $substr to get last n characters of second field and the compare it to first field, try:

db.regextest.aggregate([
    {
        $match: {
            $expr: {
                $eq: [ 
                    "$first", 
                    {
                        $let: {
                            vars: { firstLen: { $strLenBytes: "$first" }, secondLen: { $strLenBytes: "$second" } },
                            in: { $substr: [ "$second",  { $subtract: [ "$$secondLen", "$$firstLen" ] }, "$$firstLen" ] }
                        }
                    }
                ]
            }
        }
    }
])

Above aggregation will give you the same result as string comparison is case-sensitive in MongoDB. To fix that you can apply $toLower operator both on $first and on calculated substring of $second, try:

db.regextest.aggregate([
    {
        $match: {
            $expr: {
                $eq: [ 
                    { $toLower: "$first" }, 
                    {
                        $let: {
                            vars: { firstLen: { $strLenBytes: "$first" }, secondLen: { $strLenBytes: "$second" } },
                            in: { $toLower: { $substr: [ "$second",  { $subtract: [ "$$secondLen", "$$firstLen" ] }, "$$firstLen" ] } }
                        }
                    }
                ]
            }
        }
    }
])

Mongodb aggregation match ends with using field value

1 Answers