Mongoose aggregate multiple nested arrays-CodePudding

I have 3 schemas:

const userSchema = new mongoose.Schema({
    username:{
        type: String,
        unique: true
    },
    password:{
        type: String
    },
    tasks:{
        type:[mongoose.Types.ObjectId]
    }
})

const taskSchema = new mongoose.Schema({
    title:{
        type: String
    },
    finished:{
        type: Boolean
    },
    deadline:{
        type:Date
    },
    subtasks:{
        type:[mongoose.Types.ObjectId]
    }
})

const subtaskSchema = new mongoose.Schema({
    title:{
        type: String
    },
    finished:{
        type: Boolean
    },
    deadline:{
        type:Date
    },
})

I would like to perform a query that would return something like this:

{
        "_id": "id",
        "username": "username",
        "password": "password",
        "tasks": [
            "_id": "id",
            "title": "title",
            "deadline": "deadline",
             "finished": false,
            "subtasks": [
                         {
                          "_id": "id",
                          "title": "title",
                          "deadline": "deadline",
                          "finished": false
                         }
                        ]

        ]
}

To my understading, aggregate should do this but I'm not quite sure how to handle nested arrays with it. I know that relational DBs would suit this better, but this is the situation I am currently in. Any help is appreciated!

CodePudding user response：

You might not need an aggregate since your user schema already has tasks as an array in them, so you could just call "populate" on the user records via mongoose. Take a look at this for example https://mongoosejs.com/docs/populate.html

CodePudding user response：

in your userSchema, add ref: 'task' or anything you name to your task model to point it in your task collection.

const userSchema = new mongoose.Schema({
    username:{
        type: String,
        unique: true
    },
    password:{
        type: String
    },
    tasks:{
        type:[mongoose.Types.ObjectId],
        ref: 'task'
    }
})

same thing you to your taskSchema, add ref: 'subtask' or whatever you name in your subtask collection.

const taskSchema = new mongoose.Schema({
    title:{
        type: String
    },
    finished:{
        type: Boolean
    },
    deadline:{
        type:Date
    },
    subtasks:{
        type:[mongoose.Types.ObjectId],
        ref: 'subtask'
    }
})

with that you could your populate function rather than aggregate

let populateQuery = [{ path: "task", select: "", populate: { path: "subtask", select: "" } }];

in that case we are have nested populate since subtask is a field in task

let populateQuery = [{ path: "task", select: "", populate: { path: "subtask", select: "" } }];

let users = await Users.find({}).populate(populateQuery);