How to loop through an array according to a given condition?

Question

PHP

Kind everyone. I'm back with my questions.

I need to iterate over an array according to a certain condition. I access the database and get a response with an array of data:

[
    {
        "id": 222,
        "equipment_id": 141490,
        "datetime": "2021-02-07 23:08:07",
        "distance": 5.48,
        "speed": 0.02
    },
    {
        "id": 223,
        "equipment_id": 141490,
        "datetime": "2021-02-07 23:10:08",
        "distance": 6.09,
        "speed": 0.02
    },
    {
        "id": 224,
        "equipment_id": 141490,
        "datetime": "2021-02-07 23:11:01",
        "distance": 6.36,
        "speed": 0.02
    },
    {
        "id": 418,
        "equipment_id": 141491,
        "datetime": "2021-02-07 22:05:38",
        "distance": 341.65,
        "speed": 0
    },
    {
        "id": 419,
        "equipment_id": 141491,
        "datetime": "2021-02-07 22:06:03",
        "distance": 341.65,
        "speed": 0
    },
    {
        "id": 1065,
        "equipment_id": 141491,
        "datetime": "2021-02-08 04:55:46",
        "distance": 172.12,
        "speed": 1.24
    },
    {
        "id": 1066,
        "equipment_id": 141491,
        "datetime": "2021-02-08 04:56:22",
        "distance": 184.51,
        "speed": 0.72
    },
]

How can I get such a result, how to group by the "equipment_id" field.

What should I read and what examples should I pay attention to?

{
        "equipment_id": 141490,
        "speed": {
            "datetime": "2021-02-07 23:08:07",
            "value": 0.02,
            "distance": 5.48,
        },
    	{
            "datetime": "2021-02-07 23:10:08",
            "value": 0.02,
            "distance": 6.09,
        },
    {
            "datetime": "2021-02-07 23:11:01",
            "value": 0.02,
            "distance": 6.36,
        },

    },
    {
        "equipment_id": 141491,
        "speed": {
            "datetime": "2021-02-07 22:06:03",
            "value": 0,
            "distance": 341.65,
        },
    	{
            "datetime": "2021-02-08 04:55:46",
            "value": 0.02,
            "distance": 172.12,
        },
    {
            "datetime": "2021-02-08 04:56:22",
            "value": 0.72,
            "distance": 184.51,
        },

    },

Answer 1

The classic solution is to loop through the array:

<?php
$query = "select * from equipment";
$stmt = $pdo->prepare($query);
$stmt->execute();
$equipments = $stmt->fetchAll(PDO::FETCH_ASSOC);

$result = array_reduce(
  $equipments,
  function($res, $el) {
    if(!is_array($res[$el["equipment_id"]])) {
      $res[$el["equipment_id"]] = [
        "equipment_id" => 141491,
        "speed" => []
      ];
    }
    array_push(
      $res[$el["equipment_id"]]["speed"], 
      [
          "datetime" => $el["datetime"],
            	"value" => $el["speed"],
            	"distance" => $el["distance"],
      ]
    );
    return $res;
  },
  []
);

var_export(array_values($result));

Run PHP and SQL online
SQL solution:

<?php
$query = "select 
  equipment_id, 
  json_arrayagg(
    json_object(
      'datetime', `datetime`,
      'value', `speed`,
      'distance', `distance`
    )
  ) speed
from equipment
group by equipment_id;";

$stmt = $pdo->prepare($query);
$stmt->execute();
$equipments = $stmt->fetchAll(PDO::FETCH_ASSOC);


var_export($equipments);

Test code here