MySQL – How to modify parent/child select query to add more children to existing array/JSON?

I have the following query working OK:

SELECT core_condition AS name, NULL AS parent
FROM condition_theme_lookup
UNION ALL
SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent
FROM theme, condition_theme_lookup
UNION ALL
SELECT strand.strand_name AS name, theme.theme_name AS parent
FROM strand
JOIN theme ON theme.theme_pk = strand.theme_fk

The resulting array, with some PHP, produces the following JSON, which is fine so far, showing the ‘strand’ children of ‘theme’ parents:

{
    "name": "Condition",
    "children": [{
        "name": "Professional",
        "children": [{
            "name": "Professional Behavours"
        }, {
            "name": "Self-Care and Self-Awareness"
        }, {
            "name": "Medical Ethics and Law"
        }]
    }, {
        "name": "Leader",
        "children": [{
            "name": "Teamwork and Leadership"
        }, {
            "name": "Collaborative Practice"
        }, {
            "name": "Health Systems and Careers"
        }]
    }, {
        "name": "Advocate",
        "children": [{
            "name": "Health Advocacy"
        }, {
            "name": "Aboriginal Health"
        }, {
            "name": "Diversity and Inequality"
        }, {
            "name": "Health Promotion"
        }]
    }, {
        "name": "Clinician",
        "children": [{
            "name": "Scientific Knowledge"
        }, {
            "name": "Patient Assessment and Clinical Reasoning"
        }, {
            "name": "Patient Management"
        }, {
            "name": "Patient Perspective"
        }, {
            "name": "Clinical Communication"
        }, {
            "name": "Quality Care"
        }]
    }, {
        "name": "Educator",
        "children": [{
            "name": "Life-Long Learning"
        }, {
            "name": "Mentoring Relationships"
        }, {
            "name": "Patient Education"
        }, {
            "name": "Teaching and Learning"
        }, {
            "name": "Assessment and Evaluation"
        }]
    }, {
        "name": "Scholar",
        "children": [{
            "name": "Research and Biostatistics"
        }, {
            "name": "Evidence-Based Practice"
        }, {
            "name": "Information Literacy"
        }]
    }]
}

I now want to add the same children set: ‘Year 1’, ‘Year 2’, ‘Year 3’ and ‘Year 4’, from table strand.year, to each strand.strand_name parent (e.g. Professional Behaviours, Medical Ethics and Law etc).

I have tried the following modified query:

SELECT core_condition AS name, NULL AS parent
FROM condition_theme_lookup
UNION ALL
SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent
FROM theme, condition_theme_lookup
UNION ALL
SELECT strand.strand_name AS name, theme.theme_name AS parent
FROM strand, theme
UNION ALL
SELECT strand.year AS name, strand.strand_name AS parent
FROM strand
JOIN theme ON theme.theme_pk = strand.theme_fk

But as you can see below, the relationships now are incomplete; the first five nodes have lost their children, and only one strand, Information Literacy, has the Year children.

   {
    "name": null,
    "children": [{
        "name": "Professional"
    }, {
        "name": "Leader"
    }, {
        "name": "Advocate"
    }, {
        "name": "Clinician"
    }, {
        "name": "Educator"
    }, {
        "name": "Scholar",
        "children": [{
            "name": "Professional Behavours"
        }, {
            "name": "Self-Care and Self-Awareness"
        }, {
            "name": "Teamwork and Leadership"
        }, {
            "name": "Collaborative Practice"
        }, {
            "name": "Health Systems and Careers"
        }, {
            "name": "Health Advocacy"
        }, {
            "name": "Aboriginal Health"
        }, {
            "name": "Diversity and Inequality"
        }, {
            "name": "Health Promotion"
        }, {
            "name": "Scientific Knowledge"
        }, {
            "name": "Patient Assessment and Clinical Reasoning"
        }, {
            "name": "Patient Management"
        }, {
            "name": "Patient Perspective"
        }, {
            "name": "Clinical Communication"
        }, {
            "name": "Quality Care"
        }, {
            "name": "Life-Long Learning"
        }, {
            "name": "Mentoring Relationships"
        }, {
            "name": "Patient Education"
        }, {
            "name": "Teaching and Learning"
        }, {
            "name": "Assessment and Evaluation"
        }, {
            "name": "Research and Biostatistics"
        }, {
            "name": "Evidence-Based Practice"
        }, {
            "name": "Information Literacy",
            "children": [{
                "name": "Year 1"
            }, {
                "name": "Year 2"
            }, {
                "name": "Year 3"
            }, {
                "name": "Year 4"
            }]
        }, {
            "name": "Medical Ethics and Law"
        }]
    }]
}

