Take join array to root object level and whichout lookup alias

Working with Data Node.js Frameworks
, , ,
1

I needed to join two collection which I joined successfully. Since the joined collection documents are shown as sub document array in the output of lookup.
Now, what I want is to bring all he documents on the same root level

My successful query is

db.activities.aggregate([
	{ $match : { userId : "123" } },
	{ $lookup: {
		from: "activities_m",
		let: { emailAddresses: "$emailAddresses", phoneNumbers: "$phoneNumbers" },
		pipeline: [
			{ $match:
				{ $expr:
					{ $or: [
						{ $and: [
							{ $eq: [ "$activityType","SMS" ] },
							{ $eq: [ "$phoneNumbers", "$$phoneNumbers" ] }
						]},
						{ $and: [
							{ $eq: [ "$activityType","Email" ] },
							{ $eq: [ "$emailAddresses", "$$emailAddresses" ] }

						]}
					]}
				}
			}
		],
		"as": "Result"
	}}
]).pretty()

and the output result is

[
/* 1 */
{
    "_id" : {...
    },
    "activityId" : 9996666,
    "activityType" : "Email",
   
    "duration" : 2,
    "emailAddresses" : [ 
        "ewqqw@yu.com"
    ],
    "endTime" : "2021-01-13T09:49:15.783+04:00",
    "intent" : null,
    "owner" : "ewqqw@yu.com",
    "readReceipt" : "",
    "sentimentAnalysis" : "",
   "tokenIds" : null,
    "userId" : "66666",
    "Result" : [ 
        {
            "_id" : {...  },
            "activityId" : 999,
            "activityType" : "Email",
            "duration" : 2,
            "emailAddresses" : [ 
                "ewqqw@yu.com"
            ],
             "intent" : null,
            "owner" : "ewqqw@yu.com",
            "readReceipt" : "",
            "sentimentAnalysis" : "",
            "tokenIds" : null,
            "userId" : "66666"
        }
    ]
}

/* 2 */
{
    "_id" : {... },
   "activityId" : 997,
    "activityType" : "SMS",
   "duration" : 0,
    "intent" : null,
    "owner" : "wer",
    "phoneNumbers" : "886666",
    "readReceipt" : "",
    "sentimentAnalysis" : "",
    "title" : "",
    "tokenIds" : null,
    "userId" : "66666",
    "Result" : [ 
        {
            "_id" : {...
            },
            "activityId" : 9976,
            "activityType" : "SMS",
           "duration" : 0,
           "intent" : null,
            "owner" : "wer",
            "phoneNumbers" : "886666",
            "readReceipt" : "",
            "sentimentAnalysis" : "",
            "title" : "",
            "tokenIds" : null,
            "userId" : "66666"
        }
    ]
}

Now I want
Resultant array at root level as object
all documents in one big document
Like

[
/* 1 */
{
   
    "activityId" : 9996666,
    "activityType" : "Email",
   "deliveryReport" : "",
    "duration" : 2,
    "emailAddresses" : [ 
        "ewqqw@yu.com"
    ],
   "sentimentAnalysis" : "",
    "tokenIds" : null,
    "userId" : "66666",
    }, {
        "activityId" : 999,
        "activityType" : "Email",
       "duration" : 2,
        "emailAddresses" : [ 
            "ewqqw@yu.com"
        ],
        "intent" : null,
        "sentimentAnalysis" : "",
       "tokenIds" : null,
        "userId" : "66666"
    }
}

/* 2 */
{
    "activityId" : 997,
    "activityType" : "SMS",
    "duration" : 0,
    "intent" : null,
    "phoneNumbers" : "886666",
    "sentimentAnalysis" : "",
    "tokenIds" : null,
    "userId" : "66666",
    }, {
        "activityId" : 9976,
        "activityType" : "SMS",
       "duration" : 0,
        "phoneNumbers" : "886666",
        "sentimentAnalysis" : "",
        "tokenIds" : null,
        "userId" : "66666"
    }
}
]

How can I possibly achieve this

2

Hi @MWD_Wajih_N_A ,

To remove the array of values producted by the $lookup stage, an $unwind would need to be used.

This will create one to many documents for each entry in the Result array, which could then be used to rewrite the root document by using $replaceRoot + $mergeObjects as follows:

// setup
db.c1.drop();
db.c1.insert({
    "activityId" : 9996666,
    "activityType" : "Email",
   "deliveryReport" : "",
    "duration" : 2,
    "emailAddresses" : [ 
        "ewqqw@yu.com"
    ],
   "sentimentAnalysis" : "",
    "tokenIds" : null,
    "userId" : "66666",
    });
db.c2.drop();
db.c2.insert({
        "activityId" : 999,
        "activityType" : "Email",
       "duration" : 2,
        "emailAddresses" : [ 
            "ewqqw@yu.com"
        ],
        "intent" : null,
        "sentimentAnalysis" : "",
       "tokenIds" : null,
        "userId" : "66666",
        "somefield": true
    });

// operation
db.c1.aggregate([
	{ $match : { userId : "66666" } },
	{ $lookup: {
		from: "c2",
		let: { emailAddresses: "$emailAddresses", phoneNumbers: "$phoneNumbers" },
		pipeline: [
			{ $match:
				{ $expr:
					{ $or: [
						{ $and: [
							{ $eq: [ "$activityType","SMS" ] },
							{ $eq: [ "$phoneNumbers", "$$phoneNumbers" ] }
						]},
						{ $and: [
							{ $eq: [ "$activityType","Email" ] },
							{ $eq: [ "$emailAddresses", "$$emailAddresses" ] }

						]}
					]}
				}
			},
			{ $project: { _id: 0 } }
		],
		"as": "Result"
	}},
	{ $unwind: "$Result" },
	{ $replaceRoot: { newRoot: { $mergeObjects: [ "$Result", "$$ROOT" ] } } },
	{ $project: { Result: 0 } }
])

Note that as both documents will contain fields with the same name (userId, activityId …), only one value will be retained. In the above example the final document will contain an activityId of 9996666, not 999. Depending on which values should be retained the pipeline may need to be adjusted.