Nested Relations MongoDb

Question

I need some help. I have 2 MongoDB collections that I want to relate. One is for categories and the other for multimedia content. A category can contain one multimedia content and Many Childs Categories.

After execute aggregation by passing the pipeline I get the Returned Format (See image 1): Media elements inside Category.Media and Child categories inside Category.Childs. My problem is that I don't know how I can insert again Media inside Child Categories (See image 2)

I also leave below the used pipeline.

Returned Result:

Expected Result:

The Pipeline:

[
    {
        "$match": {
            "id_site": 3,
            "id_parent": null,
            "id_class": null
        }
    },
    {
        "$lookup": {
            "from": "categories",
            "localField": "_id",
            "foreignField": "id_parent",
            "as": "Childs"
        }
    },
    {
        "$lookup": {
            "from": "media",
            "localField": "id_media",
            "foreignField": "_id",
            "as": "Media"
        }
    }
]

Thanks in advance. Any suggestion is appreciated.

Answer 1

You'll have to modify the first $lookup to use a pipeline (available from v3.6)

[
    {
        "$match": {
            "id_site": 3,
            "id_parent": null,
            "id_class": null
        }
    },
    {
        "$lookup": {
            "from": "categories",
            "let": {
                "cid": "$_id"
            },
            "pipeline": [
                {
                    "$match": {
                        "$expr": { $eq: ["$id_parent", "$$cid"] }
                    }
                },
                {
                    "$lookup": {
                        "from": "media",
                        "localField": "id_media",
                        "foreignField": "_id",
                        "as": "Media"
                    }
                }
            ],
            "as": "Childs"
        }
    },
    {
        "$lookup": {
            "from": "media",
            "localField": "id_media",
            "foreignField": "_id",
            "as": "Media"
        }
    }
]