Search code examples
mongodbmongoosenosqlaggregation-frameworkgraphlookup

Join $graphLookup result with other collection

I'm working on a hierarchical structure that stores a binary tree. Let's say I have two collections: users and nodes. users collection stores personal information and nodes stores the structure of the tree using the Parent References pattern: https://docs.mongodb.com/manual/tutorial/model-tree-structures-with-parent-references/

Users:

[{
  "_id": {
    "$oid": "600365521599912a5c814e5e"
  },
  "nombre": "Andres",
  "correo": "[email protected]"
},{
  "_id": {
    "$oid": "600365e9ccf1e51b2cab341f"
  },
  "nombre": "Andres",
  "correo": "[email protected]"
},{
  "_id": {
    "$oid": "6004591536a40941f48121f9"
  },
  "nombre": "Laura",
  "correo": "[email protected]"
},{
  "_id": {
    "$oid": "6004596936a40941f48121fb"
  },
  "nombre": "Javi",
  "correo": "[email protected]"
},{
  "_id": {
    "$oid": "60047cf23f3f1a0d647cb2c7"
  },
  "nombre": "Lina",
  "correo": "[email protected]"
}]

nodos:

[{
  "_id": {
    "$oid": "60035d0a1599912a5c814e58"
  },
  "idUsuario": "600365521599912a5c814e5e",
  "nodoPadre": ""
},{
  "_id": {
    "$oid": "60047e6874cab54a7088ca56"
  },
  "idUsuario": "600365e9ccf1e51b2cab341f",
  "nodoPadre": {
    "$oid": "60035d0a1599912a5c814e58"
  }
},{
  "_id": {
    "$oid": "60047f42c89add3c20cff990"
  },
  "idUsuario": "6004591536a40941f48121f9",
  "nodoPadre": {
    "$oid": "60047e6874cab54a7088ca56"
  }
},{
  "_id": {
    "$oid": "60047f5dc89add3c20cff991"
  },
  "idUsuario": "6004596936a40941f48121fb",
  "nodoPadre": {
    "$oid": "60047f42c89add3c20cff990"
  }
},{
  "_id": {
    "$oid": "600480de9fd6a42b40679e6d"
  },
  "idUsuario": "60047cf23f3f1a0d647cb2c7",
  "nodoPadre": {
    "$oid": "60047f5dc89add3c20cff991"
  }
}]

Each document in nodos has corresponding document in users bound by _id and idUsuario.

Generally a document in nodes collection has a parent node in the same collection bound by nodoPadre field.

I'm able to get childs of a node using $graphLookup aggregation:

enter image description here

As you can se I got the childs of a node. Now I need to put the personal information in each child in the result array "hijos" as shown below:

enter image description here

Thanks for your help.

Solution
    • $graphLookup as per your requirement
    • $unwind deconstruct hijos array
    • $addFields convert hijos.idUsuario to object id because its an string, if it is already in object id then remove this stage
    • $lookup with users collection
    • $unwind deconstruct hijos.idUsuario array
    • $addFields to remove hijos if it is blank {} object
    • $group by _id and reconstruct hijos array
    db.nodes.aggregate([
  {
    "$graphLookup": {
      "from": "nodes",
      "startWith": "$_id",
      "connectFromField": "_id",
      "connectToField": "nodoPadre",
      "as": "hijos",
      "maxDepth": 4
    }
  },
  {
    $unwind: {
      path: "$hijos",
      preserveNullAndEmptyArrays: true
    }
  },
  { $addFields: { "hijos.idUsuario": { $toObjectId: "$hijos.idUsuario" } } },
  {
    "$lookup": {
      "from": "users",
      "localField": "hijos.idUsuario",
      "foreignField": "_id",
      "as": "hijos.idUsuario"
    }
  },
  {
    $unwind: {
      path: "$hijos.idUsuario",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $addFields: {
      hijos: {
        $cond: [{ $eq: ["$hijos", {}] }, "$$REMOVE", "$hijos"]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      hijos: { $push: "$hijos" },
      idUsuario: { $first: "$idUsuario" },
      nodoPadre: { $first: "$nodoPadre" }
    }
  }
])

    Playground