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

Question

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.

this is a playground link with your data and query. What is the expected output in this case? — nimrod serok
– nimrod serok, Commented May 21, 2022 at 10:05
I need to get the user_profile documents that matches with the jobs.Process.profile_ids as output after matching with email and jobs.job_id from the first collection . — Rambo
– Rambo, Commented May 21, 2022 at 11:29

nimrod serok · Accepted Answer · 2022-05-21 15:03:54Z

0

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.

edited May 21, 2022 at 15:03

answered May 21, 2022 at 13:20

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rambo Over a year ago

Thanks for the response sir, but here, I need to match with particular jobs.job_id and then insert user_profiles documents in to the profile_ids. of that particular matched job_id. In the query, you are returning all the profile_ids of all jobs object in the main document.

nimrod serok Over a year ago

Updated accordingly

Rambo Over a year ago

If its possible to get result from my query, Can u please update sir, as I want to know where I went wrong.

nimrod serok Over a year ago

What do you mean "my query"?

Rambo Over a year ago

The query that I tried and it is posted in the question.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related