We are in need of an aggregation pipeline that utilizes various stages like: addFields, lookup, group, and unwind. However, there seems to be a discrepancy when converting the MongoDB Compass syntax into Parse Cloud Code JavaScript calls, as we are not achieving the expected results.
The code exported from MongoDB Compass for "Node" appears 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'
}
}
}
]
There are distinct syntax variations between the raw MongoDB query and what is expected for Parse Server calls. Below is the converted syntax that should function (to the best of my understanding):
var pipeline =
{
addFields :
{
user : '$pUser.objectId', // accessing the user object id differently than previous syntax shown
username: '$userobject.username'
},
lookup : {
from: '_User',
localField: 'user',
foreignField: '_id', // using "_id" instead of "objectId" due to specific field naming in the lookup table
as: 'userobject'
},
unwind : { path: '$username' },
group : {
objectId: '$username',
total : {
$sum : '$score'
}
}
};
var pipelineResults = await gameTableQuery.aggregate(pipeline);
Expected Output
The desired output aims to link the current table's user pointer (pUser) with the User table, then group by username whilst calculating the sum of scores.
Actual Output
In the scenario where searching for the _id during the lookup stage, no entries are returned.
Replacing the lookup stage foreignField with objectId retrieves all users instead of only those matching the local field.
lookup : {
from: '_User',
localField: 'user',
foreignField: 'objectId',
as: 'userobject'
}
It appears there might be a syntax error occurring during the translation between MongoDB syntax and the Parse Server aggregate pipeline syntax.