Facing issues in aggregation lookup. Need lookup result in same structure as defined

user_11994093 0
Viewed 52

Schema structure

 categories: [{
    category: { type: Schema.Types.ObjectId, ref: 'Category' },
    subCategory: [{ type: Schema.Types.ObjectId, ref: 'Category' }]
}]

Query

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

Facing issues in aggregation lookup. Need lookup result in same structure as defined.

express node.js mongodb aggregation
1 Answers

You need to use $unwind for decoding an array, explanation is added in query comment below:

  • unwind your categories array
  • lookup for category with Category collection
  • unwind again because lookup will return an array and unwind will convert into object
db.collection.aggregate([
    { $unwind: "$categories" },
    {   
        $lookup: {
            from: "Category",
            localField: "categories.category",
            foreignField: "_id",
            as: "categories.category"
        }
    },
    { $unwind: "$categories.category" },
  • lookup for subCategory with Category collection
    {   
        $lookup: {
            from: "Category",
            localField: "categories.subCategory",
            foreignField: "_id",
            as: "categories.subCategory"
        }
    },
  • group by _id because without group lookup will multiple documents
  • store $$ROOT in root var, because we need to use it for replace root
  • push categories object that we got from lookup
    {   
        $group: {
            _id: "$_id",
            root: { $mergeObjects: "$$ROOT" },
            categories: { $push: "$categories" }
        }
    },
  • replace new root after merging with root and $$ROOT
    {
        $replaceRoot: {
            newRoot: {
                $mergeObjects: ["$root", "$$ROOT"]
            }
        }
    },
  • remove root var because no longer needed
    {
        $project: { root: 0 }
    }
])

For the explanation purpose i have divided in to parts, you can merge easily as all are in sequence.

Working Playground: https://mongoplayground.net/p/lKd5-MjcT1K

user_89871280
Related