I tried to aggregate the 2 collections, it works in the first layer of array, but I don't know how to do it in the next layer. This is the data have.

  "app": [
    {
      "_id": "6364da3dc6cded87a8d74084",
      "name": "app1",
      "provider_tenants": [
        {
          "_id": "6364871d84eedd81331c60eb",
          "tenants": [
            {
              "path": "path1",
              "_id": "6364872d84eedd81331c60ec",
              
            }
          ]
        },
        {
          "_id": "63579dd4539180517fd51d71",
          "tenants": [
            {
              "path": "path2",
              "_id": "636485fa84eedd81331c60e6"
            }
          ]
        }
      ]
    }
  ],
  "details_provider": [
    {
      "_id": "63579dd4539180517fd51d71",
      "name": "details_aaaaaa",
      "tenants": [
        {
          "name": "xxxxx",
          "redirect_uri": "www.asdf.com",
          "_id": "636485fa84eedd81331c60e6"
        },
        {
          "name": "zzzzz",
          "redirect_uri": "www.zxcv.com",
          "_id": "6364860684eedd81331c60e7"
        }
      ]
    },
    {
      "_id": "6364871d84eedd81331c60eb",
      "name": "details_bbbbbb",
      "tenants": [
        {
          "name": "yyyyy",
          "redirect_uri": "www.qwer.com",
          "_id": "6364872d84eedd81331c60ec"
        }
      ]
    }
  ]

This is the result of what I have done. mongoplayground

What should I do next to get the results I want? The results I want will look something like this.

[
  {
    "_id": "6364da3dc6cded87a8d74084",
    "name": "app1",
    "provider_tenants": [
      {
        "_id": "6364871d84eedd81331c60eb",
        "name": "details_bbbbbb",
        "tenants": [
          {
            "_id": "6364872d84eedd81331c60ec",
            "name": "yyyyy",
            "redirect_uri": "www.qwer.com",
            "path": "path1",
          }
        ]
      },
      {
        "_id": "63579dd4539180517fd51d71",
        "name": "details_aaaaaa",
        "tenants": [
          {
            "_id": "636485fa84eedd81331c60e6",
            "name": "xxxxx",
            "redirect_uri": "www.asdf.com",
            "path": "path2",
          }
        ]
      }
    ]
  }
]

CodePudding user response：

Extending your pipeline a bit, here's one way you could do it.

db.app.aggregate([
  {
    $lookup: {
      from: "details_provider",
      localField: "provider_tenants._id",
      foreignField: "_id",
      as: "provider"
    }
  },
  {
    "$project": {
      "name": 1,
      "provider_tenants": {
        "$map": {
          "input": "$provider_tenants",
          "as": "ps",
          in: {
            "$let": {
              "vars": {
                "d_prov": {
                  "$first": {
                    "$filter": {
                      "input": "$provider",
                      "as": "prov",
                      "cond": {"$eq": ["$$ps._id", "$$prov._id"]}
                    }
                  }
                }
              },
              "in": {
                $mergeObjects: [
                  "$$ps",
                  {
                    "name": "$$d_prov.name",
                    "tenants": {
                      "$map": {
                        "input": "$$ps.tenants",
                        "as": "tens",
                        "in": {
                          "$mergeObjects": [
                            "$$tens",
                            {
                              "$first": {
                                "$filter": {
                                  "input": "$$d_prov.tenants",
                                  "cond": {"$eq": ["$$tens._id", "$$this._id"]}
                                }
                              }
                            }
                          ]
                        }
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
])

Try it on mongoplayground.net.