MongoDB Aggregate pipeline with $group and $count on a Pointer reference returns wrong data

Question

I have a set of data in MongoDB with parse-server in the following format-

 Rating => objectId, user<_User>, rating...
 _User => objectId, gender<m|f|nb|na>

I have been trying to group the data based on the user's gender to find out how many male, female, non-binary or N/A users have rated. user field in a pointer reference to _User. I am using the following aggregate pipeline.

const pipeline = [
      {
        lookup: {
          from: '_User',
          localField: 'user',
          foreignField: 'objectId',
          as: 'user'
        }
      },
      {
        unwind: { path: '$user' }
      },
      {
        group: {
          objectId: '$user.gender',
          count: {
            $sum: 1
          }
        }
      }
    ]
const data = await new Query('Rating').aggregate(pipeline)

Result =>

[
        {
            "count": 54,
            "objectId": "na"
        },
        {
            "count": 405,
            "objectId": null
        },
        {
            "count": 27,
            "objectId": "f"
        },
        {
            "count": 540,
            "objectId": "m"
        }
    ],

However, returned data count doesn't match with actual data. The actual database has only 27 ratings with 1 f, 2 na, 24 m.

For MongoDB developers, objectId is equavalent to _id.

I am a novice to aggregation framework. What am I doing wrong?

Server Environment- parse-server: 3.2.3 mongodb: 4.0.2

Answer 1

It is tricky because you need to understand how Parse Server stores the data inside the MongoDB. The following query should solve your problem:

  const query = new Parse.Query('Rating');

  const pipeline = [
    {
      project: {
        objectId: 1,
        userId: { $substr: ['$_p_user', '_User$'.length, -1] }
      }
    },
    {
      lookup: {
        from: '_User',
        localField: 'userId',
        foreignField: '_id',
        as: 'user'
      }
    },
    {
      unwind: { path: '$user' }
    },
    {
      group: {
        objectId: '$user.gender',
        count: {
          $sum: 1
        }
      }
    }
  ];

  return await query.aggregate(pipeline, { useMasterKey: true });