Find nested object in array only if it matches sequence

Let me give you a background, so I'm working on a timetable app for bus. I have my data in this form (I've simplified the data for the purpose of the question):

{    "routeId": 3215,    "from": "cityA",    "departureTime": "15:40",    "to": "cityD",    "arrivalTime": "18:40",    "path": [      {        "name": "cityA",        "sequence": 1      },      {        "name": "cityB",        "sequence": 2      },      {        "name": "cityC",        "sequence": 3      },      {        "name": "cityD",        "sequence": 4      }    ]  }  

Now when someone search for bus from city B to city C I can do something like this to get objects where I have both city in path array:

db.route.find({     "path.name": "cityB",    "path.name": "cityC"  })  

But how will I query the data such that I only get objects where city B comes before city C in path array? Also please suggest if I can structure the data in a more efficient way.

https://stackoverflow.com/questions/65388522/find-nested-object-in-array-only-if-it-matches-sequence December 21, 2020 at 02:42PM