How to get all children documents using $graphLookup of MongoDB

Question

I have this data as below in two collections of MongoDB. categories:

[
  { "_id" : 1, "name" : "A" },
  { "_id" : 2, "name" : "B", "categoryId" : 1 },
  { "_id" : 3, "name" : "C", "categoryId" : 1 },
  { "_id" : 4, "name" : "D", "categoryId" : 2 },
  { "_id" : 5, "name" : "E", "categoryId" : 3 },
  { "_id" : 6, "name" : "F", "categoryId" : 2 }
];

And I have Locos documents:

[
  { "_id" : 1, "name" : "X", "categoryId" : 2 },
  { "_id" : 2, "name" : "Y", "categoryId" : 3 },
  { "_id" : 2, "name" : "B", "categoryId" : 1 } 
]

For example if I want to get all the chilren of category A which has the id 1, i call the function and it returns an array of ids of children categories and if possible the Locos chilren. So the result will be like this:

chilren: {
          categories: [2, 3, 4, 5, 6],
          locos: [1, 2, 3]
         }

If i call the function with the id 2 which is category B, I get the result:

chilren: {
          categories: [2, 6],
          locos: [1]
         }

your sample document does not match your code. I presume your sample document for `categories` and `locos` are masked. Can you provide valid sample data and the corresponding expected output? — ray, Jan 06 '22 at 16:22
Thank you for your fast reply, I fixed the question as you asked. we can have the result without containing it in "children". just two arrays — Sara, Jan 06 '22 at 16:35
because the first category with the id 1 is the parent of all categories hence it is a parent of all locos because the locos are children of categories that are children of that first category. I hope it makes sense to you. I want the locos to be considerate as children of categories in case I am looking for any kind of children for a certain category. thanks — Sara, Jan 06 '22 at 18:09

score 1 · Accepted Answer · answered Jan 06 '22 at 16:43

1

You are on the right track to use $graphLookup.

db.categories.aggregate([
  {
    "$match": {
      _id: 1
    }
  },
  {
    "$graphLookup": {
      "from": "categories",
      "startWith": "$_id",
      "connectFromField": "_id",
      "connectToField": "categoryId",
      "as": "categoryLookup"
    }
  },
  {
    "$graphLookup": {
      "from": "locos",
      "startWith": "$_id",
      "connectFromField": "_id",
      "connectToField": "categoryId",
      "as": "locosLookup"
    }
  },
  {
    "$project": {
      children: {
        categories: {
          "$map": {
            "input": "$categoryLookup",
            "as": "c",
            "in": "$$c._id"
          }
        },
        locos: {
          "$map": {
            "input": "$locosLookup",
            "as": "l",
            "in": "$$l._id"
          }
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.

answered Jan 06 '22 at 16:43

ray

11,310
7
18
42

Thank you so much but It's not accurate on the locos document.. I changed the Id to 2 in the query and it returned all the locos :/ – Sara Jan 06 '22 at 16:50
I guess you don't need the graphlookup on the locos since they don't have hierarchie .. – Sara Jan 06 '22 at 16:51
@Sara if `locos` does not have hierarchy, then why category A will expect [1, 2, 3] for `children.locos`? – ray Jan 06 '22 at 17:10
because the first category with the id 1 is the parent of all categories hence it is a parent of all locos because the locos are children of categories that are children of that first category. I hope it makes sense to you. I want the locos to be considerate as children of categories in case I am looking for any kind of children for a certain category. thanks – Sara Jan 06 '22 at 18:14
to correct my input; yes you need graphlookup for locos but while looking for categories: I mean that for each descendant category we need to check if it has a template so we add it to the array. Thanks again for taking me seriously and I will mark it as useful answer ;) – Sara Jan 06 '22 at 18:19
@Sara is [this](https://mongoplayground.net/p/kiqE50pOTa0) what you are looking for? It is very confusing that in category A example, `id 1` is not included in the categories array while `id 2` is included in category B example. Would be helpful if you can give more explanation on that. – ray Jan 06 '22 at 18:39
How to unwind the given id ? – Sara Jan 06 '22 at 18:43
@Sara it is not very specific for "how to unwind the given id". Can you give the expected output so we know how we can help? – ray Jan 06 '22 at 18:51
it's okey i can manage the rest thank you so much – Sara Jan 06 '22 at 18:56

How to get all children documents using $graphLookup of MongoDB

1 Answers1