regex with variable pattern taken from value of a field [MongoDB]

Question

Let's say I have some documents of this shape:

[
  {
    name: "Name1",
    surname: "Surname1",
    fullName: "Name1 Surnmame1"
  },
  {
    name: "Name2",
    surname: "Surname2",
    fullName: "Name2 Surnmame2"
  },
  // I would like to detect this one where name and name inside fullName do not match
  {
    name: "Name3",
    surname: "Surname3",
    fullName: "Name1 Surnmame3"
  }
]

and fullName is a computed field.

Ideally, I would like to detect the documents for which name is not part of fullName. This could happen due to some wrong implementation of the computation part.

I would expect something like the following would at least identify which name/fullname do really match (the opposite of what I am trying to do):

db.people.find({"fullName": /"$name"/});

However, this searches for $name and not for the value that field name holds in the same document. Any idea how to accomplish this?

Note: I try to run this against DocumentDB (v3.6.0) but if you have any suggestion for MongoDB it could work as well.

Answer 1

You can use $indexOfCP to check if the name is in fullName. If the substring is not present, $indexOfCP will return -1.

According to the official AWS documentDB document, $indexOfCP should be supported in your v3.6 DB.

db.collection.aggregate([
  {
    $addFields: {
      idx: {
        "$indexOfCP": [
          "$fullName",
          "$name"
        ]
      }
    }
  },
  {
    "$match": {
      idx: -1
    }
  }
])

Here is the Mongo playgroundfor your reference.