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:

Have opposite gender
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:

$exists can't be used inside $expr idk why
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

Maybe have a look at https://stackoverflow.com/questions/68255363/or-with-if-and-in-mongodb/68255564#68255564 — Wernfried Domscheit, Dec 23 '21 at 10:46

turivishal · Answer 1 · 2021-12-23T17:11:49.280

$exists can't be used inside $expr idk why

$expr Allows the use of aggregation expressions within the query language, and $exists is not an aggregation operator,

You just need to correct the 2 things:

put $expr condition inside first $and condition
put $expr in last $and condition

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

Playground

score 0 · Accepted Answer · answered Dec 23 '21 at 17:05

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.

Oh sorry! yes you're right. I updated person1 age preference now — artsnr, Dec 23 '21 at 17:33
@artsnr [This](https://mongoplayground.net/p/GQWgxAY60R4) should work with your updated person1 age preference. — ray, Dec 23 '21 at 17:36
It doesn't accommodate the case if preference doesn't exists....need existence check on age and preference. If both are there then compare else no comparison and include the doc — artsnr, Dec 24 '21 at 05:57
@artsnr can you update the question with your new test cases? — ray, Dec 24 '21 at 12:07

Mahdi Salah · Answer 3 · 2021-12-23T17:35:07.290

You can use $eq undefined for the field age instead of the $exists

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

   "as": "matches"
}

Mongodb $exists inside $expr in mongodb

3 Answers3