I have two collections in my database with field names in the documents that are the same. I need to join these collections and then sum the values of the common field names and finally find the average as my output.

This is an example of a document in the first collection

{
    "_id" : ObjectId("63074885ff3acbe0d63d7686"),
    
"year" : "2020",
    
"energy_products" : "Other Energy Products",
    
"sub_products" : "Other Energy Products",
    
"value_ktoe" : "70.4"
},

This is an example of a document in the second collection

{
    "_id" : ObjectId("63074882ff3acbe0d63c391a"),

"year" : "2020",

"energy_products" : "Petroleum Products",

"sub_products" : "Other Petroleum Products",

"value_ktoe" : "10633.7"
},

So I need to join the collections and sum up all the values in the energy_products and the sub_products part and then find the average.

The output needs to look something like this

    /* 1 */
    {
        "_id" : {
            "energy_products" : "Petroleum Products"
        },
        "avg" : 18312.05625
    },

    /* 2 */
    {
        "_id" : {
            "sub_products" : "Jet Fuel Kerosene"
        },
        "avg" : 4253.884375
    },

CodePudding user response：

Perform a $unionWith to "merge" the 2 collections. Perform a simple $group to get the $avg you need.

db.coll1.aggregate([
  {
    "$group": {
      "_id": {
        "energy_products": "$energy_products"
      },
      "avg": {
        "$avg": {
          "$toDouble": "$value_ktoe"
        }
      }
    }
  },
  {
    "$unionWith": {
      "coll": "coll2",
      "pipeline": [
        {
          "$group": {
            "_id": {
              "sub_products": "$sub_products"
            },
            "avg": {
              "$avg": {
                "$toDouble": "$value_ktoe"
              }
            }
          }
        }
      ]
    }
  }
])

Mongo Playground