Create multidimensional structure from grouped aggregate data from an Eloquent query

I want to create a nested JSON object, something like this:

[
    {
        "age": 8,
        "countAll": 3,
        "gender": [
            {
                "genderName": "male",
                "countGender": 2
            },
            {
                "genderName": "female",
                "countGender": 1
            },
        ]
    },
    {
        "age": 10,
        "countAll": 1,
        "gender": [
            {
                "genderName": "male",
                "countMale": null (or "0")
            },
            {
                "genderName": "female",
                "countFemale": 1
            },
        ]
    },

I have one table in mysql:

| id | name     |  gender  | age | user_id | 
--------------------------------------------
| 1  | nameA    | male     | 8   |    1    |
| 2  | nameB    | female   | 10  |    1    |
| 3  | nameC    | male     | 8   |    1    |
| 4  | nameD    | female   | 8   |    1    |

I am using laravel 10, this is the query I did

$test = Participant::select('age', DB::raw('COUNT(age) as countAge'), DB::raw('COUNT(gender) as  countGender'))->where('user_id', $user_id)->groupBy('age')->orderBy('age', 'ASC')->get();

$output = array();
$currentAge = "";
$currentCount = "";

foreach ($test as $data) {
   if ($data->age != $currentAge) {
      $output[] = array();

      end($output);
      $currentItem = &$output[key($output)];
      $currentAge = $data->age;
      $currentCount = $data->countAge;
      $currentItem['age'] = $currentAge;
      $currentItem['countAll'] = $currentCount;
      $currentItem['gender'] = array();
    }
    $currentItem['gender'][] = array('genderName' => $data->gender, 'countGender' => $data->countGender);
}

and json_encoded result:

[
    {
        "age": 8,
        "countAll": 3,
        "gender": [
            {
                "genderName": null,
                "countGender": 3
            }
        ]
    },
    {
        "age": 10,
        "countAll": 1,
        "gender": [
            {
                "genderName": null,
                "countGender": 1
            }
        ]
    },

I have read a lot of references regarding create nested JSON and made many changes to the SQL query but so far I haven't found a solution.

How can I make this Eloquent query return a nested JSON object like the one I describe above?

I find your desired output structure to be a little unwieldy. It might make better sense to declare the deeper gender-specific data points as simpler associative elements genderCounts => ['male' => 2, 'female' => 1]. In fact, to avoid the convolution of collection method calls on the result set, you should flatten your desired result to a 2d structure so that it can be purely achieved with SQL.

Code: (PHPize Demo)

$user_id = 1;

var_export(
    DB::table('Participant')
    ->select('age')
    ->selectRaw('COUNT(1) total')
    ->selectRaw("SUM(gender = 'male') male")
    ->selectRaw("SUM(gender = 'female') female")
    ->where('user_id', $user_id)
    ->groupBy('age')
    ->orderBy('age', 'ASC')
    ->get()
    ->toArray()
);

to prepare that aggregate data with a Laravel collection method, just chain map() after the get() call. (PHPize Demo)

->map(fn($row) => [
    'age' => $row->age,
    'countAll' => $row->total,
    'gender' => [
        ['genderName' => 'male', 'countMale' => $row->male],
        ['genderName' => 'female', 'countFemale' => $row->female]
    ]
])