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

CodePudding user response：

$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

CodePudding user response：

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.

CodePudding user response：

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