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

Working with Data
1

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

collectionA = [
    {
        "id": ObjectId("507f1f77bcf86cd799439011"),
        "uniqueRefId": "UUID-2023-0001",
        "products": [
            {
                "productIndex": 1,
                "productCategory": ObjectId('614g2f77bff86cd755439021'),
                "productOwners": [
                    ObjectId('63ac1e59c0afb8b6f2d41acd')
                ]
            },
            {
                "productIndex": 2,
                "productCategory": ObjectId('614g2f77bff86cd755439021'),
                "productOwners": [
                    ObjectId('63ac1e59c0afb8b6f2d41acd'),
                    ObjectId('63ac1e59c0afb8b6f2d41ace')
                ]
            },
            {
                "productIndex": 3,
                "productCategory": "",
                "productOwners": ""
            }
        ]
    }    
]

collectionB = [
    {
        "_id": ObjectId("63ac1e59c0afb8b6f2d41acd"),
        "fullname": "Jim Corbett",
        "email": "jim.corbett@pp.com"
    },
    {
        "_id": ObjectId("63ac1e59c0afb8b6f2d41ace"),
        "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.

Please provide me the solution how we can get this desirable output asap.

I am expecting the mongodb query for getting this solution.

2

Hi @Aniruddha_Bhattacharya welcome to the community!

I think what you’re looking for is the $lookup aggregation stage. Basically it’s a left outer join operation from one collection to another.

There are extensive examples in the linked documentation page. If you’re having specific issues with the query, please post the query you have tried and how it’s not meeting your needs.

Best regards
Kevin

1 Like