mongodbaggregategraphlookup

Cannot get parent data by mongoDB aggregate graphLookup


Following is the data

[
  {
    "_id": {
      "$oid": "6364f2eee35fc06fa06afb5f"
    },
    "type": "subbranch",
    "parent_id": "635116c18fe4294398842ebb",
    "org_name": "Pune - Delhi"
  },
  {
    "_id": {
      "$oid": "635116c18fe4294398842ebb"
    },
    "type": "branch",
    "org_name": "Delhi Branch",
    "parent_id": "0"
  }
]

query which i have written is as follows

// req.params.id is 6364f2eee35fc06fa06afb5f
let id = mongoose.Types.ObjectId(req.params.id);
        let data = await organisation.aggregate([
            {
                $addFields: { "_idString": { "$toString": "$_id" }}
            },
            {
                $graphLookup: {
                   from: "organisations",
                   startWith: "$parent_id",
                   connectFromField: "parent_id",
                   connectToField: "_idString",
                   as: "parent"
                }
            },
            {
                $match: {_id: id}
            },
        ]);

but in output i get as follow

[
  {
    "_id": "6364f2eee35fc06fa06afb5f",
    "type": "subbranch",
    "parent_id": "635116c18fe4294398842ebb",
    "org_name": "Pune - Delhi",
    "_idString": "6364f2eee35fc06fa06afb5f",
    "parent": [

    ]
  }
]

i am getting empty parent array but expected output is array with parent data in it. any suggestion would be appreciated.


Solution

  • Remember connectFromField expected or extracted from current aggregated collection while connectToField is connected to from orginal collection

    DEMO ON https://mongoplayground.net/p/vYDdOgNt9bW

    The aggregate query be like

    db.collection.aggregate([
      {
        $addFields: {
          "parent_id": {
            $convert: {
              input: "$parent_id",
              to: "objectId",
              onError: "$parent_id",
            }
          }
        }
      },
      {
        $graphLookup: {
          from: "collection",
          startWith: "$parent_id",
          connectFromField: "parent_id",
          connectToField: "_id",
          as: "parent"
        }
      }
    ])
    

    Outputs

    [
      {
        "_id": ObjectId("6364f2eee35fc06fa06afb5f"),
        "org_name": "Pune - Delhi",
        "parent": [
          {
            "_id": ObjectId("635116c18fe4294398842ebb"),
            "org_name": "Delhi Branch",
            "parent_id": "0",
            "type": "branch"
          }
        ],
        "parent_id": ObjectId("635116c18fe4294398842ebb"),
        "type": "subbranch"
      },
      {
        "_id": ObjectId("635116c18fe4294398842ebb"),
        "org_name": "Delhi Branch",
        "parent": [],
        "parent_id": "0",
        "type": "branch"
      }
    ]