My $or selector in a database trigger match expression doesn't work at the second level of nesting when configuring a database trigger

Question

Update: I use "$match expression" to describe this but I don't actually use the $match operator. According to the docs, the selector should conform with $match's syntax, though the $match keyword is apparently not necessary in the actual expression.

Update 2: In the actual collection, outerField represents message, fieldA represents fansNo, and fieldB represents sharedNo. So outerField.fieldA represents message.fansNo and outerField.fieldB represents message.sharedNo. This is a stringified representation of the updateDescription field when the trigger fires (i.e. when I only specify updateDescription.updatedField in the match expression):

"updateDescription: {\"removedFields\":[],\"updatedFields\":{\"someOtherField\":310,\"message.fansNo\":1,\"updatedAt\":\"2020-06-22T13:29:08.829Z\"}}"

================================================================

Original post:

So I can't understand why it fails to trigger when I specify message.fansNo and message.sharedNo in the match expression.

I am setting up a database trigger on updates to a collection, but I'm not able to get my $match expression to work in filtering the change events that cause the trigger to fire. I want to fire the trigger only if one or both of 2 nested fields are present, say fieldA and fieldB. These 2 fields are nested inside an object, and the object is the value of a field in each document. Something like this:

// CollectionA schema
{
  _id: ...,
  outerField: {
    fieldA: 1 // or any number
    fieldB: 2 // or any number
  },
  ...
}

I have tried using this $match expression below, but the trigger doesn't fire:

{
  "$or": [
    {
      "updateDescription.updatedFields.outerField.fieldA": {"$exists":true}
    },
    {
      "updateDescription.updatedFields.outerField.fieldB":{"$exists":true}
    }
  ]
}

If I remove outerField.<field>, it works. That is:

{
  "$or": [
    {
      "updateDescription.updatedFields": {"$exists":true}
    },
    {
      "updateDescription.updatedFields":{"$exists":true}
    }
  ]
}

But of course that's not useful to me because the trigger will fire on any update at all.

I would provide a demo but I'm not sure how to create a sample that has database triggers configured. Any help will be appreciated, thanks!

Answer 1

So I was able to get around this problem by changing the query to watch for a field that gets updated at the same time but isn't nested. I think the problem with checking for a nested field is that the ChangeEvent's updateDescription property doesn't contain the actual nested object that has changed; instead it contains the dot-notation representation of the change. So if you look at Update 2 in my post you'll see that updatedFields has this value: {\"someOtherField\":310,\"message.fansNo\":1... instead of {\"someOtherField\":310,\"message\":{\"fansNo\":1.... By using message.fansNo in the $match query, Mongo will look for this object shape: {\"message\":{\"fansNo\":1..., which doesn't match in this case. A "real" solution here could be to escape the . in message.fansNo in my match expression, but I couldn't get that to work (see this thread).

So the "solution" that worked for me is really just a workaround that works for my specific use-case: it so happens that someOtherField is always updated along with message.fansNo, and someOtherField isn't nested. So I can match someOtherField without worrying about nesting. Basically this match expression gives me the results I want:

{
  "$or": [
    {
      "updateDescription.updatedFields.someOtherField": {"$exists":true}
    },
    {
      "updateDescription.updatedFields.someOtherField":{"$exists":true}
    }
  ]
}

Hope this helps someone else!

Answer 2

{
  "$expr": {
    "$ne": [{
      "$size": {
        "$filter": {
          "input": { "$objectToArray": "$updateDescription.updatedFields" },
          "cond": { "$regexMatch": { "input": "$$this.k", "regex": "message\\.fansNo" } }
        }
      }
    }, 0]
  }
}

It's important to note that MongoDB interprets fields containing dots as nested objects. As a result, you cannot directly search for a field with dots using regular query syntax. To overcome this limitation and search for fields with dots, the provided query uses the $objectToArray operator to convert the updatedFields object into an array of key-value pairs. By doing so, the individual fields can be accessed and processed. The subsequent operations, such as $filter and $regexMatch, are then applied to the array elements, enabling the search for a field containing dots.

This approach allows the query to overcome MongoDB's default interpretation of dots as nested objects and effectively match the desired field, message.fansNo, even though it contains dots.