mongodb - Mongodb: apply lookup on same collection and get sub-categories inside parent category array using a single query

Question

Here is the schema of category collection, where I am storing categories, sub-categories and further children of sub-categories.

mongoose.Schema({
    name:{ type: Array, required: true}, //It is an array because name can be in multiple languages
    path:{ type: String, required: true}, // All parent categories in an order to have immediate parent at the end
    parent: { type: String, required: true}
});

I have added index over all the three fields. Now, what the requirement is - need to get all children categories nested in parent categories array returned as a result of single query.

For categories that are root, parent has '/' and path is '/' separated ids upto immediate parent.

What I have tried is, getting all subcategories of a parent category by passing the _id, Like -

categoryModel.find({parent: /^\/documentId/})

But, I want in result all the parent categories with sub categories nested in it. How to do that?

Expected output -

[
    {
        _id: '1',
        name: 'MainCategory1',
        path: '/'
        parent: '/',
        children: [
            {
                _id: '3',
                name: 'SubCategory3',
                parent: '1',
                path: '/1'
            },
        ]
    },
    {
        _id: '2',
        name: 'MainCategory2',
        path: '/'
        parent: '/',
        children: [
            {
                _id: '5',
                name: 'SubCategory5',
                parent: '2',
                path: '/2',
                children: [
                    {
                        _id: '7',
                        name: 'SubCategory7',
                        parent: '5',
                        path: '/2/5',
                        children: [
                        {
                            _id: '9',
                            name: 'SubCategory7',
                            parent: '7',
                            path: '/2/5/7',
                        }]
                    },
                ]
            },
        ]
    },
]

标签： mongodbaggregation-framework

Answer 1

我通过使用聚合$graphlookup和得到了结果$match，尽管它不是所需的确切格式。我需要的格式是在得到查询响应后构建的。这是我的查询

{ $match: {parent : '/'}}, 
        {
            $graphLookup: {
                from: "category",
                startWith: "$_id",
                connectFromField: "_id",
                connectToField: "parent",
                depthField: "depth",
                as: "children"
        },
},

这是我从上面的查询中得到的响应

    [{
         "_id":"1",
         "name":"category1",
         "parent":'/',
         "children":[
             {
                "_id":"3",
                "name":"Sub-Category1",
                "parent":"1",
                "depth":0
             }
          ]
    },
    {
        "_id":"2",
        "name":"Category2",
        "parent":'/',
        "children":[
             {
                "_id":"4",
                "name":"Sub-Category2",
                "parent":"2",
                "depth":0
             },
             {
                 "_id":"5",
                 "name":"Sub-Category3",
                 "parent":"4",
                 "depth":1
             }
        ]
    }]

其中depth 0，depth 1是子类别相对于父类别的级别。