Use $lookup with a Conditional Join

Question

provided I have following documents

User

{
    uuid: string,
    isActive: boolean,
    lastLogin: datetime,
    createdOn: datetime
}

Projects

{
    id: string,
    users: [
        {
            uuid: string,
            otherInfo: ...
        },
        {... more users}
    ]
}

And I want to select all users that didn't login since 2 weeks and are inactive or since 5 weeks that don't have projects.

Now, the 2 weeks is working fine but I cannot seem to figure out how to do the "5 weeks and don't have projects" part

I came up with something like below but the last part does not work because $exists obviously is not a top level operator.

Anyone ever did anything like this? Thanks!

return await this.collection
    .aggregate([
        {
            $match: {
                $and: [
                    {
                        $expr: {
                            $allElementsTrue: {
                                $map: {
                                    input: [`$lastLogin`, `$createdOn`],
                                    in: { $lt: [`$$this`, twoWeeksAgo] }
                                }
                            }
                        }
                    },
                    {
                        $or: [
                            {
                                isActive: false
                            },
                            {
                                $and: [
                                    {
                                        $expr: {
                                            $allElementsTrue: {
                                                $map: {
                                                    input: [`$lastLogin`, `$createdOn`],
                                                    in: { $lt: [`$$this`, fiveWeeksAgo] }
                                                }
                                            }
                                        }
                                    },
                                    {
                                        //No projects exists on this user
                                        $exists: {
                                            $lookup: {
                                                from: _.get(Config, `env.collection.projects`),
                                                let: {
                                                    currentUser: `$$ROOT`
                                                },
                                                pipeline: [
                                                    {
                                                        $project: {
                                                            _id: 0,
                                                            users: {
                                                                $filter: {
                                                                    input: `$users`,
                                                                    as: `user`,
                                                                    cond: {
                                                                        $eq: [`$$user.uuid`, `$currentUser.uuid`]
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                ]
                                            }
                                        }
                                    }
                                ]
                            }
                        ]
                    }
                ]
            }
        }
    ])
    .toArray();

Answer 1

Not certain why you thought $expr was needed in the initial $match, but really:

const getResults = () => {

  const now = Date.now();
  const twoWeeksAgo = new Date(now - (1000 * 60 * 60 * 24 * 7 * 2 ));
  const fiveWeeksAgo = new Date(now - (1000 * 60 * 60 * 24 * 7 * 5 ));

  // as long a mongoDriverCollectionReference points to a "Collection" object
  // for the "users" collection

  return mongoDriverCollectionReference.aggregate([   
    // No $expr, since you can actually use an index. $expr cannot do that
    { "$match": {
      "$or": [
        // Active and "logged in"/created in the last 2 weeks
        { 
          "isActive": true,
          "$or": [
            { "lastLogin": { "$gte": twoWeeksAgo } },
            { "createdOn": { "$gte": twoWeeksAgo } }
          ]
        },
        // Also want those who...
        // Not Active and "logged in"/created in the last 5 weeks
        // we'll "tag" them later
        { 
          "isActive": false,
          "$or": [
            { "lastLogin": { "$gte": fiveWeeksAgo } },
            { "createdOn": { "$gte": fiveWeeksAgo } }
          ]
        }
      ]
    }},

    // Now we do the "conditional" stuff, just to return a matching result or not

    { "$lookup": {
      "from":  _.get(Config, `env.collection.projects`), // there are a lot cleaner ways to register models than this
      "let": {
        "uuid": {
          "$cond": {
            "if": "$isActive",   // this is boolean afterall
            "then": null,       // don't really want to match
            "else": "$uuid"     // Okay to match the 5 week results
          }
        }
      },
      "pipeline": [
        // Nothing complex here as null will return nothing. Just do $in for the array
        { "$match": {  "$in": [ "$$uuid", "$users.uuid" ] } },

        // Don't really need the detail, so just reduce any matches to one result of [null]
        { "$group": { "_id": null } }
      ],
      "as": "projects"
    }},

    // Now test if the $lookup returned something where it mattered
    { "$match": {
      "$or": [
        { "active": true },                   // remember we selected the active ones already
        {
          "projects.0": { "$exists": false }  // So now we only need to know the "inactive" returned no array result.
        }
      ]
    }}
  ]).toArray();   // returns a Promise
};

It's pretty simple as calculated expressions via $expr are actually really bad and not what you want in a first pipeline stage. Also "not what you need" since createdOn and lastLogin really should not have been merged into an array for $allElementsTrue which would just be an AND condition, where you described logic would really mean OR. So the $or does just fine here.

So does the $or on the separation of conditions for the isActive of true/false. Again it's either "two weeks" OR "five weeks". And this certainly does not need $expr since standard inequality range matching works fine, and uses an "index".

Then you really just want to do the "conditional" things in the let for $lookup instead of your "does it exist" thinking. All you really need to know ( since the range selection of dates is actually already done ) is whether active is now true or false. Where it's active ( meaning by your logic you don't care about projects ) simply make the $$uuid used within the $match pipeline stage a null value so it will not match and the $lookup returns an empty array. Where false ( also already matching the date conditions from earlier ) then you use the actual value and "join" ( where there are projects of course ).

Then it's just a simple matter of keeping the active users, and then only testing the remaining false values for active to see if the "projects" array from the $lookup actually returned anything. If it did not, then they just don't have projects.

Probably should note here is since users is an "array" within the projects collection, you use $in for the $match condition against the single value to the array.

Note that for brevity we can use $group inside the inner pipeline to only return one result instead of possibly many matches to actual matched projects. You don't care about the content or the "count", but simply if one was returned or nothing. Again following the presented logic.

This gets you your desired results, and it does so in a manner that is efficient and actually uses indexes where available.

Also return await certainly does not do what you think it does, and in fact it's an ESLint warning message ( I suggest you enable ESLint in your project ) since it's not a smart thing to do. It does nothing really, as you would need to await getResults() ( as per the example naming ) anyway, as the await keyword is not "magic" but just a prettier way of writing then(). As well as hopefully being easier to understand, once you understand what async/await is really for syntactically that is.