Manipulate JSON

I receive two JSON objects as variables named:

$result['1a'] and $result['2a']

I've run print_r() on each of these and pasted the output into json_source.txt (attached).

The first object is total fees.
The second object is total transactions.

I want to turn these into JSON in this format:

{"value": {"board": [ 
{"name": "undefined", "values": [16, 0]},
{"name": "GBP", "values": [1, 0.1]}, 
{"name": "EUR", "values": [10, 2.37]}
]}}

Open in new window


I.e. take the data from the last 30 days for total fees and total number of transactions and consolidate into a leaderboard.

Bonus points: is there a way to then sort the leaderboard by total number of transactions?
json-source
LVL 6
benwiggyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Are you receiving a JSON string?  If so, please post the actual JSON string.  It's much more useful than the print_r() output!
0
benwiggyAuthor Commented:
No I get the json objects, but if I json_encode those objects I get the attached.
raw-json
0
Ray PaseurCommented:
This isn't working... Is there another way I can get to the original data?
http://www.laprbass.com/RAY_temp_benwiggy.php

<?php // RAY_temp_benwiggy.php

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233410.html#a39473064

error_reporting(E_ALL);
echo '<pre>';

// READ THE JSON STRING
$url = 'http://filedb.experts-exchange.com/incoming/2013/09_w36/676023/raw-json';
$str = file_get_contents($url);
var_dump($str);

// TRY TO MAKE AN OBJECT http://php.net/manual/en/function.json-decode.php
$obj = json_decode($str);
var_dump($obj);

// HOW TO CHECK THE ERRORS IF PHP >= 5.3
// COPIED FROM http://www.php.net/manual/en/function.json-last-error.php
switch (json_last_error())
{
    case JSON_ERROR_NONE:
        echo ' - No errors';
    break;
    case JSON_ERROR_DEPTH:
        echo ' - Maximum stack depth exceeded';
    break;
    case JSON_ERROR_STATE_MISMATCH:
        echo ' - Underflow or the modes mismatch';
    break;
    case JSON_ERROR_CTRL_CHAR:
        echo ' - Unexpected control character found';
    break;
    case JSON_ERROR_SYNTAX:
        echo ' - Syntax error, malformed JSON';
    break;
    case JSON_ERROR_UTF8:
        echo ' - Malformed UTF-8 characters, possibly incorrectly encoded';
    break;
    default:
        echo ' - Unknown error';
    break;
}

Open in new window

Thanks, ~Ray
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

benwiggyAuthor Commented:
Hi Ray,

I suspect the reason it isn't working is because there are two JSON objects there, not just one. Therefore, it isn't valid JSON.

I've uploaded it now as two separate files - please could you retry?

Best,
Ben
raw-json1
raw-json2
0
Ray PaseurCommented:
Yes, I can use those as separate files.  Now I'll see what I can do with the objects.
0
benwiggyAuthor Commented:
Thanks - look forward to seeing what you come up with :)
0
Ray PaseurCommented:
Clarifications, please...

1. Where does the word "board" come from?
2. Where does the value "16" come from?
3. In the "values" arrays, is the first element the count of non-zero values?
4. In the "values" array, is the second element the sum of values?
5. Is the output supposed to combine both inputs into one JSON string or create two separate JSON strings?

{"value": {"board": [ 
{"name": "undefined", "values": [16, 0]},
{"name": "GBP", "values": [1, 0.1]}, 
{"name": "EUR", "values": [10, 2.37]}
]}}

Open in new window

0
benwiggyAuthor Commented:
1. This is 'hard-coded' if you will, it's how the output needs to look
2. 16 = 12 + 4, the sum of all the undefined values during that time period
3. The first element is the sum of all values (from raw-json2)
4. The second element is the sum of all values (from raw-json1)
5. The output is supposed to combine both inputs into the one JSON string, in the format:

{"value": {"board": [ 
{"name": "undefined", "values": [16, 0]},
{"name": "GBP", "values": [1, 0.1]}, 
{"name": "EUR", "values": [10, 2.37]}
]}}

Open in new window


It therefore consolidates the input JSON (which might be 10 or 20 or 30 days of data), into the sum of the values over that entire time period.

Or in other words, the source json shows the fees/day and total transactions/day over a time period, and the output json summarises this to show the total transactions and total fees per currency over the whole time period.

Many thanks for your help, and happy to answer additional questions.
0
Ray PaseurCommented:
Please see http://www.laprbass.com/RAY_temp_benwiggy.php

<?php // RAY_temp_benwiggy.php

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233410.html#a39473064

/* PROBLEM DEFINITION
{"value": {"board": [
{"name": "undefined", "values": [16, 0]},
{"name": "GBP", "values": [1, 0.1]},
{"name": "EUR", "values": [10, 2.37]}
]}}
*/

error_reporting(E_ALL);

// A GATHERING FUNCTION
function gather($obj, $sig)
{
    $sum = 0;
    foreach ($obj->data->values->$sig as $val)
    {
        $sum += $val;
    }
    return $sum;
}

// THE FIELDS WE NEED TO GATHER
$fields = array('undefined', 'GBP', 'EUR');

// READ THE JSON STRINGS
$url = 'http://filedb.experts-exchange.com/incoming/2013/09_w36/676024/raw-json1';
$str = file_get_contents($url);
$fee = json_decode($str);

$url = 'http://filedb.experts-exchange.com/incoming/2013/09_w36/676025/raw-json2';
$str = file_get_contents($url);
$trn = json_decode($str);


// GATHER FEES
$a = array();
foreach ($fields as $f)
{
    $a[$f] = gather($fee, $f);
}

// GATHER TRANSACTIONS
$b = array();
foreach ($fields as $f)
{
    $b[$f] = gather($trn, $f);
}

// COMBINE THE ARRAYS INTO OBJECTS AND THE OBJECTS INTO AN ARRAY
$n = array();
foreach ($a as $key => $val)
{
    $o = new stdClass;
    $o->name = $key;
    $v = array($b[$key], $a[$key]);
    $o->values = $v;
    $n[] = $o;
}

// CREATE THE BOARD OBJECT
$b = new stdClass;
$b->board = $n;

// CREATE THE COMPLETE REPRESENTATION
$c = new stdClass;
$c->value = $b;
echo json_encode($c);

Open in new window

This can probably be sorted by applying usort() to $n near line 61.  You might want to try that yourself or post a new question about how it might be done.  

Best regards, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
benwiggyAuthor Commented:
Hi Ray,

Many thanks for your help - really appreciate it :)

Just one question, in your solution you've hard-coded the 'fields we need to gather', however, there are going to be lots of currencies in the live data set, and so it's best if the script figures them out on-the-fly - (i.e. not hard code) - any idea how we might do that?

Thanks again,
Ben
0
Ray PaseurCommented:
Sure - I think you can build that array by examining $obj->data->values and collecting all of the instances of the keys.   If you use the key as both key and value in the collection process, you can have built-in protection against duplicate keys in the input.

foreach ($fee->data->values as $key => $nothing)
{
    $fields[$key] = $key;
}

Open in new window

0
benwiggyAuthor Commented:
Thanks so much for your help :)
0
Ray PaseurCommented:
Thanks for the points.  It's a well-defined question, and it's always a pleasure to put a little effort into answering those :-)
0
benwiggyAuthor Commented:
any time :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.