Question 41852 in PHP Question posted in
Official manual can be found here.

How to group by name of key name in object and sum its value in php

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
}
Tags:

2

Answers


  1. 0

    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"}}}])
    Login or Signup to reply.
  2. 0

    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));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search