I have this code in MySQL corresponding to a simple car database

SELECT
"Cost" AS "Metric",
AVG(car_cost) As "Avg",
MIN(car_cost) As "Min",
MAX(car_cost) As "Max",
"Dollars" As "Unit"
from db.car_data

union SELECT
'Mileage',
AVG(car_mileage),
MIN(car_mileage),
MAX(car_mileage),
'Miles'
from db.car_data

That returns this table

But, when recreate this in a MongoDB equivalent I run into an issue

 db.car_data.aggregate([
  {"$group": 
    {
      _id: null,
      Avg: {
        $avg: { "$car_cost" }
      },
      Min: {
        $min: { "$car_cost" }
      },
      Max: {
        $max: { "$car_cost" }
      }
    }
  },
  {"$group": 
    {
      _id: null,
      Avg: {
        $avg: { car_mileage }
      },
      Min: {
        $min: { car_mileage }
      },
      Max: {
        $max: { car_mileage }
      }
    }
  }
]);

Actual Output

[
  {
    "_id": null,
    "Avg": null,
    "Min": null,
    "Max": null
  }
]

Expected Output

[
  {
    "_id": null,
    "Avg": 2345,
    "Min": 5674,
    "Max": 53456
  },
  {
    "_id": null,
    "Avg": 49023,
    "Min": 53,
    "Max": 178003
  }
]

Am I doing something wrong or is there a better way to do this with aggregation in Mongo? They each work in Mongo on their own...

CodePudding user response：

Query

• you have 1 collection in 1 database, and you want 6 accumulators
• you can seperate them in groups of 3 or even in 2 documents to get the exact SQL like result

*we have $facet $unionWith etc but you dont need those, no need to do multiple aggregations, with only 1 you can get all you need.

Test code here

aggregate(
[ {
  "$group" : {
    "_id" : null,
    "avg_cost" : {
      "$avg" : "$car_cost"
    },
    "min_cost" : {
      "$min" : "$car_cost"
    },
    "max_cost" : {
      "$max" : "$car_cost"
    },
    "avg_mileage" : {
      "$avg" : "$car_mileage"
    },
    "min_mileage" : {
      "$min" : "$car_mileage"
    },
    "max_mileage" : {
      "$max" : "$car_mileage"
    }
  }
}, {
  "$set" : {
    "array" : [ {
      "metric" : "cost",
      "avg" : "$avg_cost",
      "min" : "$min_cost",
      "max" : "$max_cost"
    }, {
      "metric" : "mileage",
      "avg" : "$avg_mileage",
      "min" : "$min_mileage",
      "max" : "$max_mileage"
    } ]
  }
}, {
  "$unwind" : {
    "path" : "$array"
  }
}, {
  "$replaceRoot" : {
    "newRoot" : "$array"
  }
} ]
)