{
    "name": "MD",
    "children": [{
        "name": "Professional",
        "children": [{
            "name": "Professional Behavours",
            "children": [{
                "name": "Year 1",
                "children": [{
                    "name": "Integrated Medical Sciences 1"
                }, {
                    "name": "Integrated Medical Sciences 2"
                }]
            }, {
                "name": "Year 2",
                "children": [{
                    "name": "Integrated Medical Practice 1",
                    "children": [{
                        "name": "Lecture - CVS"
                    }, {
                        "name": "Lecture - Type 1 Diabetes"
                    }]
                }]
            }, {...

{
    "name": "MD",
    "type": "program",
    "children": [{
        "name": "Professional",
        "type": "theme",
        "children": [{
            "name": "Professional Behavours",
            "type": "strand",
            "children": [{
                "name": "Year 1",
                "type": "strandyear",
                "children": [{
                    "name": "Integrated Medical Sciences 1",
                    "type": "unit"
                }, {
                    "name": "Integrated Medical Sciences 2",
                    "type": "unit"
                }]
            }, {
                "name": "Year 2",
                "type": "strandyear",
                "children": [{
                    "name": "Integrated Medical Practice 1",
                    "type": "learning_event",
                    "children": [{
                        "name": "Lecture - CVS"
                    }, {
                        "name": "Lecture - Type 1 Diabetes",
                        "type": "learning_event"
                    }]
                }]
            }, {...

$query = "SELECT CONCAT('program:', program_pk) AS global_id,
       program_name AS name,
       NULL AS parent_global_id
FROM program
UNION ALL
SELECT CONCAT('theme:', theme_pk) AS global_id,
       theme_name AS name,
       CONCAT('program:', program_fk) AS parent_global_id
FROM theme
UNION ALL
SELECT
       CONCAT('theme:', theme_fk, ',strand:', strand_name) AS global_id,
       strand_name AS name,
       CONCAT('theme:', theme_fk) AS parent_global_id
FROM strand
UNION ALL
SELECT
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name) AS global_id,
       strandyear_name AS name,
       CONCAT('theme:', theme_fk, ',strand:', strand_name) AS parent_global_id
FROM strandyear sy
INNER JOIN strand s ON s.strand_pk = sy.strand_fk

UNION ALL
SELECT
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name) AS global_id,
       unit_name AS name,
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name) AS parent_global_id
FROM unit u
INNER JOIN strandyear sy ON u.strandyear_fk = sy.strandyear_pk
INNER JOIN strand s ON s.strand_pk = sy.strand_fk

UNION ALL
SELECT
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name, ',learning_event:', learning_event_name) AS global_id,
       learning_event_name AS name,
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name) AS parent_global_id
FROM learning_event le
INNER JOIN unit u ON u.unit_pk = le.unit_fk
INNER JOIN strandyear sy ON u.strandyear_fk = sy.strandyear_pk
INNER JOIN strand s ON s.strand_pk = sy.strand_fk";
$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
    $data[$row->global_id] = $row;
}

$roots = array();
foreach ($data as $row) {
    if ($row->parent_global_id === null) {
        $roots[]= $row;
    } else {
        $data[$row->parent_global_id]->children[] = $row;
    }
    unset($row->parent_global_id);
    unset($row->global_id);
}

$json = json_encode($roots);