MongoDb $lookup nested document in array fields-CodePudding

I have a list of document categories ： documentCategory I have a document table
How do I put individual documents in Document into UI children in documentCategory


1、I have a list of document categories ： documentCategory

{
    "_id" : ObjectId("61cd249f50f3abf2e172d743"),
    "name" : "Vue3",
    "pid" : "-1",
    "level" : 1,
    "children" : [ 
        {
            "name" : "UI",
            "pid" : "61cd249f50f3abf2e172d743",
            "level" : 2,
            "_id" : ObjectId("61cd27d1f3b969fec5458d89")
        }
    ]
}


2、I have a document table  
{
    "_id" : ObjectId("61cd4813db3e3db388e1d7cf"),
    "name" : "ElementUI",
    "cid" : "61cd27d1f3b969fec5458d89",
    "cname" : "UI",
    "pid" : "61cd249f50f3abf2e172d743",
    "pname" : "Vue3",
}

Hoping to get results ：


{
    "_id" : ObjectId("61cd249f50f3abf2e172d743"),
    "name" : "Vue3",
    "pid" : "-1",
    "level" : 1,
    "children" : [ 
        {
            "name" : "UI",
            "pid" : "61cd249f50f3abf2e172d743",
            "_id" : ObjectId("61cd27d1f3b969fec5458d89"),  
            children:[
                {name:'ElementUI'},{name:'**'}
            ]
        }
    ]
}

How do I put individual documents in Document into UI children in documentCategory

CodePudding user response：

Since you are trying to match an ObjectId onto a string I had to add the $set stage to convert them into the same type (string).

Note that I named my 2nd collection tables instead of table since best practice is plural

db.collection.aggregate([
  {
    '$match': {
      'name': 'Vue3'
    }
  }, {
    '$unwind': {
      'path': '$children'
    }
  }, {
    '$set': {
      'children._id': {
        '$toString': '$children._id'
      }
    }
  }, {
    '$lookup': {
      'from': 'tables', 
      'localField': 'children._id', 
      'foreignField': 'cid', 
      'as': 'children.children', 
      'pipeline': [
        {
          '$project': {
            'name': 1, 
            '_id': 0
          }
        }
      ]
    }
  }, {
    '$group': {
      '_id': '$_id', 
      'name': {
        '$first': '$name'
      }, 
      'pid': {
        '$first': '$pid'
      }, 
      'level': {
        '$first': '$level'
      }, 
      'children': {
        '$push': '$children'
      }
    }
  }
])