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

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

Demo @ Mongo Playground

CodePudding user response：

I just merged the documents using javascript.

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