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 stackoverflow.com/questions/68255363/or-with-if-and-in-mongodb/… — Wernfried Domscheit
– Wernfried Domscheit, Commented Dec 23, 2021 at 10:46

turivishal · Accepted Answer · 2021-12-23 17:11:49Z

$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

ray · Accepted Answer · 2021-12-23 17:05:20Z

0

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.

answered Dec 23, 2021 at 17:05

ray

15.6k12 gold badges96 silver badges104 bronze badges

4 Comments

artsnr Over a year ago

Oh sorry! yes you're right. I updated person1 age preference now

ray Over a year ago

@artsnr This should work with your updated person1 age preference.

artsnr Over a year ago

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

ray Over a year ago

@artsnr can you update the question with your new test cases?

Mahdi Salah · Accepted Answer · 2021-12-23 17:35:07Z

0

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"
}

edited Dec 23, 2021 at 17:35

answered Dec 23, 2021 at 17:14

Mahdi Salah

1892 silver badges12 bronze badges

Collectives™ on Stack Overflow

Mongodb $exists inside $expr in mongodb

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related