How to join multiple collections in mongoDB to get concrete data in one Query PHP

Question

I just started mondoDB a month back and I am currently working to join/pull data from multiple collections (Just like extracting data from a foreign table where the primary table has a foreign key)

I have below collection dump in products collection

{
"_id": {
    "$oid": "600aef5d01290000270051e4"
},
"user_id": "600adb7f01290000270051df",
"details": {
    "name": "My product",
    "description": "some prod description goes here",
    "category": "5fef4a467d1b000086000745"
},
"images": [{
    "size_100x128": "prods/2021/Jan/prod_ab679096373ab328bf454447abc304c5_100x128.jpeg",
    "size_200x256": "prods/2021/Jan/prod_ab679096373ab328bf454447abc304c5_200x256.jpeg",
    "size_300x385": "prods/2021/Jan/prod_ab679096373ab328bf454447abc304c5_300x385.jpeg",
    "size_500x642": "prods/2021/Jan/prod_ab679096373ab328bf454447abc304c5_500x642.jpeg"
}],
"attributes": {
    "for": ["5fef71907d1b000086000761", "5fef71907d1b000086000761"],
    "colors": ["5fef719e7d1b000086000763", "5fef719e7d1b000086000763", "5fef719e7d1b000086000763"],
    "sizes": [{
        "product_size_id": "5fef716d7d1b00008600075b",
        "quantity": "9"
    }, {
        "product_size_id": "5fef716d7d1b00008600075b",
        "quantity": "1"
    }]
},
"total_quantity": 10,
"created": "2021-01-22 20:59:33",
"modified": "2021-01-22 22:54:16",
}

From the above dump, you can see that the attributes section has a lot of IDs that reside to data in other tables. I want to execute a single query that can give me a name related to that ID from foreign Table

I saw a lot of posts from StackOverflow [this was the last that I saw][1] then i somehow got to make below code

$productsArray = $collection->aggregate(
[
    [
        '$match' => [
                                '_id' => new MongoDBBSONObjectID( $product_id )
                            ]
    ],
    [
        '$lookup' => [
                                'from' => 'product_for',
                                'localField' => 'attributes.for',
                                'foreignField' => '_id',
                                'as' => 'productFor'
                            ]
    ],
    // [
    //  '$unwind' => '$productFor'
    // ],
]

);

I commented unwind because if I use it then it does now show all data but when I uncomment it I get all data but I am not able to get data from foreign fields. I have almost banged my head everywhere and stuck on how to make it happen.

Below is the collection dump from product_for

{
"_id": {
    "$oid": "5fef71907d1b000086000761"
},
"created_console_id": {
    "$oid": "5f9af88dad3ebff51648211d"
},
"name": "Gents",
"created": "2021-01-02 00:31:36",
"modified": "2021-01-02 00:31:36",
"last_modified_by": {
    "$oid": "5f9af88dad3ebff51648211d"
    }
}

It would be great if someone can help mere

question from:https://stackoverflow.com/questions/65859343/how-to-join-multiple-collections-in-mongodb-to-get-concrete-data-in-one-query-ph

Answer 1

Waitting for answers