How to write MongoDB query that find multiple paths

Question

Here is the collection with which I working:

- sign1: A1
- sign2: A2

- sign1: A2
- sign2: A5

- sign1: A2
- sign2: A6

- sign1: A2
- sign2: A8

- sign1: A5
- sign2: A8

The query should find the path from A1 to A8

for example, it should find:

path1:
A1 A2 -> A2 A5 -> A5 A8

path2:
A1 A2 -> A2 A8

path3:
A1 A2 -> A2 A6 -> should be ignored since not finishes with A8

Currently, I tried this (partial solution by @ray):

My Query

The first problem with my query is that it returns all paths even if it doesn't finish with A8

The second problem is that not separating paths put everything in one array

As commented in your other question, MongoDB as a document-oriented DB may not be a suitable tool for this kind of graph-based problem. Also, you should not claim the `$graphLookup` idea I provided as your own attempt without any citation. — ray, Dec 09 '22 at 13:35

devblack.exe · Answer 1 · 2022-12-12T19:51:55.743

To filter and return only the paths that end with A8 you will have to use the $match stage again after the $graphLookup.

You can try something like this to return only paths that end with A8

db.collection.aggregate([
  {
    "$match": {
      sign1: "A1"
    }
  },
  {
    "$graphLookup": {
      "from": "collection",
      "startWith": "$sign1",
      "connectFromField": "sign2",
      "connectToField": "sign1",
      "as": "path",
      "depthField": "step"
    }
  },
  {
    "$match": {
      "path.sign2": "A8"
    }
  }
])

Now, if you want to separate the paths into different arrays/documents you will have to use the $unwind stage before the $match stage like this:

db.collection.aggregate([
  {
    "$match": {
      sign1: "A1"
    }
  },
  {
    "$graphLookup": {
      "from": "collection",
      "startWith": "$sign1",
      "connectFromField": "sign2",
      "connectToField": "sign1",
      "as": "path",
      "depthField": "step"
    }
  },
  {
    "$unwind": "$path"
  },
  {
    "$match": {
      "path.sign2": "A8"
    }
  }
])

EDITED: If you want to filter the undesired paths or the ones that have step equal to 0 you can use the $filter operation in your $graphLookup stage like this, to return results based on those two conditions.

db.collection.aggregate([
  {
    "$match": {
      sign1: "A1"
    }
  },
  {
    "$graphLookup": {
      "from": "collection",
      "startWith": "$sign1",
      "connectFromField": "sign2",
      "connectToField": "sign1",
      "as": "path",
      "depthField": "step",
      "maxDepth": 10,
      "filter": {
        "$and": [
          { "step": { "$gt": 0 } },
          { "sign2": "A8" }
        ]
      }
    }
  },
  {
    "$unwind": "$path"
  }
])

The problem with this is that query would fail in graphLookup stage if there are many documents because it allows only 100MB data. How we can filter paths before or during graphLookup? — Vladimir Djukic, Dec 12 '22 at 10:13
You can use the `maxDepth` param in `$graphLookup` to limit the "jumps" that your query will do. It will limit the length of the paths. — devblack.exe, Dec 12 '22 at 11:23
I tried that but sometimes it finds too many paths that are depth 0 and does not finish with the required path, maybe we can somehow throw those that are 0 and finish with the required last element? — Vladimir Djukic, Dec 12 '22 at 19:32
Edited my answer. You can try the `$filter` operation in `$graphLookup`. To return only the desired results based on your conditions. — devblack.exe, Dec 12 '22 at 19:53
The first one also not returning the right result, it should be grouped by steps. But now all steps are out of the array but not grouped — Vladimir Djukic, Dec 13 '22 at 12:51

How to write MongoDB query that find multiple paths

1 Answers1