I have a problem with lookup 2 collections in MongoDB my database consists of 3 collections: PROJECT, CATEGORY, and SUBCATEGORY, where 1 PROJECT can be in many CATEGORY and 1 CATEGORY can have many SUBCATEGORY

PROJECT

  "project": [
    {
      _id: 234,
      title: "proj 1",
      description: "description 1",
      category: [
        {
          _id: 1,
          subcategory: [
            {
              _id: 11
            },
            {
              _id: 12
            }
          ]
        },
        {
          _id: 2,
          subcategory: [
            {
              _id: 21
            },
            {
              _id: 23
            }
          ]
        }
      ]
    },

CATEGORY

"category": [
    {
      _id: 1,
      title: "cate 1",
      subcategory: [
        {
          _id: 11,
          
        },
        {
          _id: 12,
          
        },
        {
          _id: 13,
          
        },
        {
          _id: 14,
          
        },
        
      ]
    },
    {
      _id: 2,
      title: "cate 2",
      subcategory: [
        {
          _id: 21,
          
        },
        {
          _id: 22,
          
        },
        {
          _id: 23,
          
        },
        
      ]
    }
]

SUBCATEGORY

 "subcategory": [
    {
      "_id": 11,
      title: "subcate 11",
      
    },
    {
      "_id": 12,
      title: "subcate 12",
      
    },
    {
      "_id": 13,
      title: "subcate 13",
      
    },
    {
      "_id": 14,
      title: "subcate 14",
      
    },
    {
      "_id": 21,
      title: "subcate 21",
      
    },
    {
      "_id": 22,
      title: "subcate 22",
      
    },
    {
      "_id": 23,
      title: "subcate 23",
      
    },
  ]

I want to lookup both categoryId and subcategoryId, but I can only lookup at one of them at a time, so when I lookup both of them, I cannot join their relation back together. For example, 1st way, when I lookup subcategoryId first, I got the details of subcategory but when I try to lookup the categoryId, the category details that contain subcategory overwrite the subcategoryLookup. On the other hand when I lookup categoryId first, I lost the information of subcategoryId.

This is the data I want

[
  {
    "_id": 234,
    "title": "proj 1"
    "description: "description 1"
    "category": [
      {
        "_id": 1,
        "title": "cate 1"
        "subcategory": [
          {
            "_id": 11,
            "title": "subcate 11"
          },
             {
            "_id": 12,
            "title": "subcate 12"
          }
        ],
      },
      {
        "_id": 2,
        "title": "cate 2"
        "subcategory": [
          {
            "_id": 21,
            "title": "subcate 21"
          },
            {
            "_id": 23,
            "title": "subcate 23"
          }
        ],
      }
    ],
  }
]

I have tried a number of ways and now I am at this stage where I got the group of CATEGORY but not PROJECT

https://mongoplayground.net/p/LXJ-BsTWX3a

Please help me, I can't think of a way to structure these data together

CodePudding user response：

The code is

db.project.aggregate([
  {
    $match: {
      _id: 234
    }
  },
  {
    "$unwind": "$category"
  },
  {
    "$lookup": {
      "from": "subcategory",
      "localField": "category.subcategory._id",
      "foreignField": "_id",
      "as": "category.subcategory"
    }
  },
  {
    "$lookup": {
      "from": "category",
      "localField": "category._id",
      "foreignField": "_id",
      "as": "joinFortitle"
    }
  },
  {
    "$addFields": {
      "category.title": {
        "$arrayElemAt": [
          "$joinFortitle",
          0
        ]
      }
    }
  },
  {
    "$addFields": {
      "category.title": "$category.title.title"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "title": {
        "$first": "$title"
      },
      "description": {
        "$first": "$description"
      },
      "category": {
        $push: "$category"
      }
    }
  }
])

Working Mongo play ground