Question 116374 in Mongodb Question posted in
The official documentation can be found here.

MongoDB – How to return an aggregation with reference to a refence using NodeJS

I am trying to do a lookup from multiple references

Here is a Mongo Playground

Here is my data

Insp

The Insp document contains an array of references to Users (by user ID)

[
  {
    "_id": {
      "$oid": "6359a12fb9450da3d8d8cdd2"
    },
    "REF_Users": [
      {
        "$oid": "6359a0f1b9450da3d8d8cdc7"
      },
      {
        "$oid": "6359a070f1e84209e0c78fc2"
      }
    ],
    "name": "Once"
  }
]

Users

The Users document contains information about a user and it has a reference to the UserType (by userType ID)

[
  {
    "_id": {
      "$oid": "6359a070f1e84209e0c78fc2"
    },
    "REF_UserType": {
      "$oid": "63596323b679475de490500a"
    },
    "fName": "Billy"
  },
  {
    "_id": {
      "$oid": "6359a0f1b9450da3d8d8cdc7"
    },
    "REF_UserType": {
      "$oid": "63596323b679475de4905007"
    },
    "fName": "Mike"
  }
]

UserType

The UserType document holds type information

[
  {
    "_id": {
      "$oid": "63596323b679475de4905007"
    },
    "value": 100,
    "name": "INS"
  },
  {
    "_id": {
      "$oid": "63596323b679475de490500a"
    },
    "value": 200,
    "name": "CLS"
  }
]

Expected output

I want the userType for each user to be with the respective user

{
  "_id": "6359a12fb9450da3d8d8cdd2",
  "people": [
    {
      "_id": "6359a070f1e84209e0c78fc2",
      "userType": {
        "_id": "63596323b679475de490500a",
        "value": 200,
        "name": "CLS"
      },
      "fName": "Billy"
    },
    {
      "_id": "6359a0f1b9450da3d8d8cdc7",
      "userType": {
        "_id": "63596323b679475de4905007",
        "value": 100,
        "name": "INS"
      },
      "fName": "Mike"
    }
  ]
}

TRY 1

This is my pipeline so far

[
  {
    "$match": {}
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "REF_Users",
      "foreignField": "_id",
      "as": "people"
    }
  },
  {
    "$lookup": {
      "from": "usertypes",
      "localField": "people.REF_UserType",
      "foreignField": "_id",
      "as": "userType"
    }
  },
  {
    "$project": {
      "REF_Users": 0,
      "people.REF_UserType": 0
    }
  }
]

Result of TRY 1

{
  "_id": "6359a12fb9450da3d8d8cdd2",
  "people": [
    {
      "_id": "6359a070f1e84209e0c78fc2",
      "fName": "Billy"
    },
    {
      "_id": "6359a0f1b9450da3d8d8cdc7",
      "fName": "Mike"
    }
  ],
  "userType": [
    {
      "_id": "63596323b679475de4905007",
      "value": 100,
      "name": "INS"
    },
    {
      "_id": "63596323b679475de490500a",
      "value": 200,
      "name": "CLS"
    }
  ]
}

It works in Compass…

enter image description here

It works in the playground

enter image description here

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!

Tags:

2

Answers


  1. 0

    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"
  }
])

    Demo @ Mongo Playground

    Login or Signup to reply.
  2. 0

    I just merged the documents using javascript.

    Demo@mongoplayground

    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
        }
      }
    ]
  }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search