MongoDB.live, free & fully virtual. Register Now MongoDB.live, free & fully virtual. Register Now

Nested documents big data query

Hello, I’ve just finished writing a query which acts as a sort of “forum” schema, each parent can have multiple children (post -> comments) however I’ve realised now that comments could act as parents themselves and have comments (post -> comment -> comments, etc). Think of it like a Twitter Feed where a main tweet can have comments, those comments can have comments, etc.

Basically, I need to adjust my query to take this into account but I’ve been stuck for a while trying to figure out if there’s a nice way to do it (think of it as a recursive iteration of posts).

My current query looks like this:

db.getCollection('posts').aggregate([
    {
        $match: {
            $and: [
                {"organization": ObjectId("5fb2c122cce3930008fb9e2e")},
                {"type": {
                    $in: ["ASKS", "UPDATE", "WINS"]
                }},
                {"timestamp": {$gte: new Date("2020-11-16 22:54:33.010Z")}},
                {"parent": null}
            ]
        }
    },
    {
        $sort: {
            "timestamp": -1
        }
    },
    {
        $limit: 15
    },
    {
        $lookup: {
            from: "organizations",
            localField: "organization",
            foreignField: "_id",
            as: "organization"
        }
    },
    {
        $unwind: "$organization"
    },
    {
        $lookup: {
            from: "posts",
            let: {"postId": "$_id"},
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq: ["$parent", "$$postId"]
                        }
                    }
                },
                {
                    $lookup: {
                        from: "accounts",
                        let: { "account_id": "$author" },
                        pipeline: [
                            {
                                $match: {
                                    $expr: {
                                        $eq: ["$_id", "$$account_id"]
                                    }
                                }
                            }
                        ],
                        as: "author"
                    }
                },
                {
                    $unwind: "$author"
                },
                {
                     $lookup: {
                        from: "organizations",
                        let: { "organization_id": "$organization" },
                        pipeline: [
                            {
                                $match: {
                                    $expr: {
                                        $eq: ["$_id", "$$organization_id"]
                                    }
                                }
                            }
                        ],
                        as: "organization"
                    }
                },
                {
                    $unwind: "$organization"
                }
            ],
            as: "comments"
        }
    },
    {
        $lookup: {
            from: "accounts",
            localField: "author",
            foreignField: "_id",
            as: "author"
        }
    },
    {
        $unwind: "$author"
    },
    {
        $project: {
            "_id": 1,
            "type": 1,
            "content": 1,
            "visibility": 1,
            "author._id": 1,
            "author.name": 1,
            "author.user": 1,
            "organization._id": 1,
            "organization.name": 1,
            "organization.settings.logo": 1,
            "timestamp": 1,
            "comments._id": 1,
            "comments.type": 1,
            "comments.content": 1,
            "comments.author._id": 1,
            "comments.author.name": 1,
            "comments.author.user": 1,
            "comments.timestamp": 1,
            "comments.parent": 1,
            "comments.organization._id": 1,
            "comments.organization.name": 1,
            "comments.organization.settings.logo": 1
        }
    }
])

Thanks in advance!