Count JSON Array in Query Builder

Question

I'm still new with Laravel so here is JSON array in my exam_schedules table for student_id column in SQLyog:

|----------------------------|
|      student_id            |
|------------------- --------|
|  {"id": [34, 35, 28, 36]}  |         
|----------------------------|
|  {"id": [2, 78]}           |         
|----------------------------|
|  {"id": [7, 34]}           |         
|----------------------------|
|  {"id": [8, 28, 20]}       |         
|----------------------------|

and here is a part of the controller:

$columns = [
            'faculties.name AS faculty_name',
            'courses.name AS course_name',
            'st.name AS staff_name',
            'fc.name AS venue',
            \DB::raw('DATE_FORMAT(CAST(exam_schedules.exam_from_at AS DATE), "%d/%m/%Y") AS exam_date'),
            \DB::raw('DATE_FORMAT(exam_schedules.exam_from_at, "%h:%i %p") AS time_start'),
            \DB::raw('DATE_FORMAT(exam_schedules.exam_to_at, "%h:%i %p") AS time_end'),
            'exam_schedules.remarks AS remarks',
            'exam_schedules.student_id AS total_student'
           ];

$schedule_list  = \DB::table('exam_schedules')
                    ->Join('courses', 'courses.id', '=', 'exam_schedules.course_id')
                    ->Join('faculties', 'faculties.id', '=', 'exam_schedules.faculty_id')
                    ->leftJoin('facilities AS fc', 'fc.id', '=', 'exam_schedules.facility_id')
                    ->leftJoin('staffs AS st', 'exam_schedules.staff_id', '=', 'st.id')
                    ->whereNull('exam_schedules.deleted_at')
                    ->whereNotNull('exam_schedules.exam_to_at')
                    ->whereNotNull('exam_schedules.exam_from_at')
                    ->whereNotNull('exam_schedules.exam_to_at');

How do I count the student_id to look like this?:

|-------------------------|
|      Total Students     |
|-------------------------|
|             4           |         
|-------------------------|
|             2           |         
|-------------------------|
|             2           |         
|-------------------------|
|             3           |         
|-------------------------|

Answer 1

You can use mysql function JSON_LENGTH() to count objects of json.

So in your columns array change total_student line :

From :

$columns = [
      'exam_schedules.student_id AS total_student'
     ];

to : ( this will give you counts of element in id)

$columns = [
   \DB::raw("JSON_LENGTH(exam_schedules.student_id,'$.id') as total_students")
  ];

Reference : https://database.guide/json_length-return-the-length-of-a-json-document-in-mysql/