Refactor the JSON object in Laravel

Question

Here's my JSON:

[
    {
        "ID": 1,
        "SOURCEID": 1,
        "TIMESTAMP": "2020-04-05 07:05:29",
        "VALUE": "30"
    },
    {
        "ID": 4,
        "SOURCEID": 2,
        "TIMESTAMP": "2020-04-05 07:05:17",
        "VALUE": "40"
    },
    {
        "ID": 3,
        "SOURCEID": 1,
        "TIMESTAMP": "2020-04-06 12:04:59",
        "VALUE": "35"
    },
    {
        "ID": 5,
        "SOURCEID": 1,
        "TIMESTAMP": "2020-06-17 12:01:32",
        "VALUE": "1"
    },
    {
        "ID": 6,
        "SOURCEID": 2,
        "TIMESTAMP": "2021-06-17 13:55:29",
        "VALUE": "2"
    }
]

I need to refactor the JSON like

I need JSON to be refactor based on timestamp and source id and JSON is dynamic like a number of source id present in the given JSON there are two ids that is 1 and 2. Below I gave the expected output.

I need a Unique time stamp in a separate array-like

 [2020-04-05,2020-04-06,2020-06-17,2021-06-17]
    { "sourceid: 1, "data":[30,35,1,0], }, { "sourceid": 2, "data":[40,0,0,2], }

Note: The value fills according to the date. Other it should fill as 0.

I have tried like this :

    `$data=json_decode($result);

    $timestamp=[];
    $dataList=[];
    foreach ($data as $value){

        $date=\Carbon\Carbon::parse($value->TIMESTAMP)->toDateString();

        if(!in_array($date, $timestamp, true)){
            array_push($timestamp, $date);
        }
        
        if(isset($dataList[$value->SOURCEID])){
            array_push($dataList[$value->SOURCEID]['data'],$value->VALUE);
        } else{
            $dataList[$value->SOURCEID]=[
                'SOURCEID'=>$value->SOURCEID,
                'data'=>[$value->VALUE]
            ];
        }
    }
    dump($timestamp);
    dump($dataList);`

But it produce like

{ "sourceid: 1, "data":[30,35,1], }, { "sourceid": 2, "data":[40,2]}

but I need like

{ "sourceid: 1, "data":[30,35,1,0], }, { "sourceid": 2, "data":[40,0,0,2] }

You need to get the list dates, and the list of source ids, first, so that you then know how many entries your result arrays need to contain. Then check if an entry exists in your input data for the given date and source - if so, take the value, if not, set 0 instead. — C3roe
– C3roe, Commented Jun 21, 2021 at 7:46

Alex · Accepted Answer · 2021-06-21 09:12:25Z

You need to find the unique timestamps and source ids and then use that to develop your new array. Since you are using laravel, using a collection makes it easier.

I used the transform modifier to modify the TIMESTAMP field so we can "query" it easier via ->where

Please note, I am using the 2nd param true modifier for the json_decode function to get an associative array rather than an object.

$data = json_decode($result, true); // note true

$collection = collect($data);

// transform the timestamp column
$collection->transform(function ($item) {
    $item['TIMESTAMP'] = \Carbon\Carbon::parse($item['TIMESTAMP'])->toDateString();
    return $item;
});

// get all unique timestamps and source ids
$timestamps = $collection->pluck('TIMESTAMP')->unique();
$sourceIds = $collection->pluck('SOURCEID')->unique();

$dataList = [];
foreach ($sourceIds as $sourceId) {
    $items = $collection->where('SOURCEID', $sourceId);
    $dataList[$sourceId]['sourceid'] = $sourceId;
    foreach ($timestamps as $timestamp) {
        $occurrence = $items->where('TIMESTAMP', $timestamp)->first();
        if ($occurrence) {
            $dataList[$sourceId]['data'][] = $occurrence['VALUE'];
        } else {
            $dataList[$sourceId]['data'][] = 0;
        }

    }
}

dd($dataList);

Note you might want to cast $occurrence['VALUE']; to int via (int) $occurrence['VALUE'];

For more information on collections click here.

Output:

Collectives™ on Stack Overflow

Refactor the JSON object in Laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related