MongoDB/Mongoose Query: Retrieve Routes from Location A to Location B with Precedence Constraints

Question

I am working on a MongoDB project using Mongoose and have encountered a challenge with querying route documents. Each route document has a structure like this:

const locationSchema = new mongoose.Schema({
  name: { type: String, required: true },
  precedence: { type: Number, required: true },
});

const routeSchema = new mongoose.Schema({
  name: { type: String, required: true },
  locations: [{ type: locationSchema, required: true }],
});

The locations array contains subdocuments with fields name and precedence.

Precedence here represents - If a route has locations A, B, and C with precedences 1, 2, and 3 respectively, it means that location A will be covered first, followed by B, and then C.

Now, I need to retrieve all routes that go from Location A to Location B, where the precedence of Location A is smaller than the precedence of Location B.

if this is the data :

[
  {
    "name": "routeA",
    "locations":[
      {
        "name":"A",
        "precedence":1
      },
            {
        "name":"B",
        "precedence":2
      },
            {
        "name":"C",
        "precedence":3
      }
      ]
  },
  {
    "name": "routeB",
    "locations":[
      {
        "name":"A",
        "precedence":2
      },
            {
        "name":"B",
        "precedence":1
      },
            {
        "name":"C",
        "precedence":3
      }
      ]
  }
]

I should get only routeA.

I tried using aggregate, but i didn't got the required result.

Answer 1

One option is:

1. $match only documents where locations includes both A and B
2. Use $filter and $sortArray to add a new temp array of A and B only, where A is first.
3. $match only documents where the first precedence is smaller
4. Format

db.collection.aggregate([
  {$match: {"locations.name": {$all: ["A", "B"]}}},
  {$addFields: {route: {
        $sortArray: {
          input: {$filter: {
              input: "$locations",
              cond: {$in: ["$$this.name", ["A", "B"]]}
          }},
          sortBy: {name: 1}
        }
  }}},
  {$match: {$expr: {
     $lt: [{$first: "$route.precedence"}, {$last: "$route.precedence"}]
  }}},
  {$unset: "route"}
])

See how it works on the playground example