Update array element from $lookup

Working with Data
1

I have recently started working with MongoDB and I have a task which is giving me a bit of a headache.
I have a boxes collection with many attributes but this is the one I need right now:

{
	"relatedJobs": [
		{
			"completedAt": "2022-09-02",
			"jobId": "muDYtPWUkFC4555anxHu",
			"weight": "2009",
			"jobType": "RECEPTION_JOB",
			"partnerId": "17"
		}
	]
}

And I have a jobs collection again with many more attributes than these:

{
    "firebaseId": "muDYtPWUkFC4555anxHu",
    "completedAt": "2022-09-02",
    "createdAt": "2022-09-02",
    "dueAt": "2022-09-02",
    "jobType": "RECEPTION_JOB",
    "partnerId": "17",
    "relatedBoxes": [...],
    "status": "DONE"
  }

These data were migrated over from a Firestore DB, that’s why the id is called firebaseId. What I’d like to achieve is to update the boxes.relatedJobs so their jobId contains the _id attribute of the corresponding job document and then drop the firebaseId altogether. $unsetting the firebaseId is not an issue, but how do I update that one attribute in my array?
Thanks

3

Hi @K_Cs,

Welcome to the MongoDB Community forums :sparkles:

I assume, your boxes collection is looking like this:

{     
         "_id": ObjectId('63da520f6cd602b05233cc4b'),
	     "relatedJobs": [
		{
			"completedAt": "2022-09-02",
			"jobId": "muDYtPWUkFC4555anxHu",
			"weight": "2009",
			"jobType": "RECEPTION_JOB",
			"partnerId": "17"
		}
	]
}

I noticed that the _id field is missing from the sample provided. Could you kindly provide additional examples or specify the field you would like to use for the replacement? Additionally, it would be helpful if you could provide the expected output based on the sample documents.

If I may suggest, utilizing a driver and a language that you are comfortable with may make the process simpler unless it is mandatory to use MongoDB’s MQL. Could you please let me know if MongoDB’s MQL is a requirement in this scenario?

Also, for clarity, is the connection between the job document in the boxes collection and the corresponding job in the jobs collection determined by the match between the relatedJobs.jobId field and the jobs collection’s firebasedId field?

Lastly, I observed that the example in the boxes collection only includes one job, but I assume that the relatedJobs field may contain multiple entries. Please correct me if my assumption is incorrect.

Best,
Kushagra

4

Hi @Kushagra_Kesav,

Here is an exported box document which has multipe relatedJobs:

[{
  "_id": {
    "$oid": "63da3c1feac7af351445caad"
  },
  < many more fields >
  "relatedJobs": [
    {
      "partnerId": "41",
      "completedAt": "2022-09-13",
      "jobType": "RECEPTION_JOB",
      "weight": 1972,
      "jobId": "tCDcpp4K1JFfsXju6LYQ"
    },
    {
      "jobId": "TDfrqods85bQYECOa3n3",
      "partnerId": "13",
      "jobType": "DELIVERY_JOB",
      "completedAt": "2023-01-18",
      "weight": 23060
    }
  ]
}]

And this is the 2 jobs related to it:

[{
  "_id": {
    "$oid": "63da2b01eac7af351445c946"
  },
  "firebaseId": "TDfrqods85bQYECOa3n3",
  "completedAt": "2023-01-18",
  "createdAt": "2023-01-18",
  "dueAt": "2023-01-18",
  "partnerId": "13",
  "startedAt": "2023-01-18",
  "status": "DONE",
  "jobType": "DELIVERY_JOB",
  < many more fields >
},{
  "_id": {
    "$oid": "63da2b01eac7af351445ca57"
  },
  "firebaseId": "tCDcpp4K1JFfsXju6LYQ",
  "completedAt": "2022-09-13",
  "createdAt": "2022-09-13",
  "dueAt": "2022-09-13",
  "partnerId": "41",
  "startedAt": "2022-09-13",
  "status": "DONE",
  "jobType": "RECEPTION_JOB",
  < many more fields >
}]

Currently the jobId in a relatedJobs array element contains the firebaseId from the job and that’s my only connection to it. I want to replace that obsolete reference with the job’s _id which is the ObjectId while preserving everything else as it was. The updated box document should look similar to this:

[{
  "_id": {
    "$oid": "63da3c1feac7af351445caad"
  },
  < many more fields >
  "relatedJobs": [
    {
      "partnerId": "41",
      "completedAt": "2022-09-13",
      "jobType": "RECEPTION_JOB",
      "weight": 1972,
      "jobId": {
		  "$oid": "63da2b01eac7af351445ca57"
		  }
    },
    {
      "jobId": {
		  "$oid": "63da2b01eac7af351445c946"
		  },
      "partnerId": "13",
      "jobType": "DELIVERY_JOB",
      "completedAt": "2023-01-18",
      "weight": 23060
    }
  ]
}]

My current approach is to write JavaScript for it where I already converted some strings to date:

const db = db.getSiblingDB("database_name");
const boxes = db.boxes;
const jobs = db.jobs;
boxes.updateMany( {}, [
	{ $set: {
		fillDate: { $dateFromString: { dateString: "$fillDate", format: "%Y-%m-%d" } },
		expirationDate: { $dateFromString: { dateString: "$expirationDate", format: "%Y-%m-%d" } }
	} }
] );

Please let me know if there is any more info I should provide.
Thanks

5

I solved it by returning everything from the lookup and doing the changes in JS:

var boxesWithJobs = boxes.aggregate( [
	{
		$lookup: {
			from: "jobs",
			localField: "relatedJobs.jobId",
			foreignField: "firebaseId",
			as: "relatedJobsWithIds"
		}
	}
] ).toArray();

boxesWithJobs.forEach(boxWithJobs => {
	boxWithJobs.relatedJobs.forEach(relatedJob => {
		var job = boxWithJobs.relatedJobsWithIds.find(rj => rj.firebaseId === relatedJob.jobId);
		relatedJob.jobId = job._id;
	});
	delete boxWithJobs.relatedJobsWithIds;
});

boxes.drop();
boxes.insertMany(boxesWithJobs);

Might not be the best or most beautiful solution but it gets the job done (pun intended).

7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.