How to query Child Reference Pattern collection with $graphLookup

Question

I have a collection with child reference. Each document can have multiple parents. How can I query it with $graphLookup in order to make a result prepared for a treeview?

Example:

{
    "_id" : ObjectId("6143450cc0318c23d8f18424"),
    "id" : "3",
    "name" : "prod03",
    "children" : [
        {
            "_id" : "6143440ac0318c23d8f1841f",
            "qty" : 10
        },
        {
            "_id" : "614344b1c0318c23d8f18422",
            "qty" : 100
        }
    ],
    "totalQty" : 110
},

{
    "_id" : ObjectId("614344b1c0318c23d8f18422"),
    "id" : "2",
    "name" : "prod02",
    "children" : [ ],
    "totalQty" : 100
},

{
    "_id" : ObjectId("6143440ac0318c23d8f1841f"),
    "id" : "1",
    "name" : "prod01",
    "children" : [ ],
    "totalQty" : 10
}

Prod03 is formed from prod01 and prod02

The desired result would be like:

{
  id: '3',
  name: 'prod03',
  totalQty: 110 
  children: [
    {
      id: '1',
      name: 'prod01',
      qty: 10
    },
    {
      id: '2',
      name: 'prod02',
      qty: 100
    },
  ],
}

The query must go multiple levels down until find no more children. Final result would be a tree with all history of product manufacture components.

Answer 1

You are actually on the right track to use $graphLookup. You just need to convert children._id back to objectId from String for lookup.

db.collection.aggregate([
  {
    "$match": {
      "id": "3"
    }
  },
  {
    "$addFields": {
      "children": {
        "$map": {
          "input": "$children",
          "as": "c",
          "in": {
            "_id": {
              "$toObjectId": "$$c._id"
            },
            "qty": "$$c.qty"
          }
        }
      }
    }
  },
  {
    "$graphLookup": {
      "from": "collection",
      "startWith": "$children._id",
      "connectFromField": "children._id",
      "connectToField": "_id",
      "as": "children"
    }
  },
  {
    "$addFields": {
      "children": {
        "$map": {
          "input": "$children",
          "as": "c",
          "in": {
            "id": "$$c.id",
            "name": "$$c.name",
            "qty": "$$c.totalQty"
          }
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.