How do I achieve Json Array in Json Object in MariaDB?

Question

I have a table Named `FAMPRODUCT` and it's somewhat like this.

Select * from FAMPRODUCT;


Pid|Prd_Code|Prd_Name|Prd_SrNo|Prd_Rate|Stock|
  1|    1001|     Pr1|       1|  40.000|   50|
  2|    1001|     Pr1|       2|  50.000|   40|
  3|    1001|     Pr1|       3|  60.000|   30|
  4|    1001|     Pr1|       4|  70.000|   20|
  5|    1002|     Pr2|       1|  10.000|   10|
  6|    1002|     Pr2|       2|  20.000|    5|
  7|    1002|     Pr2|       3|  30.000|    2|
  8|    1003|     Pr3|       2|  90.000|    0|
...

What I want :
JSon out Like this

{
  "Data": {
    "famproduct": [
      {
        "Prd_Code": "1001",
        "Descr": "Pr1",
        "Sr_No": "4",
        "Prd_Rate": "70.000",
        "Clos_Stock": "140",
        "famproductdesc": [
          {
            "Prd_Code": "1001",
            "Descr": "Pr1",
            "Sr_No": "4",
            "Prd_Rate": "70.000",
            "Clos_Stock": "20"
          },
          {
            "Prd_Code": "1001",
            "Descr": "Pr1",
            "Sr_No": "3",
            "Prd_Rate": "60.000",
            "Clos_Stock": "30"
          },
          {
            "Prd_Code": "1001",
            "Descr": "Pr1",
            "Sr_No": "2",
            "Prd_Rate": "50.000",
            "Clos_Stock": "40"
          },
          {
            "Prd_Code": "1001",
            "Descr": "Pr1",
            "Sr_No": "1",
            "Prd_Rate": "40.000",
            "Clos_Stock": "50"
          }
        ]
      },
      {
        "Prd_Code": "1002",
        "Descr": "Pr2",
        "Sr_No": "3",
        "Prd_Rate": "30.000",
        "Clos_Stock": "17",
        "famproductdesc": [
          {
            "Prd_Code": "1002",
            "Descr": "Pr2",
            "Sr_No": "3",
            "Prd_Rate": "30.000",
            "Clos_Stock": "2"
          },
          {
            "Prd_Code": "1002",
            "Descr": "Pr2",
            "Sr_No": "2",
            "Prd_Rate": "20.000",
            "Clos_Stock": "5"
          },
          {
            "Prd_Code": "1002",
            "Descr": "Pr2",
            "Sr_No": "1",
            "Prd_Rate": "10.000",
            "Clos_Stock": "10"
          }
        ]
      },
      {
        "Prd_Code": "1003",
        "Descr": "Pr3",
        "Sr_No": "2",
        "Prd_Rate": "90.000",
        "Clos_Stock": "0",
        "famproductdesc": [
          {
            "Prd_Code": "1003",
            "Descr": "Pr3",
            "Sr_No": "2",
            "Prd_Rate": "90.000",
            "Clos_Stock": "0"
          }
        ]
      }
    ]
  }
}

What I tried :
I successfully created an api which gives this output but there is a catch.
All that takes looping in php code and I fired 2 queries and looped the data.

Following is what I did in php, I fired 2 queries and looped it something like this.

  $checkfor  = false;
        for ($a = 0; $a < count($product); $a++) {
            $final_famproductdesc = array();
            for ($b = 0; $b < count($subproduct); $b++) {
                if ($product[$a]['Prd_Code'] == $subproduct[$b]['Prd_Code']) {
                    $final_famproductdesc[] = $subproduct[$b];
                    $checkfor = true;
                } else {
                    if ($checkfor == true) {
                        $checkfor = false;
                        break;
                    }
                }
            }
            $product[$a]['famproductdesc'] = $final_famproductdesc;
        }
        $final_array["famproduct"] = $product;

My method worked but the issue with this method is the if there is a lot of data then this loop takes a lot of time so I am seeking a faster way.

Another way I am trying is to get Data using JSON_ARRAYAGG and JSON_OBJECT methods but I don't know how to create Json Array inside Json Object.
Also I have to run 2 queries as the main Object is just kind of summary and the rest below that is details.

I am able to achieve the 1st summary part (Json Object Part) through mySQL Query. Need help how do I join the details section below every summary part accordingly.

My SQL Query that I tried :

SELECT JSON_ARRAYAGG(JSON_OBJECT('Prd_Code', Prd_Code, 'Descr', Descr, 'Sr_No', Sr_No, 
'Prd_Rate', Prd_Rate, 
'Clos_Stock', Clos_Stock, 'famproductdesc', 
'')) as Result 
FROM (SELECT T.Prd_Code, T.Descr, MAX(T.Sr_No) as Sr_No, T.Prd_Rate, 
SUM(T.Clos_Stock) as Clos_Stock from FAMPRODUCT T 
WHERE Comp_Year = '2022-2023' and Comp_No = '1' and P_Code = 'OMSU0095'
Group by T.Prd_Code, T.Descr, T.Packing, T.PCS_Cartoon, T.PCS_BOX, T.Prd_Group Order By Prd_Code ASC) as t;