Lookup joining through a function

Working with Data
1

Hello,

I have to join two collections based on multiple criteria, let’s suppose something like:

exam:
{_id: 1, patientId: “PatID1”, dateOfExam:“2020-04-01”, description:“Some exam”}
{_id: 2, patientId: “PatID1”, dateOfExam:“2020-04-15”, description:“Some exam”}

check:
{_id: 10, patientId: “PatID1”, dateOfCheck:“2020-03-31”, type: “PreExam”, description:“Some check”}
{_id: 11, patientId: “PatID1”, dateOfCheck:“2020-03-31”, type: “Generic”, description:“Some generic check”}
{_id: 12, patientId: “PatID1”, dateOfCheck:“2020-04-04”, type: “PreExam”, description:“Some check”}
{_id: 13, patientId: “PatID1”, dateOfCheck:“2020-04-12”, type: “PreExam”, description:“Some check”}

Let’s suppose I need to get all exams which have checks of type PreExam in the day before the exam.

So I only want the exam of 2020-04-01 (_id: 1) together with its “PreExam” check of 2020-03-31 (_id: 10).

I’m using a lookup with let/pipeline because I need to “join” on two fields (patientId and type), but I need to compare dates which are actually strings.
Is there some way of dealing with such matching inside the lookup phase? I’ve tried to get “the day before” inside the let, in different ways, but with no success.
My best guess, I thought, was:

let: {joinKey: “$patientId”, dataType: “PreExam”, endDate: “new Date((new Date($dateOfExam).getTime() - 1 (1000606024))).toISOString().substring(0,10)”},
pipeline: [
{$match: {
$expr: {
$and: [
{$eq: ["$type", “$$dataType”]},
{$eq: ["$patientId", “$$joinKey”]},

						{$lt: ["$dateOfCheck", "$$endDate"]}
						
					]
			}
		}}
	],

But it seems that “let” doesn’t allow evaluating expressions…

I solved in another way, but is there a clean solution to manage cases where the join condition needs to be a function?

Thank you very much in advance!

2

In the let you can use aggregation operators.

For example, to convert the string date of dateOfExam to a Date object, you can use the $toDate operator. And, to arrive at the value of endDate you can use aggregation arithmetic operators. You don’t have to convert it back to a string value again to use it in the pipeline. Instead, in the { $lt: [ "$dateOfCheck", "$$endDate" ] } you can convert the dateOfCheck to a Date object - so that you are comparing two date objects.

What are you deriving in this following code: "new Date((new Date($dateOfExam).getTime() - 1 (1000606024)) .toISOString().substring(0,10)"

3

Thank you very much for the explanation and the links!
I’ll try first thing on Monday! And will study in the meantime :sweat_smile: