Mongodb $exists inside $expr in mongodb

Question

I want to add multiple conditions on join. Join those docs (of the same collection) who met the following conditions:

1. Have opposite gender
2. Have age (IF EXISTS) between the primary doc age preference and primary doc have age (IF EXISTS) between the foreign doc preference (i.e two-way check)

My attempt is the following but has two issues:

1. $exists can't be used inside $expr idk why
2. Age query is one way right now

$lookup: {
       "from": "appusers",
       "let": { 'gen': "$gender",'pref': "$preference" },
       "pipeline": [{
         $match: {$expr: {
         $and: [
             { $ne: ["$gender", "$$gen"]},
             { $or: [
              {$exists: {"$age": false}},
              {$and: [
                 { $gte: ["$age", '$$pref.age_from' ] },
                 { $lte: [ "$age", '$$pref.age_to' ] }
               ]}
             ]}
           ]
        }}}],
   
       "as": "matches"
   }

Example: Input Docs:

    {
      name: "person1",
      age: 36,
      gender: "Male",
      preference: {
        age_from: 25,
        age_to: 35
      }
    }
    
    {
      name: "person2",
      age: 18,
      gender: "Female",
      preference: {
        age_from: 25,
        age_to: 40
      }
    }
    
    {
      name: "person3",
      age: 26,
      gender: "Female",
      preference: {
        age_from: 30,
        age_to: 35
      }
    }
    
    {
      name: "person4",
      age: 26,
      gender: "Female",
      preference: {
        age_from: 30,
        age_to: 40
      }
    }

Output: For person 1 the matches array will show only person 4 (and similarly person 4 match will show person 1) i.e.:

  {
    name: person1,
    age: 36,
    gender: "Male",
    preference: {
      age_from: 28,
      age_to: 35
    },
    matches: [
      {
        name: person4,
        ...
      }
  
    ]
  }

I have viewed this and this but didn't help

Answer 1

For the $exists problem, you can wrap age with $ifNull and use $eq to check for the existence.

For the 2-way age matching, I think you just need to repeat your age matching criteria from person1 to person4 for person4 to person1. Although in your current given test case, no match will be found as person4's age is out of person1's preference.

db.appusers.aggregate([
  {
    "$match": {
      name: "person1"
    }
  },
  {
    $lookup: {
      "from": "appusers",
      "let": {
        "a": "$age",
        "gen": "$gender",
        "pref": "$preference"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $ne: [
                    "$$gen",
                    "$gender"
                  ]
                },
                {
                  $and: [
                    {
                      $or: [
                        {
                          $eq: [
                            {
                              "$ifNull": [
                                "$age",
                                "age-not-exists"
                              ]
                            },
                            "age-not-exists"
                          ]
                        },
                        {
                          $and: [
                            {
                              $gte: [
                                "$age",
                                "$$pref.age_from"
                              ]
                            },
                            {
                              $lte: [
                                "$age",
                                "$$pref.age_to"
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    {
                      $or: [
                        {
                          $eq: [
                            {
                              "$ifNull": [
                                "$$a",
                                "age-not-exists"
                              ]
                            },
                            "age-not-exists"
                          ]
                        },
                        {
                          $and: [
                            {
                              $gte: [
                                "$$a",
                                "$preference.age_from"
                              ]
                            },
                            {
                              $lte: [
                                "$$a",
                                "$preference.age_to"
                              ]
                            }
                          ]
                        }
                      ]
                    }
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "matches"
    }
  }
])

Here is the Mongo playground for your reference.