I have this mongoose aggregate that returns the right records but do not include fields in the lookup collection i.e referenced collection.
These are my schemas:
const usersSchema = mongoose.Schema(
{
fullname: {
type: String,
required: [true, 'First Name cannot be empty'],
trim: true,
index: true,
},
email: {
type: String,
validate: [validator.isEmail, 'Provide a valid email'],
trim: true,
},
password: {
type: String,
required: [true, 'Password is required'],
},
}, {
timestamps: true,
},
);
const Users = mongoose.model('User', usersSchema);
module.exports = Users;
const sub_origin_info_schema = mongoose.Schema({
sub_garrage_user1: { //It is an object ID of users collection
type: String,
required: false,
},
garrage_name1: {
type: String,
required: false,
},
sub_garrage_user2: { //It is an object ID of users collection
type: String,
required: false,
},
garrage_name2: {
type: String,
required: false,
},
})
const motorSchema = new mongoose.Schema(
{
user_id:{
type: { type: Schema.Types.ObjectId, ref: 'User'},
},
garrage_of_origin_user:{ //It is an object ID of users collection
type: String,
required: true,
},
motor_code: {
type: String,
required: true,
unique: true,
},
motor_name: {
type: String,
required: true,
},
motor_number: {
type: String,
required: true,
},
imo_number: {
type: String,
required: true,
},
garrage_of_origin: {
type: String,
required: true,
},
journey_date: {
type: String,
required: true,
},
sub_origin_garage: sub_origin_info_schema,
},
{
timestamps: true,
},
);
const Motors = mongoose.model('Motor', motorsSchema);
module.exports = Motors;
The query worked but does not work as perfectly. It doesn’t include the fullname and email of the referenced Collection using the field mentioned.
I want to achieve two things,
-
I want to pull the user details (fullname and email) from the Users collection using the garrage_of_origin_user field in the Motors schema collection. The query worked but the fullname and email are null i.e they were not pulled.
-
How do I pull details (fullname and email) of the users in the subdocument sub_origin_garrage array object which are “sub_garrage_user1” and “sub_garrage_user2” fields? Note that there are users in the subdocuments (sub_origin_garage) too. How do I use lookup to pull out the fields in the users collection for this 3 users i.e garrage_origin_user, sub_garrage_user1 and sub_garrage_user2 in one document?
Motor.aggregate([
//{ $match : { '_id' : '674ed7fd61f952ddf3e4a661' } },
{ $unwind: "$sub_origin_garage" },
{ $unwind: "$sub_origin_garage.sub_garrage_user1" },
{ $match :
{ $or: [
{ 'sub_origin_garage.sub_garrage_user1' : '6750838a51d231d3178343c7' },
{ 'sub_origin_garage.sub_garrage_user2' : '6750838a51d231d319656209' },
]
}
},
{ $sort : {journey_date: -1} },
{
"$lookup": {
"from": "users",
"localField": "garrage_of_origin_user",
"foreignField": "_id",
"as": "motorDriver"
}
},
{ $project: {
motor_code: 1,
motor_name: 1,
motor_number: 1,
imo_number: 1,
garrage_of_origin: 1,
journey_date: 1,
'sub_origin_garage.garrage_name1': 1,
'sub_origin_garage.garrage_name2': 1,
motorDriver: { fullname: 1, email: 1 }
}
}
])
.then(response => {
res.status(200).json({
status: 'success',
response
});
})
.catch(error => {
res.json({
message: `An error occured!: ${error}`
})
})