MongoDB $substr from index to index

Question

The data is like "abc abc abc (xyz) efg efg".

I am trying to extract the xyz from the string.

Trying to do this in the $project section:

 $project:   {
        bracketextract : { $substr: [ "$title",{ $indexOfCP: [ "$title", "(" ]}, { $indexOfCP: [ "$title", ")" ]} - { $indexOfCP: [ "$title", "(" ]} ]}, 
    }

Answer 1

You should use $subtract to the length (you're using - currently) and there should be 1 $add-ed to the starting point

db.col.aggregate([
    {
        $project:   {
            bracketextract : { $substr: [ "$title", { $add: [ { $indexOfCP: [ "$title", "(" ]}, 1 ] } , { $subtract: [ { $indexOfCP: [ "$title", ")" ]}, { $add: [ { $indexOfCP: [ "$title", "(" ]}, 1 ] } ] } ]}, 
        }
    }
])

More readable way using $let:

db.col.aggregate([
    {
        $project: {
            bracketextract: { 
                $let: {
                    vars: { 
                        start: { $add: [ { $indexOfCP: [ "$title", "(" ] }, 1 ] }, 
                        end: { $indexOfCP: [ "$title", ")" ] }
                    },
                    in: { $substr: [ "$title", "$$start", { $subtract: [ "$$end", "$$start" ] } ] }
                }
            }
        }
    }
])