I have some data in mongoDB and i want to group and sum it in object key - value:

{
    '_id': '1',
    'value': {
        A: 1,
        B: 2,
        C: 3
    }
},
{
    '_id': '2',
    'value': {
        B: 2,
        C: 3
    }
}

I need to group by keys name and sum the value of each key - that value. For the example above the result would be:

{
    '_id': 'A',
    'total': 1
},
{
    '_id': 'B',
    'total': 4
},
{
    '_id': 'C',
    'total': 6
}

CodePudding user response：

Query

• if those fields(A,B,C) are unknown, we have to make it so the schema is stable, so we can group by a common field, so object to array is used
• unwind to make the array members separate documents
• group and sum the values

*if your fields are unknown its not good idea, its best the schema to be known, even if some fields can be missing, but not knowing the schema causes many problems while querying

Playmongo

aggregate(
[{"$set": {"value": {"$objectToArray": "$value"}}},
 {"$unwind": "$value"},
 {"$group": {"_id": "$value.k", "count": {"$sum": "$value.v"}}}])

CodePudding user response：

Here's how to do this with PHP, I'm not sure if there's a more efficient way to do it on the DB level. But this does the trick, you might need to wrap your json keys with a quotation first ("A": instead of A:)

<?php

$json_string = '
[{
    "_id": "1",
    "value": {
        "A": 1,
        "B": 2,
        "C": 3
    }
},
{
    "_id": "2",
    "value": {
        "B": 2,
        "C": 3
    }
}]';

function groupByKeys($json_string) {
    // convert json_string to an array
    $json_array = json_decode($json_string, true );
    $result = array();
    foreach ($json_array as $key => $value) {
        foreach ($value['value'] as $key2 => $value2) {
            if (isset($result[$key2])) {
                $result[$key2]  = $value2;
            } else {
                $result[$key2] = $value2;
            }
        }
    }
    return $result;
}

echo json_encode(groupByKeys($json_string));