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": "oachica@gmail.com"
},{
  "_id": {
    "$oid": "600365e9ccf1e51b2cab341f"
  },
  "nombre": "Andres",
  "correo": "cachi777_@hotmail.com"
},{
  "_id": {
    "$oid": "6004591536a40941f48121f9"
  },
  "nombre": "Laura",
  "correo": "w.l777@hotmail.com"
},{
  "_id": {
    "$oid": "6004596936a40941f48121fb"
  },
  "nombre": "Javi",
  "correo": "jocta@hotmail.com"
},{
  "_id": {
    "$oid": "60047cf23f3f1a0d647cb2c7"
  },
  "nombre": "Lina",
  "correo": "lvelas@hotmail.com"
}]

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

  • 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