How to replace array of object containing ids with the data using MongoDB aggregation

I am having a collection which contains the data like the following and want to have the desirable output which I have mentioned below.

db={
collectionA: [
{
"id": ObjectId("63b7c24c06ebe7a8fd11777b"),
"uniqueRefId": "UUID-2023-0001",
"products": [
{
"productIndex": 1,
"productCategory": ObjectId("63b7c24c06ebe7a8fd11777b"),
"productOwners": [
ObjectId("63b7c2fd06ebe7a8fd117781")
]
},
{
"productIndex": 2,
"productCategory": ObjectId("63b7c24c06ebe7a8fd11777b"),
"productOwners": [
ObjectId("63b7c2fd06ebe7a8fd117781"),
ObjectId("63b7c12706ebe7a8fd117778")
]
},
{
"productIndex": 3,
"productCategory": "",
"productOwners": ""
}
]
}
],
collectionB: [
{
"_id": ObjectId("63b7c2fd06ebe7a8fd117781"),
"fullname": "Jim Corbett",
"email": "jim.corbett@pp.com"
},
{
"_id": ObjectId("63b7c12706ebe7a8fd117778"),
"fullname": "Carry Minatti",
"email": "carry.minatty@pp.com"
},
]
}
Desirable Output = [
{
"id": ObjectId("507f1f77bcf86cd799439011"),
"uniqueRefId": "UUID-2023-0001",
"products": [
{
"productIndex": 1,
"productCategory": ObjectId('614g2f77bff86cd755439021'),
"productOwners": [
{
"_id": ObjectId("63ac1e59c0afb8b6f2d41acd"),
"fullname": "Jim Corbett",
"email": "jim.corbett@pp.com"
}
]
},
{
"productIndex": 2,
"productCategory": ObjectId('614g2f77bff86cd755439021'),
"productOwners": [
{
"_id": ObjectId("63ac1e59c0afb8b6f2d41acd"),
"fullname": "Jim Corbett",
"email": "jim.corbett@pp.com"
},
{
"_id": ObjectId("63ac1e59c0afb8b6f2d41ace"),
"fullname": "Carry Minatti",
"email": "carry.minatty@pp.com"
}
]
},
{
"productIndex": 3,
"productCategory": "",
"productOwners": ""
}
]
}
]

In the collectionA we are having other documents as well, its not just one document.
Similarly for collectionB we are having other documents too.

How we can get this desirable output?

I am expecting the mongodb query for getting this solution.

I have implemented the lookup like the following

db.collectionA.aggregate([
{
"$lookup": {
"from": "collectionB",
"localField": "products.productOwners",
"foreignField": "_id",
"as": "inventory_docs"
}
}
])

You can try this:

db.collectionA.aggregate([
{
"$unwind": "$products"
},
{
"$lookup": {
"from": "collectionB",
"localField": "products.productOwners",
"foreignField": "_id",
"as": "products.productOwners"
}
},
{
"$group": {
"_id": {
id: "$id",
uniqueRefId: "$uniqueRefId"
},
"products": {
"$push": "$products"
}
}
},
{
"$project": {
id: "$_id.id",
uniqueRefId: "$_id.uniqueRefId",
products: 1,
_id: 0
}
}
])

Playground link.

In this query, we do the following:

1. First we unwind the products array, using $unwind.
2. Then we calculate productOwners, using $lookup.
3. Then we group the unwinded elements, using $group.
4. Finally we, project the desired output using $project.