Mongodb - How to Join two documents by key in second document and merge child documents as array to parent document

Question

I have three documents written in mongodb. It is mostly like a folder and file structure. I'm trying to search a category of files by inputting a category Id.

And if any files matches with the category id I need to list these files inside the parent folder document as an embedded array in response.

Document folders

[
    {
        _id: "5f7763103a4af84960f86ae6",
        folderName: "abcd"
    },
    {
        _id: "5f7763103a4af84960f86ae7",
        folderName: "qwerty"
    },
    {
        _id: "5f7763103a4af84960f86ae8",
        folderName: "asdfgh"
    },
]

Document files

[
    {
        _id: "1c7763103a4af84960f86aa5",
        fileName: "test file 1",
        folderId: "5f7763103a4af84960f86ae7",
        categoryId: "6b7763103a4af84960f86ae2"
    },
    {
        _id: "1c7763103a4af84960f86aa6",
        fileName: "test file 2",
        folderId: "5f7763103a4af84960f86ae7",
        categoryId: "6b7763103a4af84960f86ae2"
    },
    {
        _id: "1c7763103a4af84960f86aa7",
        fileName: "test file 3",
        folderId: "5f7763103a4af84960f86ae6",
        categoryId: "6b7763103a4af84960f86ae2"
    },
]

Document categories

[
    {
        _id: "6b7763103a4af84960f86ae1",
        categoryName: "misc"
    },
    {
        _id: "6b7763103a4af84960f86ae2",
        categoryName: "images"
    },
    {
        _id: "6b7763103a4af84960f86ae3",
        categoryName: "other"
    },
]

When I search by a categoryId in files document I need to group all the matching files into an array grouped with the parent folder name.

I'm expecting a result like below

    {
        folder: {
            _id: "5f7763103a4af84960f86ae7",
            folderName: "qwerty",
            files: [
                {
                    _id: "1c7763103a4af84960f86aa5",
                    fileName: "test file 1",
                    folderId: "5f7763103a4af84960f86ae7",
                    categoryId: "6b7763103a4af84960f86ae2"
                },
                {
                      _id: "1c7763103a4af84960f86aa6",
                      fileName: "test file 2",
                      folderId: "5f7763103a4af84960f86ae7",
                      categoryId: "6b7763103a4af84960f86ae2"
                  },
                  {
                      _id: "1c7763103a4af84960f86aa7",
                      fileName: "test file 3",
                      folderId: "5f7763103a4af84960f86ae6",
                      categoryId: "6b7763103a4af84960f86ae2"
                  },
            ]
        }
    }

Answer 1

You can do it like this:

db.folders.aggregate([
  {
    "$lookup": {
      "from": "files",
      "localField": "_id",
      "foreignField": "folderId",
      "as": "files"
    }
  }
])

Here is the working example: https://mongoplayground.net/p/HaYRsK0ulXN