Mongo query for lookup array of keys which is itself an item in a nested array-CodePudding

My first collection is as below, I am searching the document with the email and match the particular jobid inside the jobs array. Then insert the document of second collection by matching _id with jobs.Process.profile_id.

{
    "_id": {
      "$oid": "6229d3cfdbfc81a8777e4821"
    },
    "jobs": [
    {
          "job_ID": {
            "$oid": "62289ded8079821eb24760e0"
          },
          "Process": [
            {
              "profile_id": {
                "$oid": "6285e571681188e83d434797"
              }
            },
            {
              "profile_id": {
                "$oid": "6285e571681188e83d434799"
              }
            }
          ],
        },
        {
          "job_ID": {
            "$oid": "6228a252fb4554dd5c48202a"
          },
          "Process": [
            {
              "profile_id": {
                "$oid": "62861067dc9771331e61df5b"
              }
            }
          ],
        },
        {
          "job_ID": {
            "$oid": "622af1c391b290d34701af9f"
          },
          "Process": [
            ""
          ],
        }
      ],
      "email": "********@gmail.com"
    }

and my second collection is, I need to insert this document in my first collection by matching with jobs.Process.profile_id.

{
    "_id": {
      "$oid": "6285e571681188e83d434797"
    },
    "Name": "Lakshdwanan",
    "Location":"California"
}

I have tried with query,

aggregate([
  { $match: { email: email } },
  {
    $lookup: {
      from: 'user__profiles',
      localField: 'jobs.Process.profile_id',
      foreignField: '_id',
      as: 'jobings',
    },
  },
  {
    $addFields: {
      jobings: {
        $map: {
          input: {
            $filter: {
              input: '$jobs',
              as: 'm',
              cond: {
                $eq: ['$$m.job_ID', objInstance],
              },
            },
          },
          as: 'm',
          in: {
            $mergeObjects: [
              {
                $arrayElemAt: [
                  {
                    $filter: {
                      input: '$jobings',
                      cond: {
                        $eq: ['$$this._id', '$$m.Process.profile_id'],
                      },
                    },
                  },
                  0,
                ],
              },
              '$$m',
            ],
          },
        },
      },
    },
  },
  {
    $project: {
      jobings: 1,
      _id: 0,
    },
  },
]);

My output should only display second collection document based on the first collection document matching.

CodePudding user response：

EDIT: If you want the data for a specific job only, it is better to $filter the jobs before the $lookup step. After the $lookup, just $unwind and format:

db.firstCol.aggregate([
  {
    $match: {email: email}
  },
  {
    $project: {
      jobs: {
        $filter: {
          input: "$jobs",
          as: "item",
          cond: {$eq: ["$$item.job_ID", objInstance]}
        }
      },
      _id: 0
    }
  },
  {
    $lookup: {
      from: "user__profiles",
      localField: "jobs.Process.profile_id",
      foreignField: "_id",
      as: "jobings"
    }
  },
  {
    $project: {res: "$jobings", _id: 0}
  },
  {
    $unwind: "$res"
  },
  {
    $replaceRoot: {newRoot: "$res"}
  }
])

Playground

The jobs.Process.profile_id is the user__profiles _id, so no need to merge anything...The results are documents from user__profiles collection "as is" but they can be formatted as wanted..._id key name can be renamed profile_id easily.