How should the query be changed to show all the relationships as in the first JSON, and add the set of four ‘Year X’ children to each strand?

Required JSON result up to Year children (ignore children of Year x

See fiddle for original query

SQL:

theme.sql

strand.sql

The working PHP/MySQL for the original version of the JSON is:

$condition = $_POST['condition'];

$query = "SELECT core_condition AS name, NULL AS parent
FROM condition_theme_lookup
UNION ALL
SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent
FROM theme, condition_theme_lookup
UNION ALL
SELECT strand.strand_name AS name, theme.theme_name AS parent
FROM strand
JOIN theme ON theme.theme_pk = strand.theme_fk";
$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
     $data[$row->name] = $row;
 }

foreach ($data as $row) {   
    if ($row->name == 'Condition') {
        $row->name = $condition;
    }
    if ($row->parent === null) {
        $roots[]= $row;
    } else {
        $data[$row->parent]->children[] = $row;
    }
    unset($row->parent);
}

$json = json_encode($roots);

Answer

As I wrote in my other answer: “The name should be unique along all tables”. That was an assumption based on sample data from your previous question. But it’s not the case for the strand table. If a name occurs multiple times in the SQL result set, previous rows with same name will be overwritten here:

$data[$row->name] = $row;

because $row->name has the same value. So you need a column as a unique identifier and use that column as index for the $data array. You can not use name because it’s not unique in the strand table. And you can not use the primary keys, because they are not unique along all tables. But you can use a combination of table name (or a unique table alias) and primary key like

CONCAT('condition:', condition_theme_lookup_pk) AS global_id
...
CONCAT('theme:', theme_pk) AS global_id
....
CONCAT('strand:', strand_pk) AS global_id

The parent column should have the same pattern

CONCAT('theme:', theme_fk) AS parent_global_id

The next problem is – How to group the strands by year per theme? The nesting logic doesn’t follow the pattern parentTable <- childTable <- grandChildTable. That would be condition <- theme <- year <- strand. Instead two levels (year and strand name) are in one table. You need to “extract” the years from the strand table with a DISTINCT query as if they were stored in a separate table. The unique identifier should be a combination of theme PK and year. The individual strands should reference those identifiers in the parent column. The final query would be like

SELECT CONCAT('condition:', condition_theme_lookup_pk) AS global_id,
       core_condition AS name,
       NULL AS parent_global_id
FROM condition_theme_lookup
UNION ALL
SELECT CONCAT('theme:', theme_pk) AS global_id,
       theme_name AS name,
       CONCAT('condition:', condition_theme_lookup_pk) AS parent_global_id
FROM theme CROSS JOIN condition_theme_lookup
UNION ALL
SELECT DISTINCT
       CONCAT('theme:', theme_fk, ',year:', strand.year) AS global_id,
       strand.year AS name,
       CONCAT('theme:', theme_fk) AS parent_global_id
FROM strand
UNION ALL
SELECT CONCAT('strand:', strand_pk) AS global_id,
       strand.strand_name AS name,
       CONCAT('theme:', theme_fk, ',year:', strand.year) AS parent_global_id
FROM strand

db-fiddle

The result looks like

