How query for elements in array of subdocuments-CodePudding

This is mongoose schema of route model

const routeSchema = new mongoose.Schema({
    route: {
        type: [{
            stationCode: {
                type: String,
                required: true,
                uppercase: true,
                validate: {
                    validator: async function(val) {
                        const doc = await Station.findOne({
                            code: val,
                        });
                        if (!doc) return false;
                        return true;
                    },
                    message: `A Station with code {VALUE} not found`,
                },
            },
            distanceFromOrigin: {
                type: Number,
                required: [
                    true,
                    'A station must have distance from origin, 0 for origin',
                ],
            },
        }, ],

        validate: {
            validator: function(val) {
                return val.length >= 2;
            },
            message: 'A Route must have at least two stops',
        },
    },
}, {
    toJSON: {
        virtuals: true
    },
    toObject: {
        virtuals: true
    },
});

This schema has a field route as array of documents, each document has a stationCode,

I want to query for all the documents which has "KMME" and "ASN" stationCode in the specified order. Below is example of a document created with this schema

{
    "_id": {
        "$oid": "636957ce994af955df472ebc"
    },
    "route": [{
            "stationCode": "DHN",
            "distanceFromOrigin": 0,
            "_id": {
                "$oid": "636957ce994af955df472ebd"
            }
        },
        {
            "stationCode": "KMME",
            "distanceFromOrigin": 38,
            "_id": {
                "$oid": "636957ce994af955df472ebe"
            }
        },
        {
            "stationCode": "ASN",
            "distanceFromOrigin": 54,
            "_id": {
                "$oid": "636957ce994af955df472ebf"
            }
        }
    ],
    "__v": 0
}

Please suggest a query for this problem or another schema definition for this problem

CodePudding user response：

One simple option is:

db.collection.aggregate([
  {$match: {$expr: {$setIsSubset: [["ASN", "KMME"], "$route.stationCode"]}}},
  {$set: {
      wanted: {$first:{
        $filter: {
          input: "$route",
          cond: {$in: ["$$this.stationCode", ["ASN", "KMME"]]}
        }
      }}
  }},
  {$match: {"wanted.stationCode": "KMME"}},
  {$unset: "wanted"}
])

See how it works on the playground example