I'm using MongoDB with Nodes js and Mongoose, and I'm creating a hierarchical tree structure. In each collection, I have a document with the ID of that document, an entry with the parent ID as ObjectId, and a string with the name of the parent collection called 'collectionName'. I wanted to use graphLookUp to retrieve all the ancestors of a given collection, but I feel like it won't work because not all the ancestors are in the same collection. Do you have a solution?

Thank you in advance for your help :)

Here's what I'd like to retrieve in the end:

{
  "_id": "67cadacfce03c85963934b1d",
  "genre": "Fantastique",
  "parent": {
    "_id": "67c9617c86b4c8d8d2bfb315",
    "nom": "J.K. Rowling",
    "parent": {
      "_id": "67c9615486b4c8d8d2bfb313",
      "titre": "Harry Potter",
      "parent": {
        "_id": "67c960dd86b4c8d8d2bfb30f",
        "nom": "Jolie librairie",
        "ville": "Besançon",
        "parent": null,
        "path": null
      },
      "children": [
        {
          "_id": "67d98929a19f321484d25a14",
          "childrenId": "67c9617c86b4c8d8d2bfb315",
          "collectionName": "Auteur"
        }
      ],
      "parentCollection": "librairie",
      "ancestors": [
        "librairie"
      ]
    },
    "children": [
      {
        "_id": "67d98929a19f321484d25a12",
        "childrenId": "67cadacfce03c85963934b1d",
        "collectionName": "Genre"
      }
    ],
    "parentCollection": "livre",
    "ancestors": [
      "livre",
      "librairie"
    ]
  },
  "parentCollection": "auteur"
}

I'm using MongoDB with Nodes js and Mongoose, and I'm creating a hierarchical tree structure. In each collection, I have a document with the ID of that document, an entry with the parent ID as ObjectId, and a string with the name of the parent collection called 'collectionName'. I wanted to use graphLookUp to retrieve all the ancestors of a given collection, but I feel like it won't work because not all the ancestors are in the same collection. Do you have a solution?

Thank you in advance for your help :)

Here's what I'd like to retrieve in the end:

{
  "_id": "67cadacfce03c85963934b1d",
  "genre": "Fantastique",
  "parent": {
    "_id": "67c9617c86b4c8d8d2bfb315",
    "nom": "J.K. Rowling",
    "parent": {
      "_id": "67c9615486b4c8d8d2bfb313",
      "titre": "Harry Potter",
      "parent": {
        "_id": "67c960dd86b4c8d8d2bfb30f",
        "nom": "Jolie librairie",
        "ville": "Besançon",
        "parent": null,
        "path": null
      },
      "children": [
        {
          "_id": "67d98929a19f321484d25a14",
          "childrenId": "67c9617c86b4c8d8d2bfb315",
          "collectionName": "Auteur"
        }
      ],
      "parentCollection": "librairie",
      "ancestors": [
        "librairie"
      ]
    },
    "children": [
      {
        "_id": "67d98929a19f321484d25a12",
        "childrenId": "67cadacfce03c85963934b1d",
        "collectionName": "Genre"
      }
    ],
    "parentCollection": "livre",
    "ancestors": [
      "livre",
      "librairie"
    ]
  },
  "parentCollection": "auteur"
}

• node.js
• mongodb
• mongoose
• graphlookup

• 1. It may be more useful to provide examples of the original docs in each of their separate collections. Rather than requiring us to de-construct the end result. (I should have mentioned that in the Staging Ground.) 2. Use Mongo Playground's Multiple Collections template to construct the example collections and documents. 3. Your collection names are not case-consistent. "collectionName": "Auteur" vs "parentCollection": "auteur", "Genre", "livre", etc. – aneroid Commented Mar 18 at 23:43
• 4. Separating books by genre and putting them in different collections is not great design. 5. For nested or parent-child structures, take a look at $graphLookup - which does allow looking up a different collection but those have to be provided as a string, not a dynamically changing expression. So "$parentCollection" or "$genre" won't work. – aneroid Commented Mar 18 at 23:43
• In general, structure the documents in MongoDB according to how the data will be queried, not its relationships. If it helps query performance de-normalize, storing the same data in multiple documents/collections so queries don't need to use as many lookups to get results. – Joe Commented Mar 19 at 16:07

1 Answer 1

You're correct that $graphLookup may not work efficiently across multiple collections since it operates within a single collection. However, there are alternative approaches to retrieving hierarchical data when dealing with multiple collections. Here are some solutions:

Solution 1: Multiple Aggregation Queries Since each document references its parent collection (collectionName), you can retrieve ancestors by recursively performing $lookup operations on different collections.

async function getAncestors(collectionName: string, documentId: string) {
    const db = mongoose.connection.db;
    
    let ancestors = [];
    let current = await db.collection(collectionName).findOne({ _id: new mongoose.Types.ObjectId(documentId) });

    while (current?.parent) {
        const parent = await db.collection(current.parentCollection).findOne({ _id: current.parent });
        if (!parent) break;

        ancestors.push({
            _id: parent._id,
            collection: current.parentCollection,
            data: parent
        });

        current = parent;
    }

    return ancestors;
}

// Example usage:
getAncestors("genre", "67cadacfce03c85963934b1d").then(console.log);

How This Works

Fetch the document from the given collection.

Follow the parent reference, retrieving the corresponding document from the parentCollection.

Continue traversing the hierarchy until the top-most parent is found.

Another approach is : How This Works Fetch the document from the given collection.

Follow the parent reference, retrieving the corresponding document from the parentCollection.

Continue traversing the hierarchy until the top-most parent is found.**

Instead of dynamically resolving ancestors, you can store an ancestors array in each document, updating it when inserting or updating a record.

const GenreSchema = new mongoose.Schema({
    genre: String,
    parent: { type: mongoose.Schema.Types.ObjectId, refPath: "parentCollection" },
    parentCollection: String,
    ancestors: [{ type: String }] // Stores collection names
});

Updating Ancestors When Adding a Document typescript

async function createDocument(collectionName, data) {
    const db = mongoose.connection.db;
    let ancestors = [];

    if (data.parent) {
        const parent = await db.collection(data.parentCollection).findOne({ _id: data.parent });
        if (parent) {
            ancestors = [...parent.ancestors, data.parentCollection];
        }
    }

    data.ancestors = ancestors;

    return db.collection(collectionName).insertOne(data);
}