时间戳

首页 > 解决方案 > How to aggregate over map keys across all the documents in mongodB collection?

java - How to aggregate over map keys across all the documents in mongodB collection?

问题描述

I have data in the following format:

[{
"OrderId": "406-5309498-5972326",
"revenueHeader": {
"Principal": 982.14,
"Product Tax": 117.86,
"Total": 1100
}},
{
"OrderId": "506-5234568-5934567",
"revenueHeader": {
"Principal": 382.54,
"Product Tax": 34.46,
"Shipping charge": 30.5,
"Giftwrap charge": 27.5,
"Total": 234
}}]

How can I sum the revenueHeader map values for the keys across all the documents? Note: "Shipping charge" is not present in the first document but we still want the sum for this key across all the documents. Also, the keys can vary, there is no way to know the name of keys beforehand.

标签： javaarraysmongodbmongodb-queryaggregation-framework

解决方案

You have to use $objectToArray to transform revenueHeader in an array of {k,v} object.

You can then $unwind the array, and group by revenueHeader.k, summing revenueHeader.v . By this way, you never take care of the fields name or presence inside revenueHeader.

Here's the query : 

db.collection.aggregate([
  {
    $project: {
      revenueHeader: {
        $objectToArray: "$revenueHeader"
      }
    }
  },
  {
    $unwind: "$revenueHeader"
  },
  {
    $group: {
      _id: "$revenueHeader.k",
      total: {
        $sum: "$revenueHeader.v"
      }
    }
  }
])

You can test it here

推荐阅读