Filter null values of two populations in Mongoose

Question

MAIN GOAL

I want to retrieve translations, which are projected as terms (Term) with a single relation (Relation) in between.

STATUS

I'm describing terms Term. Think of it as words (usually nouns).

const TermSchema: Schema = new Schema({
  _id:  mongoose.Schema.Types.ObjectId,  
  term: { type: String, required: true },
  lang: { type: String, required: true},
  relations: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Relation' }],
});

export default mongoose.model<Term & mongoose.Document>('Term', TermSchema);

The Relation schema describes the relations of these terms.

const RelationSchema: Schema = new Schema({
    _id:  mongoose.Schema.Types.ObjectId,  
    relationType: { type: String, required: true, default: RelationEnum.TRANSLATION },
    termSrc: { type: mongoose.Schema.Types.ObjectId, ref: 'Term'  },
    termTrg: { type: mongoose.Schema.Types.ObjectId, ref: 'Term'  },    
  });

export default mongoose.model<Relation & mongoose.Document>('Relation', RelationSchema);

I want to retrieve all translations from a language combination. Let's say German (de) and Arabic (ar). That is:

1. Retrieve all relations with the relationType of value RelationEnum.TRANSLATION.
2. Populate the termSrc where language is either de or ar.
3. Populate the termTrg where language is either de or ar.

Which led me to write the following mongoose statement.

var query = Relation.find({relationType: 'TRANSLATION' }, null)
                .populate( {path: 'termSrc', select: 'term lang', model: Term, match: {lang: {$in: ['ar', 'de']}}})
                .populate( {path: 'termTrg', select: 'term lang', model: Term, match: {lang: {$in: ['ar', 'de']}}});

In most cases that certainly results in what I'm aiming for. Here is a desired sample of a single records, which is returned.

    {
        "_id": "41224d776a326fb40f000023",
        "relationStatus": "QUALIFIED",
        "termSrc": {
            "_id": "41224d776a326fb40f000021",
            "term": "Verhaftung",
            "lang": "de"
        },
        "termTrg": {
            "_id": "41224d776a326fb40f000022",
            "term": "اعتقالات",
            "lang": "ar"
        }
    }

PROBLEM STATEMENT

Since the population of termSrc and termTrg are fully independent (it is like an OR statement, a disjunction), it will also include relations where only one filter applies to the population. Thus the query will also return records for French-Arabic translations. Of course, setting the fr term to null (because we look for 'ar', 'de') as in this sample record.

   {
        "_id": "01224d776a326fb40f000050",
        "relationStatus": "QUALIFIED",
        "termSrc": null,
        "termTrg": {
            "_id": "01224d776a326fb40f000034",
            "term": "تقالا",
            "lang": "ar"
        }
    }

Which is not what I desire and leads to an after-math cleanup. I only want de-ar and ar-de translations.

As my main goal expresses, I'd like to have only relations returned where BOTH terms are populated (none of them should be null).

• Could one 1) run the find then both populate then 2) run a filter on the intermediate result to avoid null records?
• If it cannot be accomplished with intermediate queries, how can one achive this using aggregates, hence filter all the relations with null records in either termSrc and termTrg?

Answer 1

You can use below $lookup version

  Relation.aggregate([
    { $match: { relationType: "TRANSLATION" } },
    {
      $lookup: {
        from: Term.collection.name,
        let: { termSrc: "$termSrc" },
        pipeline: [{ $match: { $expr: { $eq: ["$_id", "$$termSrc"] } } }],
        as: "termSrc"
      }
    },
    { $unwind: "$termSrc" },
    {
      $lookup: {
        from: Term.collection.name,
        let: { termTrg: "$termTrg" },
        pipeline: [{ $match: { $expr: { $eq: ["$_id", "$$termTrg"] } } }],
        as: "termTrg"
      }
    },
    { $unwind: "$termTrg" }
  ]);