MongoDB : Push or merge one lookup's result in other lookup's result

Viewed 44

I have 3 collection namely constants, activities, applications with mentioned properties.

Now, querying constants collection with activities and activities with applications with matching Id's. I am getting correct results. But now activity_types are shown at per data level.

But expecting the output should be at per item level inside data whichever is matching with item. Because activities are matching for Item and it should be shown in per item level not at data level. I tried with $push and $group but not getting expected results.

Constants

{
    _id: id
    value : {
        categories: [
            {
                id: 001,
                title: "test 1"
            },
            {
                id: 002,
                title: "test 2"
            },
            {
                id: 003,
                title: "test 3"
            }
        ]
    }
}

Activity

{
    propert1: "",
    propert2: "",
    config: {
        agenda_item_category_ids: [ObjectId(001), ObjectId(002)]
    },
    activity_type_id: ObjectId(123)

}
{
    propert1: "",
    propert2: "",
    activity_type_id: ObjectId(456)
    config: {
        agenda_item_category_ids: [ObjectId(002)]
    }

}

applications

{
    _id: ObjectId(123),
    prop1: "",
    prop2: ""
}
{
    _id: ObjectId(456),
    prop1: "",
    prop2: ""
}

Current query

const results = await Constants.aggregate([
    {
        $match: query,
    },
    {
        $unwind: {
            path: '$value.categories',
            preserveNullAndEmptyArrays: true,
        },
    },
    {
        $lookup: {
            from: 'activity',
            localField: 'value.categories.id',
            foreignField: 'config.agenda_item_category_ids',
            as: 'data',
        },
    },
    {
        $lookup: {
            from: 'applications',
            localField: 'items.activity_type_id',
            foreignField: '_id',
            as: 'activity_type',
        },
    },
    {
        $project: {
            _id: 0,
            category_id: '$value.categories.id',
            title: '$value.categories.title',
            description: '$value.categories.description',
            icon_src: '$value.categories.icon_src',
            data: 1,
            activity_type: 1,
        },
    },
]);

Current output

[
    {
        data: [
            {item1},
            {item2}
        ],
        activity_type,
        title
        _id
    },
    {
        data: [
            {item1},
            {item2}
        ],
        activity_type,
        title
        _id
    }
]

Expected output

[
    {
        data: [
            {
                item1,
                activity_type
            },
            {
                item2,
                activity_type
            }
        ],
        title
        _id
    },
]

Tried method

{
  "_id": "$_id",
  "activity_type": {
    "$push": "$activity_type"
  }
}
0 Answers
Related