I am trying to do a lookup from multiple references
Here is a 
It works in the playground
When I put the code into NodeJS and run it from my server:
TRY 2
const agg_project_try = {
people: {
$map: {
input: '$people',
as: 'people',
in: {
$mergeObjects: [
'$$people',
{
userType: {
$first: {
$filter: {
input: '$userType',
cond: {
$eq: ['$$people.REF_UserType', '$$this._id'],
},
},
},
},
},
],
},
},
},
};
I get this error
Arguments must be aggregate pipeline operators
TRY 3
I exported from Compass as NODE
[
{
'$lookup': {
'from': 'users',
'localField': 'REF_Users',
'foreignField': '_id',
'as': 'people'
}
}, {
'$lookup': {
'from': 'usertypes',
'localField': 'people.REF_UserType',
'foreignField': '_id',
'as': 'userType'
}
}, {
'$project': {
'people': {
'$map': {
'input': '$people',
'as': 'people',
'in': {
'$mergeObjects': [
'$$people', {
'userType': {
'$first': {
'$filter': {
'input': '$userType',
'cond': {
'$eq': [
'$$people.REF_UserType', '$$this._id'
]
}
}
}
}
}
]
}
}
}
}
}, {
'$unset': 'people.REF_UserType'
}
]
Then tried the 'project' portion in my server
const agg_project_try = {
'people': {
'$map': {
'input': '$people',
'as': 'people',
'in': {
'$mergeObjects': [
'$$people', {
'userType': {
'$first': {
'$filter': {
'input': '$userType',
'cond': {
'$eq': [
'$$people.REF_UserType', '$$this._id'
]
}
}
}
}
}
]
}
}
}
};
I get this error
Arguments must be aggregate pipeline operators
Here is my node JS pipeline ( that causes the error )
[
{ "$match": {} },
{ "$lookup": { "from": "users", "localField": "REF_Users", "foreignField": "_id", "as": "people" } },
{ "$lookup": { "from": "usertypes", "localField": "people.REF_UserType", "foreignField": "_id", "as": "userType" } },
{
"people": {
"$map": {
"input": "$people",
"as": "people",
"in": {
"$mergeObjects": [
"$$people",
{
"userType": {
"$first": {
"$filter": { "input": "$userType", "cond": { "$eq": ["$$people.REF_UserType", "$$this._id"] } }
}
}
}
]
}
}
}
},
{ "$project": { "REF_Users": 0 } }
]
ANSWER
Up too late last night working on this stuff, actually need the "project" statement to do a projection - doh !
$project:{
'people': {
$map: {
input: '$peopleLookup',
as: 'tempPeople',
in: {
$mergeObjects: [
'$$tempPeople',
{
'userType': {
$first: {
$filter: {
input: '$userTypeLookup',
cond: {
$eq: ['$$tempPeople.REF_UserType', '$$this._id'],
},
},
},
},
},
],
},
},
},
}
Thank you!
CodePudding user response：
In $project stage, you need to iterate each document from the people array`.
Merge ($merge) the current iterated document with the first ($first) filtered ($filter) result from the userType array.
db.workoutDetailSchema.aggregate([
{
"$match": {}
},
{
"$lookup": {
"from": "users",
"localField": "REF_Users",
"foreignField": "_id",
"as": "people"
}
},
{
"$lookup": {
"from": "usertypes",
"localField": "people.REF_UserType",
"foreignField": "_id",
"as": "userType"
}
},
{
"$project": {
"people": {
$map: {
input: "$people",
as: "people",
in: {
$mergeObjects: [
"$$people",
{
userType: {
$first: {
$filter: {
input: "$userType",
cond: {
$eq: [
"$$people.REF_UserType",
"$$this._id"
]
}
}
}
}
}
]
}
}
}
}
},
{
$unset: "people.REF_UserType"
}
])
CodePudding user response：
I just merged the documents using javascript.
db.workoutDetailSchema.aggregate([
{
"$match": {}
},
{
"$lookup": {
"from": "users",
"localField": "REF_Users",
"foreignField": "_id",
"as": "peoples"
}
},
{
"$lookup": {
"from": "usertypes",
"localField": "peoples.REF_UserType",
"foreignField": "_id",
"as": "userType"
}
},
{
$addFields: {
people: {
$function: {
body: "function (people, userType) {people.forEach(function (item, index) {if(JSON.stringify(userType[index]._id) === JSON.stringify(item.REF_UserType)){people[index].userType=userType[index];}});return people;}",
args: [
"$peoples",
"$userType"
],
lang: "js"
},
}
}
},
{
"$project": {
"REF_Users": 0,
"peoples": 0,
"userType": 0,
"people.REF_UserType": 0,
}
}
])
Output
[
{
"_id": ObjectId("6359a12fb9450da3d8d8cdd2"),
"name": "Once",
"people": [
{
"_id": ObjectId("6359a0f1b9450da3d8d8cdc7"),
"fName": "Mike",
"userType": {
"_id": ObjectId("63596323b679475de4905007"),
"name": "INS",
"value": 100
}
},
{
"_id": ObjectId("6359a070f1e84209e0c78fc2"),
"fName": "Billy",
"userType": {
"_id": ObjectId("63596323b679475de490500a"),
"name": "CLS",
"value": 200
}
}
]
}
]