Hi,
I need help sone help here.
I have three collections, named employees, customfield.values, customfields.
I want to join customfield.values to employees and customfields to customfield.values.
I manage to do the first join with a lookup aggregation but the second one is the problem.
I cannot lookup customfieldvalues.customfield in the customfield collection.
Can someone help?
//employees
[
{
"_id": "61da0dc4f30580abadcb018c",
"employeenumber": "hitest1"
}]
//customfield.values
[
{
"_id": "61daf0c600f0af793e477e7b",
"customfield": "61daf1ebb124e2e3ce754eda",
"employee": "61da0dc4f30580abadcb018c",
"value": "first cf value"
},
{
"_id": "61daf0df9e0eaa2056dc7475",
"customfield": "61dafdf905b91bce14bff88e",
"employee": "61da0dc4f30580abadcb018c",
"value": "second cf value"
}]
//customfields
[
{
"_id": "61daf1ebb124e2e3ce754eda",
"customfieldname": "first cf"
},
{
"_id": "61dafdf905b91bce14bff88e",
"customfieldname": "second cf"
}]
I want to join all three collections together. Starting with employees, joining customfield.values, and then joining customfields to it. If I had to do two aggregations , it would look something like this:
aggregate(
[{"$lookup":
{"from":"customfield.values",
"localField":"_id",
"foreignField":"employee",
"as":"customfieldvalues"}},
{"$unwind":"$customfieldvalues"}]),
aggregate(
[{"$lookup":
{"from":"customfield",
"localField":"customfieldvalues.customfield",
"foreignField":"_id",
"as":"customfieldvalues.customfield"}}])
and thats the result i desire
[
{
"_id": "61da0dc4f30580abadcb018c",
"employeenumber": "hitest1",
"customfieldvalues": [
{
"_id": "61daf0c600f0af793e477e7b",
"customfield": {
"_id": "61daf1ebb124e2e3ce754eda",
"customfieldname": "first cf"
},
"employee": "61daf0c500f0af793e477e74",
"value": "first cf value",
},
{
"_id": "61daf0df9e0eaa2056dc7475",
"customfield": {
"_id": "61dafdf905b91bce14bff88e",
"customfieldname": "second cf"
},
"employee": "61daf0c500f0af793e477e74",
"value": "second cf value",
}]
Thank you.