I have the following collection called servicebyAff, where the parent collections are referenced

{
    "_id" : ObjectId("53ed7efca75ca1a5248a281a"),
    "category" : {_id, ref: category},
    "service" : {_id, ref: service}
}

I use $lookup for join the collection and the result was this

{
    "_id": ObjectId("5d4fb125ec8bb5af826935f1"),
    "category": {
        "_id": ObjectId("5d4fb125ec8bb5af826935f1"),
        "active": true,
        "enabled": false,
        "name": {
            "en": "General Medicine",
            "other": "xxx"
        },
      "description": {
            "en": "Is a General Medicine",
            "other": "xxx"
        },
        "parentCategory": [
            "5dd095d860c43279e76e8d06"
        ]
    },
    "service":  [
        {
            "_id": "5ce520370fc47e2d0edc4411",
            "active": true,
            "enabled": true,
            "affiliateType": [
                "5d242feb3555ec2b76e86bce"
            ],
            "name": {
                "en": "Laboratory Tests",
                "other": "xxx"
            },
            "description": {
                "en": "Laboratory Tesrs",
                "es": "xxx"
            },
            "createdAt": "2019-05-22T10:11:03.945Z",
            "updatedAt": "2019-12-30T21:55:03.569Z",
            "__v": 0,
            "isAppointment": false,
            "atHome": true
        }
    ]
}

So, from the category (collection) reference I want to group the parents into the children of this collection. In the following way:

{
    "_id": ObjectId("5d4fb125ec8bb5af826935f1"),
    "category": {
        "_id": ObjectId("5d4fb125ec8bb5af826935f1"),
        "active": true,
        "enabled": false,
        "name": {
            "en": "General Medicine",
            "other": "xxx"
        },
        "description": {
            "en": "Is a General Medicine",
            "other": "xxx"
        },
        "parentCategory": {
            "_id": ObjectId("5d4fb125ec8bb5af826935f1"),
            "active": true,
            "enabled": false,
            "name": {
                "en": "Parents of General Medicine",
                "other": "xxx"
            },
            "description": {
                "en": "Parents General Medicine",
                "other": "xxx"
            }
        }
    },
    "service": [
        {
            "_id": "5ce520370fc47e2d0edc4411",
            "active": true,
            "enabled": true,
            "affiliateType": [
                "5d242feb3555ec2b76e86bce"
            ],
            "name": {
                "en": "Laboratory Tests",
                "other": "xxx"
            },
            "description": {
                "en": "Laboratory Tesrs",
                "es": "xxx"
            },
            "createdAt": "2019-05-22T10:11:03.945Z",
            "updatedAt": "2019-12-30T21:55:03.569Z",
            "__v": 0,
            "isAppointment": false,
            "atHome": true
        }
    ]
}

I need a result, that would contain all parent element. That's my code

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
        };

        const 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: {
                _id: { $ifNull: ["$parentCategory", "$category.parentCategory"] },
                parent: '$category',
                parentCategory: { $ifNull: ["$parentCategory", false] }
            }
        },
        // {
        //     $group: {
        //         _id: "$_id",
        //         name: { $min: { $cond: ["$parentCategory", false, "$name"] } },
        //         childCount: {
        //             $sum: {
        //                 $cond: [
        //                     "$parentCategory",
        //                     1,
        //                     0
        //                 ]
        //             }
        //         },
        //         lastContent: { $last: "$description" }
        //     }
        // },


            // _id?: any;
            // // code: any;
            // name: any;
            // parentCategory?: any;
            // description: any;
            // active?: boolean;
            // enabled?: boolean;
            // service: ServiceModel[];
            // updatedBy?: UserModel;
            // menu?: MenuModel[];
            // {
            //     $project: {
            //         _id: { $cond: ['$parentCategory', '$parentCategory', '$category.parentCategory'] },
            //         name: 1,
            //         parent: '$category',
            //         parentCategory: { $ifNull: ['$parentCategory', false] },
            //     },
            // },

            // {
            //     $group: {
            //         _id: "$_id",
            //         parentCategory: { 
            //             $addToSet: {
            //                 $arrayElemAt: [ "$" ]
            //             }
            //          }

            //     }
            // }

            // {
            //     $addField: {
            //         group: {
            //             $filter: {
            //                 input: "$category",
            //                 as: "category",
            //                 cond: { 
            //                     $and: [
            //                         {
            //                             $eq: ["$$parentCategory"]
            //                         }
            //                     ]
            //                 }
            //             }
            //         }
            //     }
            // }

            // {
            //     $lookup: {
            //         from: "categories",
            //         let: { id: "$_id" },
            //         pipeline: [{
            //             $match: {
            //                 ...categoryConditions
            //             }
            //         }],
            //         as: "category"
            //     }
            // },
            // {
            //     $project: {
            //         category: { $arrayElemAt: ["$parentCategory", 0] }
            //     }
            // }



            // {
            //     $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() });
    }

};

Help me plese! I've been trying for almost eight hours, I need help

CodePudding user response：

Just need to add a $lookup for parentCategory at the end of your query,

  {
    $lookup: {
      from: "categories",
      localField: "category.parentCategory",
      foreignField: "_id",
      as: "category.parentCategory"
    }
  }

Out of the question, you can use $first operator instead of $unwind stage in $addFields,

  // $lookup with service
  // $lookup with category
  {
    $addFields: {
      service: { $first: "$service" },
      category: { $first: "$category" }
    }
  },
  // $lookup with category for parent category

The final query added in Playground