How to aggregate arrays to nested arrays by level?

Question

I need your help for an aggregation query. I've made some searches before posting, but none of results found did the trick for me.

I have data collection:

[
    { _id: 1, name: 'AA' },
    { _id: 2, name: 'BB', parent: 1, ancestors: [1] },
    { _id: 3, name: 'CC' },
    { _id: 4, name: 'DD', parent: 3, ancestors: [3] },
    { _id: 5, name: 'DD', parent: 4, ancestors: [3, 4] }
]

I need result to present like:

[
    {
        _id: 1,
        name: 'AA',
        child:
            [
                {
                    _id: 2,
                    name: 'BB',
                    parent: 1,
                }
            ]
    },
    {
        _id: 3,
        name: 'CC',
        child: [
            {
                _id: 4,
                name: 'DD',
                parent: 3,
                child: [
                    {
                        _id: 5,
                        name: 'DD',
                        parent: 4
                    }
                ]
            }
        ]
    }
]

I hope anyone help me, thanks.

Answer 1

What you are trying to do is recursive iteration for child lookup. if you already know how deep you want to go like one level or two level then its fine. Here is query for going two level deep by using nested lookup.

db.users.aggregate([
    {
        $lookup: {
            let: { id: "$_id" },
            from: 'users',
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq: ["$$id", "$parent"]
                        }
                    }
                },
                {
                    $lookup: {
                        from: "users",
                        localField: '_id',
                        foreignField: 'parent',
                        as: "child"
                    }
                }
            ],
            as: 'child'
        }        
    }
])

It's not a good idea to go much deeper because it can cost you heavy performance.

better way is to define your schema such way so you don't need to go so deep.

Hope it helps.