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

[Dharmik_Soni1 (Dharmik Soni)]

I have 3 collection namely constants, activities, applications with mentioned properties.
Now, Quering 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"
  }
}

[steevej (Steeve Juneau)]

It looks like you might have redacted your data or your query prior before posting your question. The query is inconsistent with your input documents and cannot possibly produce the current output you shared.

You $lookup with localField: 'items.activity_type_id' and no input documents have a field named items and items is neither a projected field or the as: field of a $lookup.

Your ObjectId(xxx) also seems to be redacted, in Constants collection you have id:001 but you seem to use them as ObjectId(xxx). Not that mongosh can use the expression ObjectId(002) but you do not get the same value for all invocation. Taking your 2 Activity documents as published, I get:

{
  propert1: '',
  propert2: '',
  config: {
    agenda_item_category_ids: [
      ObjectId("00000001a582a3f70ad77b7e"),
      ObjectId("00000002a582a3f70ad77b7f")
    ]
  },
  activity_type_id: ObjectId("0000007ba582a3f70ad77b80")
}

{
  propert1: '',
  propert2: '',
  activity_type_id: ObjectId("000001c8a582a3f70ad77b81"),
  config: {
    agenda_item_category_ids: [ ObjectId("00000002a582a3f70ad77b82") ]
  }
}

You are also missing some field separator.

Please verify your query and documents and post corrections that we can use.