menu

Questions & Answers

$aggregation and $look up in the same collection- mongodb not get child

I have an product option schema where I have chield material and add-on type

Product_options

const productOptionsSchema = new mongoose.Schema({
    category: [{
        value: { type: String },
        display: { type: Number },
        display_order: { type: String },
        categoryImage: { type: String },
        parent_id: { type: mongoose.Schema.Types.ObjectId },
        parent_name: { type: String }
    }],
    material: [{
        value: { type: String },
        display: { type: Number },
        display_order: { type: String },
        price: { type: String }
    }],
    addon_type: [{
        // addon_id: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'AddOnDetails'
        // }
    }],
    design: [{
        // design_id: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'DesignDetails'
        // }
    }]


      }, {
    timestamps: true
        })

const Product_options = mongoose.model('Product_options', productOptionsSchema)

i want to add one more key 'isChildExist' if have any chield of category have parent_id == parent_id . my node js code is

    const cat = await Product_options.aggregate( [
    {
        $unwind: {
            path: "$category",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $lookup: {
            from: 'Product_options.category',
            localField: '_id',
            foreignField: 'parent_id',
            as: 'category.isChildExist'
        }
    },
    {
        "$project": {
            "_id": 0,
            "category": 1,
            
        }
    }
    
    ] );

    res.json({
    msg: 'Success!',
    cat
})

my current response is whenre isChildExist is blank :

{
       "msg": "Success!",
        "cat": [
         {
            "category": {
                "value": "Rudraksha",
                "display": 1,
                "display_order": "",
                "categoryImage": "",
                "parent_id": null,
                "parent_name": null,
                "_id": "63aeac69f969cf78856b7db9",
                "isChildExist": []
            }
        },
        {
            "category": {
                "value": "Bead Bracelet",
                "display": 1,
                "display_order": "",
                "categoryImage": "",
                "parent_id": null,
                "parent_name": null,
                "_id": "63aeae05f969cf78856b80b2",
                "isChildExist": []
            }
        },
        {
            "category": {
                "value": "Gemstones",
                "display": 1,
                "display_order": "1",
                "categoryImage": "",
                "parent_id": null,
                "parent_name": null,
                "_id": "63aeb968f969cf78856b8cd9",
                "isChildExist": []
            }
        },
        {
            "category": {
                "value": "Natural Yellow Sapphire - Pukhraj ",
                "display": 1,
                "display_order": "1",
                "categoryImage": "",
                "parent_id": "63aeb968f969cf78856b8cd9",
                "parent_name": "Gemstones",
                "_id": "63aec39bf969cf78856bb9c3",
                "isChildExist": []
            }
        },
        {
            "category": {
                "value": "Natural Blue Sapphire - Neelam",
                "display": 1,
                "display_order": "1",
                "categoryImage": "",
                "parent_id": "63aeb968f969cf78856b8cd9",
                "parent_name": "Gemstones",
                "_id": "63aebe42f969cf78856b9fae",
                "isChildExist": []
            }
        },
        {
            "category": {
                "value": "Natural Ruby -  Manik ",
                "display": 1,
                "display_order": "",
                "categoryImage": "",
                "parent_id": "63aeb968f969cf78856b8cd9",
                "parent_name": "Gemstones",
                "_id": "63aebe54f969cf78856b9fd9",
                "isChildExist": []
            }
        },
        {
            "category": {
                "value": "Natural Hessonite - Gomed",
                "display": 1,
                "display_order": "",
                "categoryImage": "",
                "parent_id": "63aeb968f969cf78856b8cd9",
                "parent_name": "Gemstones",
                "_id": "63aebe83f969cf78856ba076",
                "isChildExist": []
            }
        },
        {
            "category": {
                "value": "Natural Coral - Moonga ",
                "display": 1,
                "display_order": "",
                "categoryImage": "",
                "parent_id": "63aeb968f969cf78856b8cd9",
                "parent_name": "Gemstones",
                "_id": "63aebe9ff969cf78856ba0a8",
                "isChildExist": []
            }
        },
        {
            "category": {
                "value": "Natural Emerald - Panna ",
                "display": 1,
                "display_order": "",
                "categoryImage": "",
                "parent_id": "63aeb968f969cf78856b8cd9",
                "parent_name": "Gemstones",
                "_id": "63aebeb5f969cf78856ba0de",
                "isChildExist": []
            }
        }
        ]
        }

i expect isChildExist have data in gemstone but current is blank array

{
    "category": {
        "value": "Gemstones",
        "display": 1,
        "display_order": "1",
        "categoryImage": "",
        "parent_id": null,
        "parent_name": null,
        "_id": "63aeb968f969cf78856b8cd9",
        "isChildExist": [
    {
        "value": "Natural Yellow Sapphire - Pukhraj ",
        "display": 1,
        "display_order": "1",
        "categoryImage": "",
        "parent_id": "63aeb968f969cf78856b8cd9",
        "parent_name": "Gemstones",
        "_id": "63aec39bf969cf78856bb9c3",
        "child": []
    },{
        "value": "Natural Blue Sapphire - Neelam",
        "display": 1,
        "display_order": "1",
        "categoryImage": "",
        "parent_id": "63aeb968f969cf78856b8cd9",
        "parent_name": "Gemstones",
        "_id": "63aebe42f969cf78856b9fae",
        "child": []
    }
    ]
    }
},
Comments:
2023-01-11 09:20:47
Here you better use a group instead of a lookup. Since it is in the same collection you have all kinds of data available in that pipeline. So, just group your data and put in a single place with addFilelds or project. mongodb.com/docs/manual/reference/operator/aggregation/group
2023-01-11 09:20:47
without lookup is it possible to get the child category of a parent? how I can do this? any example?
Answers(1) :

Problem is in $lookup

Try this code:

const cat = await Product_options.aggregate([{
    $unwind: {
      path: "$category",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $lookup: {
      from: 'Product_options',
      let: {
        category_id: "$category._id"
      },
      pipeline: [{
          $match: {
            $expr: {
              $and: [{
                $eq: ["$category.parent_id", "$$category_id"]
              }, ]
            }
          }
        },
        {
          $project: {
            _id: 0,
            category: 1,
          }
        }
      ],
      as: "category.isChildExist"
    }
  },
  {
    $project: {
      _id: 0,
      category: 1
    }
  }
]);