Separate counting is required for several fields that have been merged from another collection using the mongodb aggregation request

Question

I am trying to use the MongoDB aggregation query to join ($ search) two collections, and then count the various of all unique values ​​in a connected array.

So, my two collections look like this: events -

{ "_id" : "1", "name" : "event1", "objectsIds" : [ "1", "2", "3" ], } 

The objects

 { "_id" : "1", "name" : "object1", "metaDataMap" : { "SOURCE" : ["ABC", "DEF"], "DESTINATION" : ["XYZ", "PDQ"], "TYPE" : [] } }, { "_id" : "2", "name" : "object2", "metaDataMap" : { "SOURCE" : ["RST", "LNE"], "TYPE" : ["text"] } }, { "_id" : "3", "name" : "object3", "metaDataMap" : { "SOURCE" : ["NOP"], "DESTINATION" : ["PHI", "NYC"], "TYPE" : ["video"] } } 

What I want to go out when I make a $ match for the _id = 1 event I want to join the metaDataMap, and then a clear count of all the keys, like this: Counts for the _id = 1 event

 SOURCE : 5 DESTINATION: 4 TYPE: 2 

What I still know:

 db.events.aggregate([ {$match: {"_id" : id}} ,{$lookup: {"from" : "objects", "localField" : "objectsIds", "foreignField" : "_id", "as" : "objectResults"}} ,{$project: {x: {$objectToArray: "$objectResults.metaDataMap"}}} ,{$unwind: "$x"} ,{$match: {"xk": {$ne: "_id"}}} ,{$group: {_id: "$xk", y: {$addToSet: "$xv"}}} ,{$addFields: {size: {"$size":"$y"}} } ]); 

This fails because $ objectResults.metaDataMap is not an array object. Any suggestions on how to solve this or another way to do what I want to do? Also, I do not necessarily know which fields (keys) are in the metaDataMap array. And I do not want to read or include fields that may or may not exist on the Map.

Answer 1

That should do the trick. I tested it on your input set and intentionally added some cheating values, such as NYC , displayed in more than one DESTINATION , to make sure it got a zero (i.e. a distinct counter on request). For pleasure, comment on all stages, and then top-down UNcomment it to see the effect of each stage of the pipeline.

 var id = "1"; c=db.foo.aggregate([ // Find a thing: {$match: {"_id" : id}} // Do the lookup into the objects collection: ,{$lookup: {"from" : "foo2", "localField" : "objectsIds", "foreignField" : "_id", "as" : "objectResults"}} // OK, so we've got a bunch of extra material now. Let's // get down to just the metaDataMap: ,{$project: {x: "$objectResults.metaDataMap"}} ,{$unwind: "$x"} ,{$project: {"_id":0}} // Use $objectToArray to get all the field names dynamically: // Replace the old x with new x (don't need the old one): ,{$project: {x: {$objectToArray: "$x"}}} ,{$unwind: "$x"} // Collect unique field names. Interesting note: the values // here are ARRAYS, not scalars, so $push is creating an // array of arrays: ,{$group: {_id: "$xk", tmp: {$push: "$xv"}}} // Almost there! We have to turn the array of array (of string) // into a single array which we'll subsequently dedupe. We will // overwrite the old tmp with a new one, too: ,{$addFields: {tmp: {$reduce:{ input: "$tmp", initialValue:[], in:{$concatArrays: [ "$$value", "$$this"]} }} }} // Now just unwind and regroup using the addToSet operator // to dedupe the list: ,{$unwind: "$tmp"} ,{$group: {_id: "$_id", uniqueVals: {$addToSet: "$tmp"}}} // Add size for good measure: ,{$addFields: {size: {"$size":"$uniqueVals"}} } ]); 

Answer 2

I managed to create the required result using the following query.

 db.events.aggregate( [ {$match: {"_id" : id}} , {$lookup: { "from" : "objects", "localField" : "objectsIds", "foreignField" : "_id", "as" : "objectResults" }}, {$unwind: "$objectResults"}, {$project:{"A":"$objectResults.metaDataMap"}}, {$unwind: {path: "$A.SOURCE", preserveNullAndEmptyArrays: true}}, {$unwind:{ path: "$A.DESTINATION", preserveNullAndEmptyArrays: true}}, {$unwind:{ path: "$A.TYPE", preserveNullAndEmptyArrays: true}}, {$group:{"_id":"$_id","SOURCE":{$addToSet:"$A.SOURCE"},"DESTINATION":{$addToSet:"$A.DESTINATION"},"TYPE":{$addToSet:"$A.TYPE"}}}, {$addFields: {"SOURCE":{$size:"$SOURCE"},"DESTINATION":{$size:"$DESTINATION"},"TYPE":{$size:"$TYPE"}}}, {$project:{"_id":0}}] ).pretty() 

Updated query for dynamic fields.

 db.events.aggregate([ { $match: {"_id" : id}} , {$lookup: {"from" : "objects","localField" : "objectsIds","foreignField" : "_id","as" : "objectResults"}}, {$unwind: "$objectResults"}, {$project:{"A":"$objectResults.metaDataMap"}}, {$project: {x: {$objectToArray: "$A"}}}, {$unwind: "$x"}, {$match: {"xk": {$ne: "_id"}}}, {$unwind:"$xv"}, {$group: {_id: "$xk", y: {$addToSet: "$xv"}}}, {$project:{"size":{$size:"$y"}}}] ).pretty()