How create json format with group-concat mysql | How to put JSON into a column data if sub-query returns more than 1 row in MySQL | Optimized way of getting subqueries at once using JSON

How create json format with group-concat mysql |  How to put JSON into a column data if sub-query returns more than 1 row in MySQL | Optimized way of getting subqueries at once using JSON.

A bit convoluted, but you could create JSON objects for each row, concatenate them using GROUP_CONCAT and cast the result (wrapped in [] to make it an array) to JSON;

Below is the full example:

<?php
define("DB_DEFAULT_HOST", "localhost");
define("DB_DEFAULT_DATABASE", "test71");
define("DB_DEFAULT_USER", "root");
define("DB_DEFAULT_PASSWORD", "");

$dbh = null;
try {
    $dbh = new PDO(
        "mysql:host=" . DB_DEFAULT_HOST . ";dbname=" . DB_DEFAULT_DATABASE,
        DB_DEFAULT_USER,
        DB_DEFAULT_PASSWORD
    );
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();

    $query =
        "SELECT
            s.name AS Name, s.roll AS Roll,
            CONCAT(
              '[', COALESCE(
                GROUP_CONCAT(
                  CONCAT('{', 
                    '\"ExamName\":', '\"', HEX(CONVERT(e.subject USING utf32)), '\"',
                    ',\"Score\":', '\"', e.score, '\"',
                  '}')
                  SEPARATOR ','
                ), ''
              ), ']'
            ) AS ExamsRaw
        
        FROM student AS s
          LEFT JOIN exams AS e ON e.student_id=s.id
        
        GROUP BY s.id";

    $students = $dbh->query($query)->fetchAll(PDO::FETCH_OBJ);
    foreach ($students as $entity) {
        $entity->Exams = array();
        foreach (json_decode($entity->ExamsRaw) as $group) {
            $group->ExamName = hexToStr($group->ExamName);
            array_push($entity->Exams, $group);
        }
        unset($entity->ExamsRaw);
    }
    echo "<pre>";
    print_r($students);
    echo "</pre>";
}
catch (\Exception $ex) {
    echo "MySQL_Error=" . $ex->getMessage();
    $dbh->rollBack();
}

function hexToStr($hex)
{
    $string = '';
    for ($index = 0; $index < strlen($hex) - 1; $index += 2) {
        $string .= chr(hexdec($hex[$index] . $hex[$index + 1]));
    }
    return $string;
}

And below is the output of above example code block:

Array
(
    [0] => stdClass Object
        (
            [Name] => Prtiom First Year
            [Roll] => P1
            [Exams] => Array
                (
                    [0] => stdClass Object
                        (
                            [ExamName] => ©|æ|þ|½|±|®|¶
                            [Score] => 1.12
                        )

                    [1] => stdClass Object
                        (
                            [ExamName] => Subject 2   
                            [Score] => 2.54
                        )

                    [2] => stdClass Object
                        (
                            [ExamName] =>  ¡¢£¤¥¦§¨©
                            [Score] => 1.98
                        )

                )

        )

    [1] => stdClass Object
        (
            [Name] => Pritom Second Year
            [Roll] => P2
            [Exams] => Array
                (
                    [0] => stdClass Object
                        (
                            [ExamName] => Subject 2
                            [Score] => 4.00
                        )

                    [1] => stdClass Object
                        (
                            [ExamName] => Subject 3
                            [Score] => 3.00
                        )

                )

        )

)

Aware that mysql group function max length is by default 1024 characters. If your group function more than 1024 then you have to change limit of mysql group function using following query:

This is for current session:
SET SESSION group_concat_max_len = 1000000;

But if you want it global scope, then do following:
SET GLOBAL group_concat_max_len = 1000000;