title: MySQL/PHP - Include a new parameter key/value pair in hierarchical JSON
body: <p>I have a MySQL query which produces hierarchical JSON with some help from PHP.</p>
  <p>What I would like is a new 'parameter' key/value pair <strong>ONLY</strong> where the type = unit. Like <code>&quot;type&quot;:&quot;unit&quot;, &quot;parameter&quot;:&quot;xyz&quot;</code> as in:</p>
  <pre><code>{
      &quot;name&quot;: &quot;PATH5121 Clinical Biochemistry&quot;,
      &quot;column_to_sort_by&quot;: &quot;PATH5121 Clinical Biochemistry&quot;,
      &quot;children&quot;: [
          {
              &quot;name&quot;: &quot;Learning Events - PATH5121&quot;,
              &quot;column_to_sort_by&quot;: null,
              &quot;type&quot;: &quot;unit_group&quot;
          },
          {
              &quot;name&quot;: &quot;Assessments - PATH5121&quot;,
              &quot;column_to_sort_by&quot;: null,
              &quot;type&quot;: &quot;unit_group&quot;
          }
      ],
      &quot;type&quot;: &quot;unit&quot;,
      &quot;parameter&quot;: &quot;xyz&quot;
  }
  </code></pre>
  <p>E.g.: this is the relevant part of the current query:</p>
  <pre><code>...
  UNION ALL
  SELECT 
         CONCAT('year:', u.year_fk, ',unit:', u.unit_full_name) AS global_id,
         u.unit_full_name,
         CONCAT('year:', u.year_fk) AS parent_global_id,
         
         NULL AS column_to_sort_by
  FROM unit u
  UNION ALL
  ...
  </code></pre>
  <p>The PHP section after the query:</p>
  <pre><code>$result = $connection-&gt;query($query);
  $data = array();
  while ($row = $result-&gt;fetch_object()) {
      $data[$row-&gt;global_id] = $row;
  }
  
  $roots = array();
  foreach ($data as $row) {   
      $row-&gt;type = end(explode(&quot;,&quot;,(implode(array_slice(explode ( ':',  $row-&gt;global_id), -2, 1)))));
      if ($row-&gt;parent_global_id === null) {
          $roots[]= $row;
      } else {
          $data[$row-&gt;parent_global_id]-&gt;children[] = $row;
      }
      unset($row-&gt;parent_global_id);
      unset($row-&gt;global_id);
  }
  
  $json = json_encode($roots);
  
  $json = trim($json, '[]');
  
  $fp = fopen('data.json', 'w');
  fwrite($fp, $json);
  fclose($fp);
  </code></pre>
  <p>The PHP adds a key/value pair to the JSON named 'type' for each of the structural parts of the JSON.  'type' get its value from the table name.  Possibly the way to include the new key/value pair above is to do something similar.  Not sure how to do that.</p>
  <p>A snippet from the existing JSON:</p>
  <pre><code>{
      &quot;name&quot;: &quot;PATH5121 Clinical Biochemistry&quot;,
      &quot;column_to_sort_by&quot;: &quot;PATH5121 Clinical Biochemistry&quot;,
      &quot;children&quot;: [
          {
              &quot;name&quot;: &quot;Learning Events - PATH5121&quot;,
              &quot;column_to_sort_by&quot;: null,
              &quot;type&quot;: &quot;unit_group&quot;
          },
          {
              &quot;name&quot;: &quot;Assessments - PATH5121&quot;,
              &quot;column_to_sort_by&quot;: null,
              &quot;type&quot;: &quot;unit_group&quot;
          }
      ],
      &quot;type&quot;: &quot;unit&quot;
  }
  </code></pre>
  <p>The structure of table <code>unit</code> has the following relevant columns:</p>
  <p><code>unit_pk</code> Index for table unit</p>
  <p><code>unit_full_name</code> Currently included in the JSON for table unit.  Eg as in the example above 'PATH5121 Clinical Biochemistry'</p>
  <p><code>development_level</code>. This is the new 'parameter' key/value I want included.</p>
  <p>see <a href="https://www.db-fiddle.com/f/3xy96hwyCcd4n6jSBLFx52/1" rel="nofollow noreferrer">DB Fiddle</a></p>