mongodb aggregating and grouping nested objects

Question

I have a several collections:

User {
 items: [ObjectID(n)]
}

Items {
 _id: ObjectID(n),
 name: '',
 categories: [ObjectID(n)]
}

Categories {
 _id: ObjectID(n),
 name: ''
}

I Use query below to lookup items:

db.Users.aggregate(
    [
        {
            $match: {
                _id: ObjectId("n")
            }
        }, {
            $unwind: "$items"
        }, {
            $lookup: {
                from: "Items",
                localField: "items",
                foreignField: "_id",
                as: "items"
            }
        }, {
            $unwind: "$items"
        }, {
            $group: {
                _id: "$_id",
                items: { $push: "$items" }
            }
        }
    ]
)

Now I populate Users.items but i cannot deal with categories inside items. I Use code below to get categories.

db.Users.aggregate(
    [
        {
            $match: {
                _id: ObjectId("n")
            }
        }, {
            $unwind: "$items"
        }, {
            $lookup: {
                from: "Items",
                localField: "items",
                foreignField: "_id",
                as: "items"
            }
        }, {
            $unwind: "$items"
        }, {
            $unwind: "$items.categories"
        }, {
            $lookup: {
                from: "Categories",
                localField: "items.categories",
                foreignField: "_id",
                as: "items.categories"
            }
        }, {
            $group: {
                _id: "$_id",
                items: { $push: "$items" }
            }
        }
    ]
)

But I cannot perform correct grouping.

Any ideas how to properly group categories inside items?

s7vr · Accepted Answer · 2016-11-02 22:51:12Z

I think you're on track so far. Now you just have to do nested grouping to merge the structure.So you'll need two groups at the end. First group by user id and item id and push the unique users items along with the categories and final grouping by user id and push the item with its categories.

db.User.aggregate(
    [{
        $match: {
            _id: 123
        }
    }, {
        $unwind: "$items"
    }, {
        $lookup: {
            from: "Items",
            localField: "items",
            foreignField: "_id",
            as: "userItems"
        }
    }, {
        $unwind: "$userItems"
    }, {
        $unwind: "$userItems.categories"
    }, {
        $lookup: {
            from: "Categories",
            localField: "userItems.categories",
            foreignField: "_id",
            as: "categoryItems"
        }
    }, {
        $group: {
            _id: {
                id: "$_id",
                itemId: "$userItems._id"
            },
            items: {
                $addToSet: "$items"
            },
            categories: {
                $push: "$categoryItems"
            }
        }
    }, {
        $group: {
            _id: "$_id.id",
            itemCategories: {
                $push: {
                    items: "$items",
                    categories: "$categories"
                }
            }
        }
    }]
);

Sample Output:

{
    "_id": 123,
    "itemCategories": [{
        "items": [456],
        "categories": [
            [{
                "_id": 789
            }],
            [{
                "_id": 890
            }]
        ]
    }, {
        "items": [234],
        "categories": [
            [{
                "_id": 567
            }],
            [{
                "_id": 678
            }]
        ]
    }]
}

Collectives™ on Stack Overflow

mongodb aggregating and grouping nested objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related