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
},
]
},
| 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]
]
])