I have a collection (servicebyAff) in MongoDB with the following structure in its documents:
{
"category": [
{
"active": true,
"enabled": true,
"service": [
"5ce520370fc47e2d0edc4411"
],
"menu": [],
"_id": "5dd3576a222f4f265d3657ce",
"name": {
"en": "ACTH",
"es": "ACTH"
},
"description": {
"en": "ACTH",
"es": "ACTH"
},
"parentCategory": [
"5dd096dd60c43279e76e8d08"
],
"updatedBy": "5cd51f9c33d554e479daaee8",
"createdAt": "2019-11-19T02:46:02.080Z",
"updatedAt": "2019-11-19T02:46:02.080Z",
"__v": 0
},
{
"active": true,
"enabled": true,
"service": [
"5ce520370fc47e2d0edc4411"
],
"menu": [],
"_id": "5dd35d02222f4f265d3657e3",
"name": {
"en": "ANA",
"es": "ANA"
},
"description": {
"en": "ANA",
"es": "ANA"
},
"parentCategory": [
"5dd0971260c43279e76e8d09"
],
"updatedBy": "5cd51f9c33d554e479daaee8",
"createdAt": "2019-11-19T03:09:54.050Z",
"updatedAt": "2019-11-19T03:09:54.050Z",
"__v": 0
}
],
"subCategory": [],
"appointmentRequired": false,
"enabled": true,
"atHome": true,
"_id": "6287b77d35a7804b30e47d16",
"affiliate": "61097293913cf4eaacb177b2",
"officePhone": "+50433333333",
"place": {
"loc": {
"type": "Point",
"coordinates": [
-88.0264096,
15.5085874
]
},
"enabled": true,
"virtual": false,
"_id": "6109766f913cf469f6b177ba",
"country": "HND",
"countryName": "Honduras",
"city": "San Pedro Sula 21102",
"name": "Laboratorios CDC",
"createdBy": "5cd51f9c33d554e479daaee8",
"address": "6ta avenida 4ta calle N.O San Pedro Sula Cortes HN, 4 Calle NO, Barrio Guamilito, San Pedro Sula 21102, Sector 1 Nor-Oeste, San Pedro Sula, San Pedro Sula, Cortés Department, Honduras, GX5F+CC San Pedro Sula",
"createdAt": "2021-08-03T17:01:35.775Z",
"updatedAt": "2021-08-03T17:01:35.775Z",
"__v": 0
},
"room": "Laboratorios CDC, Plaza Bulevares",
"floor": 23,
"coverageRadiusInKm": 3,
"service": {
"active": true,
"enabled": true,
"affiliateType": [
"5d242feb3555ec2b76e86bce",
"5d242fc13555ec2b76e86bcd"
],
"isAppointment": false,
"showIsAppointment": false,
"isVirtual": false,
"atHome": true,
"onPlace": false,
"_id": "5ce520370fc47e2d0edc4411",
"name": {
"en": "Laboratory Tests At Home",
"es": "Análisis de Laboratorio Domicilio"
},
"description": {
"en": "Laboratory Tests At Home",
"es": "Análisis de Laboratorio A Domicilio"
},
"createdAt": "2019-05-22T10:11:03.945Z",
"updatedAt": "2021-09-30T20:58:57.714Z",
"__v": 0
},
"price": 0,
"createdAt": "2022-05-20T15:45:01.728Z",
"updatedAt": "2022-05-20T23:33:59.947Z",
"__v": 0
}
As you can see within the "category" array that belongs to a collection called categories, there is an attribute called "parentCategory" which is an array of Ids, these Ids are documents within the categories collection. That is, within parentCategory, Ids that are within the same collection are being called
What am I looking for?
That instead of bringing me the array type:
"parentCategory": [
"5dd096dd60c43279e76e8d08"
]
Go and look for the Id in categories, filter it and group it in parentCategory something like this:
"parentCategory": [
{
"id": "5dd096dd60c43279e76e8d08",
"active": true,
"enabled": true,
"service": [],
"menu": [],
"name": {},
"description": {},
"updatedBy": "5cd51f9c33d554e479daaee8",
"createdAt": "2019-11-17T00: 39: 57.807+00: 00",
"updatedAt": "2020-02-24T16: 36: 40.935+00: 00",
"__v": 0
}
],
At the moment I have something like this:
export const categoriesAtHome = async(req: Request & any, res: Response, next: NextFunction) => {
try {
// ?=idService="xxx"&idCategories=""&enable=true
// {$or: [{service: {ObjectId('5ce520370fc47e2d0edc4411')}}], enabled: true}
const {
affiliateId
} = req.params;
const affiliateObjectId = Types.ObjectId(affiliateId);
// const sort: any = { "createdAt": -1 };
// const categoriesByAtHome = await Category.find({
// service: idServiceObjectId,
// enabled: true,
// }).populate("service").populate("subCategory").populate("menu").sort(sort).lean().exec();
const categoryConditions = {
enabled: true,
active: true
};
let data = await ServicesByAffiliate.aggregate([{
$match: {
affiliate: affiliateObjectId,
enabled: true
}
}, {
$lookup: {
from: "services",
localField: "service",
foreignField: "_id",
as: "service"
}
},
{
$unwind: "$service"
},
{
$lookup: {
from: "categories",
localField: "category",
foreignField: "_id",
as: "category"
}
},
{
$project: {
opened: "$category.parentCategory"
}
}
// {
// $graphLookup: {
// restrictSearchWithMatch: categoryConditions,
// from: "categories",
// startWith: "$_id",
// connectFromField: "_id",
// connectToField: "parentCategory",
// depthField: "depth",
// as: "items"
// }
// }
])
res.status(200).json({
data: data
});
} catch (error) {
res.status(500).json({
error: true,
message: error.toString()
});
}
};
Could anyone help me please? I'm going to put this question in reward in three days
I am working with Nodejs and Moogose
ISSUE
The problem is that you are trying to make a
$lookup
reference using values of different types.The list of
parentCategory
is of type string (string
) and you are trying to compare it with values of typeObjectId
. That is why it returns an empty list where it should return a list of the documentsparentCategory
you are trying to get.You can see the following Mongo Playground as an example.
SOLUTION
The solution is simple, you must convert the values of type string to values of type ObjectId for each element of the list
parentCategory
, in this way the stage$lookup
will return the list with the required information.To convert the values of the list into ObjectId's we will use a stage
$addFields
together with the aggregation operator$map
, which, as its name indicates, performs a "mapping" of the existing elements in a list, returning a version of them to which some function has been applied. operation. The operation that we will apply on each element will be to convert it to ObjectId using the conversion operator$toObjectId
.The stage
$addFields
using$map
and$toObjectId
could look like this:Note the use of the expression
"$$parent"
, which is a MongoDB aggregation variable . In this case it is used to refer to the variableparent
declared in theas
operator field$map
.Depending on how you have your whole aggregation set up, this would be the way to convert each element of
parentCategory
to an ObjectId so that you can then use it in a stage$lookup
. You can see the following Mongo Playground as an example.I hope this helps you solve the problem.