skip to Main Content

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?

2

Answers


  1. Something similar can be done like below:

    $participants = Participant::select('age')
        ->selectRaw('COUNT(*) as countAll')
        ->selectRaw('SUM(CASE WHEN gender = "male" THEN 1 ELSE 0 END) as countMale')
        ->selectRaw('SUM(CASE WHEN gender = "female" THEN 1 ELSE 0 END) as countFemale')
        ->where('user_id', $user_id)
        ->groupBy('age')
        ->orderBy('age', 'ASC')
        ->get();
    
    $result = [];
    
    foreach ($participants as $participant) {
        $genderData = [
            [
                'genderName' => 'male',
                'countGender' => $participant->countMale ?? 0,
            ],
            [
                'genderName' => 'female',
                'countGender' => $participant->countFemale ?? 0,
            ]
        ];
    
        $result[] = [
            'age' => $participant->age,
            'countAll' => $participant->countAll,
            'gender' => $genderData,
        ];
    }
    
    $jsonOutput = json_encode($result);
    
    Login or Signup to reply.
  2. 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]
        ]
    ])
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search