How to write a query that will return the count of objects in table 2 that have a certain field matc-CodePudding

I have two tables, Users and Rides.

I want to return the first and last name of all Users that have 0 rides in the Rides table.

A User object is set up with the following fields:

_id:
StravaConnect:Object {
  AthleteID: 123
}
FirstName:
LastName:

A Ride object is set up with the following fields:

_id:
AthleteID: 123
Length:

How can I aggregate this data to show a list of all users where there are 0 rides where Ride.AthleteID == User.StravaConnect.AthleteID?

Note: I am using MongoDB Compass

CodePudding user response：

Try this pipeline:

$lookup to get users' rides
$match documents where $size of rides array if greater than 0
$project required fields

[
  {
    "$lookup": {
      "from": "rides",
      "localField": "StravaConnect.AthleteID",
      "foreignField": "AthleteID",
      "as": "rides"
    }
  },
  {
    "$match": {
      $expr: {
        "$gt": [
          {
            "$size": "$rides"
          },
          0
        ]
      }
    }
  },
  {
    "$project": {
      "FirstName": 1,
      "LastName": 1
    }
  }
]

Mongo Playground