Aggregate pipeline from cloud code not returning anything

Question

Aggregation calls that are made on parse cloud code are not providing the required result. I'm not sure if this is a problem with the syntax we are using or if there are some missing things we need to get pipeline aggregation calls to work correctly.

For the required aggregation call, we are building a pipeline that uses five different stages. Within the five stages we are using the following four functions: addFields, lookup, unwind, and group. This has been tested on the MongoDB compass application, and the result is displayed correctly. When the aggregations are exported, and converted to what we believe is the correct syntax, the query returns no results.

Simple aggregation pipelines that only use one stage are working fine. This has been tested for both group by and addField calls. It seems that as soon as multiple stages are added to the pipeline there is a failure.

The aggregation call that is produced directly from MongoDB Compass export to Node, is as follows

[
  {
    '$addFields': {
      'user': {
        '$substr': [
          '$_p_pUser', 6, -1
        ]
      }
    }
  }, {
    '$lookup': {
      'from': '_User', 
      'localField': 'user', 
      'foreignField': '_id', 
      'as': 'userobject'
    }
  }, {
    '$addFields': {
      'username': '$userobject.username'
    }
  }, {
    '$unwind': {
      'path': '$username'
    }
  }, {
    '$group': {
      '_id': '$username', 
      'total': {
        '$sum': '$score'
      }
    }
  }
]

The above call, when converted to the syntax provided here (https://docs.parseplatform.org/js/guide/#aggregate), is as follows:

var pipeline = {
        addFields : { user: { $substr : ['$_p_pUser', 6, -1]} },
        lookup : {
            from: '_User',
            localField: 'user',
            foreignField: 'objectId',
            as: 'userobject'
        },
        addFields : { username: '$userobject.username' },
        unwind : { path: '$username' },
        group : {
            objectId: '$username',
            total : {
              $sum : '$score'
            }
        }
    };
var pipelineResults = await gameTableQuery.aggregate(pipeline);

This provided no results. It was also tested using the specific field name (pUser) rather than the _p_pUser (required to get the query working in MongoDB Compass).

var pipeline = {
        addFields : { user: { $substr : ['$pUser', 6, -1]} },
        lookup : {
            from: '_User',
            localField: 'user',
            foreignField: 'objectId',
            as: 'userobject'
        },
        addFields : { username: '$userobject.username' },
        unwind : { path: '$username' },
        group : {
            objectId: '$username',
            total : {
              $sum : '$score'
            }
        }
    };

A possible issue could be the duplication of the functions addFields. I also attempted the same call, using one addFields call instead.

var pipeline = {
        addFields : 
        { 
            user: { $substr : ['pUser', 6, -1]},
            username: '$userobject.username'
        },
        lookup : {
            from: '_User',
            localField: 'user',
            foreignField: 'objectId',
            as: 'userobject'
        },
        unwind : { path: '$username' },
        group : {
            objectId: '$username',
            total : {
              $sum : '$score'
            }
        }
    };

These calls were done with cloud code and they do not return the required results that were found in MongoDB Compass. No errors are thrown due to syntax, there are simply no results. Are there any restrictions within the parse aggregation calls that would explain why the call is failing?

Answer 1

It seems the issue was due to the substring call.

In MongoDB compass the pUser field was assigned a value that required trimming to access the objectId. When trying to do the same thing from the parse cloud code aggregate, the trimming wasn't necessary as pUser contains objectId as a child element.

To access the objectId I am now using:

user : '$pUser.objectId'

instead of

user : { $substr : ['pUser', 6, -1]}