I have a large collection I'm searching through on mongodb. (Supports PCRE v8.42)

Let's say I'm trying to find where "comment" contains "alpha bravo" where the field value of "name" doesn't immediately follow

In other words, match $regex: "alpha bravo (?!"$name)"

Example:

Doc1
"name": "john"
"comment": "alpha; alpha lima alpha bravo golf"

#this should match


Doc2
"name": "fonz"
"comment": "foxtrot alpha bravo fonz; zulu"

#this shouldn't match

Doc3
"name": "zoltan"
"comment": "golf alpha bravo zoltan; alpha bravo delta; lambda alpha"

#this should match due to alpha bravo delta

Typically I'd try to accomplish this with regex but i can't get this to work with a mix of static and field values.

I have tried the following and it hasn't worked.

"$regex": "alpha bravo (?!"$name")"

I've tried aggregate with match followed by expr, which will work if I'm calling the field value alone in the regex field, but not in combination with the static values, at least with the functions I've tried.

db.collection.aggregate({
  "$match": {
    "$expr": {
      "$regexMatch": {
        "input": "$comment",
        "regex": "alpha bravo (?!"$name")"
      }
    }
  }
})

I'm receiving errors that the regex expression is either invalid or missing closing brackets or quotes.

Escaping the double quotations around the field haven't helped either.

Thanks in advance for any help!

CodePudding user response：

Extending your "$regexMatch" idea by using "$concat" to build the "regex" seems to work.

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$regexMatch": {
          "input": "$comment",
          "regex": {"$concat": ["alpha bravo (?!", "$name", ")"]}
        }
      }
    }
  }
])

Try it on mongoplayground.net.

CodePudding user response：

Interesting question. Looks like @rickhg12hs beat me by 2 minutes, but I'll share this anyway mostly for the two additional notes I've added at the bottom. Feel free to accept theirs though!

Looks like you are about 90% of the way there. The missing piece is to wrap the $regex value with a $concat operator, eg:

  $expr: {
    $regexMatch: {
      input: "$comment",
      regex: {
        $concat: [
          "alpha bravo (?!",
          "$name",
          ")"
        ]
      }
    }
  }

Here is the Mongo Playground demonstration.

Two quick notes:

1. There is no way for an index to efficiently be used for this predicate.
2. You can use $expr inside of find() (which is what I used in the demo) in case you didn't need to use aggregation for anything else