how do I get nested pojo result from php mysql

Question

How to get nested pojo result like this one,

object
------object
------object
------------object
------array

This is I got from somewhere I forgot, but it has no code for make this result.

{
  "success": true,
  
  "counter": {
    "pending": 100,
    "rejected": 200,
    "completed": 300,
    "expired": 400,
    "total": 3200
  },
  "pie_statistics": {
    "assigned": 120,
    "opened": 212,
    "in-progress": 100,
    "completed": 320,
    "done": 433,
    "rejected": 111,
    "expired": 332
  },
  
  "bar_months":[
  "jan",
  "feb",
  "mar"
],
  
  "bar_pending":[
    100,
    200,
    300
  ],
  
   "bar_rejected":[
    140,
    220,
    340
  ],
  
  
   "bar_completed":[
    170,
    290,
    310
  ]
  
}

with my code like this

<?php 

require "connection.php";

$query =    "SELECT a.kondisi, k.area, COUNT(k.area) AS carea, COUNT(k.nrp) AS cnrp
            FROM tb_absens AS a
            INNER JOIN tb_karyawans AS k 
            ON a.nrp = k.nrp
            WHERE a.kondisi = 'Sehat' 
            GROUP BY k.area";

$data = mysqli_query($conn, $query);
$json_array = array();

while($row = mysqli_fetch_array($data)){
    array_push($json_array, array(
        "success"   =>  true,
        "cnrp"      =>  $row["cnrp"],
        "area"      =>  $row["area"],
        "kondisi"   =>  $row["kondisi"],
    ));
}

echo json_encode($json_array);

the result of mine like this

[
{
    "success": true,
    "cnrp": "1",
    "area": "Administrator",
    "kondisi": "Sehat"
},
{
    "success": true,
    "cnrp": "2",
    "area": "AMK CPBP",
    "kondisi": "Sehat"
},..
]

Yeah I know it's somekind old question, with many of questions similiar to. But I have no clue at all. I appreciate any help. Thanks.

Answer 1

If you want to get different json type into single json, you can split query first, and you execute each of query. To get nested object result like this

"counter": {
    "pending": 100,
    "rejected": 200,
    "completed": 300,
    "expired": 400,
    "total": 3200
  },

try execute

while($row = mysqli_fetch_assoc($data)){

    $json_arr['counter'][$row['a']] = $row['b'];
       
}

for nested array

"bar_months":[
  "jan",
  "feb",
  "mar"
]

use this

 while($row = mysqli_fetch_assoc($data)){
        $json_arr2['bar_months'][] = $row_area['months'];
 }

and to merge it to single json you can use

echo json_encode(array_merge($json_arr, $json_arr2));

Hope it can helps