how to group documents by specific id list in mongodb?-CodePudding

currently I have a 'category' collection in mongodb like this:

{
    "_id": "top1",
    "title": "face",
    "depth": 0
    "parent_category_ids": [],
    "child_category_ids": ["middle1", "middle2"]
},
{
    "_id": "middle1",
    "title": "eyes",
    "depth": 1,
    "parent_category_ids": ["top1"],
    "child_category_ids": ["third1"]
},
{
    "_id": "middle2",
    "title": "mouth",
    "depth": 1,
    "parent_category_ids": ["top1"],
    "child_category_ids": ["third2"]
},
{
    "_id": "third1",
    "title": "eyelid",
    "depth": 2,
    "parent_category_ids": ["middle1"],
    "child_category_ids": []
}

and I want to query and get results like this:

[
    {
    "_id": "top1",
    "title": "face",
    "depth": 0
    "parent_category_ids": [],
    "child_category_ids": ["middle1", "middle2"],
    "child_categories": [
         {
            "_id": "middle1",
            "title": "eyes",
            "depth": 1,
            "parent_category_ids": ["top1"],
            "child_category_ids": ["middle1", "middle2"]
        },
         {
            "_id": "middle2",
            "title": "mouth",
            "depth": 1,
            "parent_category_ids": ["top1"],
            "child_category_ids": ["middle1", "middle2"]
        },
    ]
    },
    {
    "_id": "middle1",
    "title": "eyes",
    "depth": 1,
    "parent_category_ids": ["top1"],
    "child_category_ids": ["third1"],
    "child_categories": [
         {
            "_id": "third1",
            "title": "eyelid",
            "depth": 2,
            "parent_category_ids": ["middle1"],
            "child_category_ids": []
        }
    ]
    }
   ...
]

is there any way of grouping by specified field and make it sub-documents in it? I think I need to use $group or $project but have no idea how to get right query. Thank you for helping me in advance.

CodePudding user response：

As mentioned above, you should use $lookup in your aggregation.

Example playground - https://mongoplayground.net/p/PZDfqP4otuD

Query:

db.collection.aggregate([
  {
    $lookup: {
      from: "collection", // In your case, replace with this 'category'
      localField: "child_category_ids",
      foreignField: "_id",
      as: "child_categories"
    }
  }
])

Note: That you can syntax can either look like from /localField / foreignField / as for a single join condition, or use an additional aggregation pipeline for more complex subqueries. from / let / pipeline / as.