Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to exclude null values from Mongoose populate query

Tags:

I am building an app and I have create 2 models.

  const UserSchema = new Schema({
    _id: Schema.Types.ObjectId,
    account:{
      type: String,
      unique: true
    }, 
    email: String,
    first_name: String,
    last_name: String
}

  const VenueSchema = new Schema({
    _id: Schema.Types.ObjectId,
    venue_type: String,
    capacity: Number
  })

and

const MediatorSchema = new Schema({
    _id: Schema.Types.ObjectId,
    account:{
      type: String,
      unique: true
    },
    user: {type: Schema.Types.ObjectId, 
      ref:'User'
    }
    venue: {type: Schema.Types.ObjectId, 
      ref:'Venue'
    }
  })

the mediator Schema is created in order to populate multiple paths .

The problem is that when i try to create a query like

var populateQuery = [{path:'user',match: { account:'testuser1'},select:'email'},{path:'venue',match: { venue_type: 'club'}, select:'venue_type'}];

const confirmedVenues = await  Mediator.find({})  
.exists('venue',true)
.populate(populateQuery)
.exec();

the returned array contains objects with null values when the match is not fulfilled.

For example when I query with the previous matches I get the following results

enter image description here

So what I want is , when a user or venue or something is NULL ( so the match is not fulfilled) , the Whole object not to be returned.

I got the following solution but i dont want to do it this way

var i =confirmedVenues.length;
while(i--){
  if(confirmedVenues[i].user == null){
    temp.splice(i,1)
  }
}
like image 996
Nikos Chatzivasileiadis Avatar asked Sep 03 '18 12:09

Nikos Chatzivasileiadis


People also ask

Why is populate returning NULL?

Deep (multiple level) populate returns null if reference schema is not one of the parent schemas.

How to query for null in MongoDB?

MongoDB fetch documents containing 'null' If we want to fetch documents from the collection "testtable" which contains the value of "interest" is null, the following mongodb command can be used : >db. testtable. find( { "interest" : null } ).

What does populate in mongoose?

Mongoose Populate() Method In MongoDB, Population is the process of replacing the specified path in the document of one collection with the actual document from the other collection.

What does REF do in Mongoose schema?

The ref option is what tells Mongoose which model to use during population, in our case the Story model. All _id s we store here must be document _id s from the Story model. Note: ObjectId , Number , String , and Buffer are valid for use as refs.


1 Answers

Finally , I had to use aggregation here , there are not other option .

The schemas dont need to change

var results =await dbo.collection('mediators').aggregate([
     { $lookup:
         {
           from: 'venues',
           localField: 'venue',
           foreignField: '_id',
           as: 'venue'
         }
      },
        $match:{$and:[{"venue.venue_type":req.query.venue_type} , {"venue.capacity":{$gte:parseInt(req.query.capacitylb) , $lte:parseInt(req.query.capacityub)}}]}
      },{
        $lookup:
         {
           from: 'users',
           localField: 'user',
           foreignField: '_id',
           as: 'user'
         }
      },{
        $lookup:
        {
           from: 'professionals',
           localField: 'professional',
           foreignField: '_id',
           as: 'professional'
        }
      },{
        $lookup:
        {
          from:'availabilities',
          localField: 'availability',
          foreignField: '_id',
          as: 'availability'
        }
      },{
        $unwind: '$availability'
      },{
        $match:{$and:[{"availability.start":{$lte:new Date(req.query.dateFrom)}},{"availability.end":{$gte:new Date(req.query.dateTo)}}]}
      },{
        $lookup:
        {
          from:'locations',
          localField: 'location',
          foreignField: '_id',
          as: 'location'
        }
      },{
        $project:{
          "_id":1,
          "email":"$user.email",
          "organization_name":"$user.organization_name",
          "website":"$user.website",
          "profile_pic_hash":"$user.profile_pic_hash",
          "bio_hash":"$user.bio_hash",
          "venue_type":"$venue.venue_type",
          "capacity":"$venue.capacity",
          "flat_fee":"$professional.flat_fee",
          "per_participant_fee":"$professional.per_participant_fee",
          "hourly_fee":"$professional.hourly_fee",
          "start_date":"$availability.start",
          "end_date":"$availability.end",
          "distance":"$dist.calculated",
          "location":"$location.location.coordinates",
          "country":"$location.country",
          "city":"$location.city",
          "street":"$location.street",
          "number":"$location.number",
          "zip":"$location.zip"}}

It worked perfect for me. Thanks Anthony Winzlet for your help.

like image 62
Nikos Chatzivasileiadis Avatar answered Oct 13 '22 08:10

Nikos Chatzivasileiadis