global_id           | name                         | parent_global_id
--------------------|------------------------------|---------------------
condition:1         | Condition                    | null
theme:1             | Professional                 | condition:1
theme:2             | Leader                       | condition:1
...
theme:1,year:Year 1 | Year 1                       | theme:1
theme:2,year:Year 1 | Year 1                       | theme:2
...
theme:1,year:Year 2 | Year 2                       | theme:1
theme:2,year:Year 2 | Year 2                       | theme:2
...
strand:1            | Professional Behavours       | theme:1,year:Year 1
strand:2            | Self-Care and Self-Awareness | theme:1,year:Year 1
strand:3            | Teamwork and Leadership      | theme:2,year:Year 1
strand:4            | Collaborative Practice       | theme:2,year:Year 1
...
strand:27           | Teamwork and Leadership      | theme:2,year:Year 2

You see – “Teamwork and Leadership” appears twice. But the two rows have different global_id and different parent_global_id. You can also see how parent_global_id unambiguously references the global_id of a parent row.

The result is basically an adjacency list composed from data in different tables. These schema is quite simple to transform into a nested structure in PHP. The PHP code needs little changes to be adjusted to the new columns:

$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
    $data[$row->global_id] = $row;
}

$roots = [];
foreach ($data as $row) {   
    if ($row->name == 'Condition') {
        $row->name = $condition;
    }
    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);

Notes:

• The result will not be the same as in your link. But I don’t know how a strand row (like “Professional Behavours”) can be parent of other strand rows, without any according information in the data.
• I replaced your comma join with an explicit CROSS JOIN, which makes the intention clearer. The assumption here is that there is only one row in the condition_theme_lookup table. Otherwise you will need a JOIN condition, which is not possible with the given schema.
• You wrote in the comments: “There will be several more child levels in the final JSON”. All levels must follow the same nesting logic, or at least be convertible (like the case with the years). If you have more surprises, the solution might be not appropriate. At some point I would consider to execute one query per level and build the hierarchical structure “bottom-up” (from leafs to root).

MySQL 8 – CTE + JSON support

Using the combination of JSON_OBJECT() function, JSON_ARRAYAGG() aggregate function and common table expressions (CTE), we are now able to get a nested JSON result with multiple nesting levels with a single query:

with years as (
  select 
    theme_fk,
    year,
    json_arrayagg(json_object('name', strand_name)) as children
  from strand
  group by theme_fk, year
), themes as (
  select
    t.theme_pk,
    t.theme_name as name,
    json_arrayagg(json_object('name', year, 'children', children)) as children
  from theme t
  left join years y on y.theme_fk = t.theme_pk
  group by t.theme_pk
)
select json_object(
    'name', c.core_condition,
    'children', json_arrayagg(json_object('name', t.name, 'children', t.children))
  ) as json
from condition_theme_lookup c
cross join themes t
group by c.condition_theme_lookup_pk

db-fiddle

formatted result

Every nesting level is wrapped in its own CTE, which improves the readability. And every level can have its own nesting logic. Since the result is build step by step, it shouldn’t be a big deal to add more levels.

Update

To swap the levels of strands and years in the UNION query, only little changes are required in the last two subqueries:

...
SELECT DISTINCT
       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('strand_year:', strand_pk) AS global_id,
       strand.year AS name,
       CONCAT('theme:', theme_fk, ',strand:', strand_name) AS parent_global_id
FROM strand

db-fiddle

If you need the children of a node to be sorted in a specific way but differently for the levels, I would suggest to add two columns (num_sort and str_sort) to every subquery. For example if you want themes to be sorted by their PK – Add

theme_pk as num_sort, '' as str_sort

If strand should be sorted by name – add

0 as num_sort, strand_name as str_sort

If years should be sorted by value but in a natural way (“Year 10” > “Year 2”)

cast(replace(year, 'Year ', '') as signed) as num_sort, '' as str_sort

Then append ORDER BY num_sort, str_sort to the query.

db-fiddle

You will then need to remove those columns (attributes) from the PHP objects

unset($row->parent_global_id);
unset($row->global_id);
unset($row->num_sort);
unset($row->str_sort);