Put Lookup result from array inside the same array

[Fryderyk_Chopin]

Hi, I have a SOURCE COLLECTION with an array of documents where each document from the array has an ObjectId which references a FOREIGN COLLECTION. From each one of these ObjectsId’s I have to look for information (fields ‘name’ and ‘code’) in another collection and add it to the initial array. Next I show an example of the collections.

SOURCE_COLLECTION

{'_id': 1, 
'array': [
{'_id_array': 1, 'type': ObjectId('1'), 'array_other_fields': some_data}
{'_id_array': 2, 'type': ObjectId('2'), 'array_other_fields': some_data}
{'_id_array': 3, 'type': ObjectId('1'), 'array_other_fields': some_data}
]}

{'_id': 2, 
'array': [
{'_id_array': 1, 'type': ObjectId('1'), 'array_other_fields': some_data}
{'_id_array': 2, 'type': ObjectId('2'), 'array_other_fields': some_data}
{'_id_array': 3, 'type': ObjectId('3'), 'array_other_fields': some_data}
]}

FOREIGN_COLLECTION

[{
'_id': ObjectId('1'), 
'name': object_id_1_name
'code': object_id_1_code
},
{
'_id': ObjectId('2'), 
'name': object_id_2_name
'code': object_id_2_code
},
{
'_id': ObjectId('3'), 
'name': object_id_3_name
'code': object_id_3_code
}

DESIRED OUTPUT

{'_id': 1, 
'array': [
{'_id_array': 1, 'type': ObjectId('1'), 'array_other_fields': some_data, 'name': object_id_1_name, 'code': object_id_1_code}
{'_id_array': 2, 'type': ObjectId('2'), 'array_other_fields': some_data, 'name': object_id_2_name, 'code': object_id_2_code}
{'_id_array': 3, 'type': ObjectId('1'), 'array_other_fields': some_data, 'name': object_id_1_name, 'code': object_id_3_code}
]}

{'_id': 2, 
'array': [
{'_id_array': 1, 'type': ObjectId('1'), 'array_other_fields': some_data, 'name': object_id_1_name, **'code'**: object_id_1_code}
{'_id_array': 2, 'type': ObjectId('2'), 'array_other_fields': some_data, 'name': object_id_2_name, 'code': object_id_2_code}
{'_id_array': 3, 'type': ObjectId('3'), 'array_other_fields': some_data, 'name': object_id_3_name, 'code': object_id_3_code}
]}

I can solve this using the code showed above (Mongo Compass Syntax) but I think that is very inefficient:

[
{
    "$unwind": {
        "path": $array"
    }
},
{
    "$lookup": {
        "from": 'FOREING_COLLECTION',
        "localField": 'array.type'
        "foreignField": '_id',
        "as": array.foreign_collection_info
    }
},
{
    "$set": {
        "array.name": {$arrayElemAt: ["$array.foreign_collection_info.name", 0]}
        "array.code": {$arrayElemAt: ["$array.foreign_collection_info.code", 0]}
}
},
{
    "$project":{
        array.foreign_collection_info: 0
    }
},
{
    "$group":{
        "_id": "$_id",
        "array": {"$push": {"$array"}}
    }
}]

So is there any way in which I can do this without using unwind+ set+project+group?

Thank you.

[Asya_Kamsky]

You are correct that it’s very inefficient to unwind an array and then group back by the _id of the original document. There’s no elegant way to do what you want but there’s a workaround that’s far more efficient because it doesn’t involve re-grouping. You can use the array of indexes as is in $lookup and put the results into a new result array and then in the next stage “pair” the original array elements with corresponding new array elements.

There’s code showing how to do it here: SERVER-42306 - that’s the Jira ticket tracking some sort of syntax to allow you to do what you want during the $lookup itself.

Asya