Change stream pipeline on nested fields

Mathieu_GOBERT · 2024-03-08T23:56:04.500Z

Hi,

I have a schema liket this in a ‘portal’ collection

{
  enterprise: {
    name: 'Company',
    street: 'foo',
    iban: '123456789',
    bic: '123456789'
  },
  salt: 'mskfjlzjrjzjreofskfns',
  hashPassword: 'pjsdfknsfjiejgfqkùgjlr',
}

To detect change on iban or bic field. I use this pipeline

    const pipeline = [
      {
        $match: {
          operationType: 'update',
          $or: [        
            { 'updateDescription.updatedFields.enterprise.bic': { $exists: true } },
            { 'updateDescription.updatedFields.enterprise.iban': { $exists: true } },
          ],
        },
      },
    ];

But it’s don’t work

I have test different strategy, but with no success:

    const pipeline = [
      {
        $match: {
          operationType: 'update',
          $or: [        
            { "updateDescription.updatedFields.enterprise.iban": { $exists: true } },
            { "updateDescription.updatedFields.'enterprise.iban'": { $exists: true } },
            { "updateDescription.updatedFields['enterprise.iban']": { $exists: true } },
            { "updateDescription.updatedFields.['enterprise.iban']": { $exists: true } },
          ],
        },
      },
    ];

If i test the same thing on a field with no dot notation it’s work

    const pipeline = [
      {
        $match: {
          operationType: 'update',
          $or: [        
            { 'updateDescription.updatedFields.salt': { $exists: true } },
            { 'updateDescription.updatedFields.hashPassword': { $exists: true } },
          ],
        },
      },
    ];

How to write or working pipeline on fields with dot notation ?

Thank in advance for your help

steevej · 2024-03-09T13:59:40.492Z

Read the following thread. At first sight it looks like it is the same issue.

'match' not operating as expected within watch(pipeline) functionality on nested fields Working with Data

Hi, I’m trying to set up a listener to a Mongo DB collection using the inbuilt change streams functionality. I’m coming into issues when I set up my watch on the collection when filtering for nested fields. I’m using PyMongo and my Mongo DB is hosted on Atlas. Here is my code… import pymongo client = pymongo.MongoClient(connection_uri) pipeline = [ { '$match': { '$or': [ {'updateDescription.updatedFields.base_name': {'$exists': True}}, …

Mathieu_GOBERT · 2024-03-09T15:40:37.034Z

Yes it’s the same issue.

The result is : It’s not possible. It’s not a bug, it’s a limitation.

I was told by a MongoDB staff member that it was a feature limitation, rather than a bug! I don’t have the level of paid service with Mongo for specific queries to be answered, but a sales rep chased it up for me and mentioned it wasn’t a bug.
I suspect still that it’s a bug but that was the information I was told.

Akshat_Gupta3 · 2024-03-11T18:20:49.345Z

I have not worked with change streams a lot but as for your problem i think it can be solved by using $getField operator which mongo recently launched just to cater use cases like this where the field names contains special characters like dot (.) and dollar ($).

mongodb.com

$getField (aggregation) — MongoDB Manual

Mathieu_GOBERT · 2024-03-12T20:43:14.512Z

Really thanks for your response it’s work

here the code :

    const bankFields = ['bank', 'iban', 'rib', 'bic'];
    const pipeline = [
      {
        $match: {
          operationType: 'update',
          $expr: {
            $or: bankFields.map((field: string) => ({
              $ne: [
                {
                  $type: {
                    $getField: {
                      field: `enterprise.${field}`,
                      input: '$updateDescription.updatedFields',
                    },
                  },
                },
                'missing',
              ],
            })),
          },
        },
      },
    ];

Mathieu_GOBERT · 2024-03-12T21:13:05.757Z

Too bad $getField doesn’t support the $ operator for arrays.

I have a new problem: I’m trying to detect a push in an array.

Here’s a basic sample schema:

{
  userIds: [
    '123',
    '456'
  ]
}

during push ‘789’ in userIds I receive
updateDescription.updatedFields: { 'userIds.3': '789'}

during remove ‘789’ in userIds I receive
updateDescription.updatedFields.userIds: ['123', '456']

Thise pipeline don’t work:

   const pipeline = [
      {
        $match: {
          operationType: 'update',
          $expr: {
            $ne: [
              {
                $type: {
                  $getField: {
                    field: 'userIds.$',
                    input: '$updateDescription.updatedFields',
                  },
                },
              },
              'missing',
            ],
          },
        },
      },
   }

if this can help other users, actually I use this pipeline

I don’t filter event in the $match, I filter received event in the changeStream watch function
I use a $project, to delete the case of remove an element of the array

const pipeline = [
      {
        $match: {
          operationType: 'update',
        },
      },
      {
        $project: {
          // with this projection, we can delete the case of removing an id of userIds
          'updateDescription.updatedFields.userIds': 0,
        },
      },
    ];

fernando_robles · 2024-07-31T03:31:50.950Z

Hi, guys,

After reading this thread I want to share my weird case to see if anyone can help, Im having an issue about monitoring changes in a collection.
I have a nested property that I want to monitor, and I used this approach:

for (String property : Fields.PROPERTIES_TO_MONITOR.keySet()) {
            orFilters.add(Document.parse(
                    "{'updateDescription.updatedFields." + property + "': {$exists: true}}")
                    .toBsonDocument()
            );
        }

        List<Bson> pipeline = List.of(
                Aggregates.match(Filters.and(
                        Filters.in("operationType", Arrays.asList("update", "replace", "insert"))
                        Filters.or(new ArrayList<>(orFilters))
                ))
        );

where the keyset returns something like this: “phone_number”, “mobile_phone_number”, “settings.communication.notes”, this works for the first two, but not for any nested property.

so, after reading this thread I opted for the getField approach:

var fieldsToMonitor = Fields.PROPERTIES_TO_MONITOR.keySet();
        List<Bson> pipeline = List.of(
                Aggregates.match(
                        Filters.and(
                                Filters.in("operationType", Arrays.asList("update", "replace", "insert")),
                                Filters.expr(
                                        new Document("$or", fieldsToMonitor.stream()
                                                .map(field -> new Document("$ne", Arrays.asList(
                                                        new Document("$type",
                                                                new Document("$getField",
                                                                        new Document("field", field).append("input", "$updateDescription.updatedFields")
                                                                )
                                                        ),
                                                        "missing"
                                                ))).toList()
                                        )
                                )
                        )
                )
        );

this works great for “phone_number” or “settings.communication.notes”, the weird part is that is NOT working for both at the same time, if I update “phone_number” I can get the updated_fields, with “phone_number” in it, and when I update “settings.communication.notes” I also get the updated_fields with the notes in it, but if I update the two properties, I just get the “phone_number” (and any other NON nested property).

I hope someone can help me with this.
thanks in advance