title: CFML/JS Creating nested JSON/Array from plain SQL
body: <p>I would like to build a tree structre from a plain json array. 
  The regular depth is approx. 6/7 (max 10) and has about 5,000 records.
  My input json looks like this</p>
  
  <pre><code>[3,"01","GruppenAnfangHook",1,0,1,0,"Installationsmaterial",1.0,"",null,null,0.0,-1.0,null,803.0300,803.0300,0.00000,1,1]
  [5,"01.001","JumboAnfangHook",3,0,3,0,"MBS Wandler 1.000",6.0,"St",null,null,0.0,-6.0,0.0000,336.7800,56.1300,0.00000,2,2], 
  [38,"","ArtikelHook",3,5,3,0,"ASK 61.4 1000/5A 5VA Kl.1 Preis lt. Hr. K am 16.05.17",6.0,"stk",6.0,6.0,0.0,-6.0,null,21.5000,21.5000,0.00000,3,3]
  </code></pre>
  
  <p>But I need it structured with childrens like that</p>
  
  <pre><code> {"0":34,1":"02.003",2":"JumboBegin","3":26,"4":0, "5":26,"6":0, "children":[
       {  "0":36,"1":"", "2":"Article","3":26,"4":34,"5":26,6:"0", 7: "Artikel"},
       {  "0":35,"1":"", "2":"JumboEnd",3":26,"4":34, "5":26, 6:"0",7:"Stunde"}
   ]}
  </code></pre>
  
  <p>My best approach so far was to build the child-structure with the following JS function in the frontend</p>
  
  <pre><code>function nest(data, parentId = 0) {
  return data.reduce((r, e) =&gt; {
      let obj = Object.assign({}, e)
      if (parentId == e[4]) {
          let children = nest(data, e[0])
          if (children.length) obj.children = children
          r.push(obj)
      }
    return r;
  }, [])}
  </code></pre>
  
  <p>It works well and fast (&lt; 1s) with a small (&lt;500) amount of records but my browser begins to freeze at 2,000 and above. </p>
  
  <p>My thought was it is too much data and so I tried to solve it in the CFML backend.
  Due to I'm new with recursion, Ben Nadels Blog helped me alot, so I used his post about recursion and created a working <a href="https://trycf.com/scratch-pad/gist/b08b964d924444cbe52b1303ce6c15ee" rel="nofollow noreferrer">example with sample data</a>. </p>
  
  <pre><code>q = queryNew("id,grpCol,jumCol,leiCol,name,typ,order");
  </code></pre>
  
  <p>The grpCol is level 0, up to 5 groups can be placed in each other, in those groups can be placed two kinds of containers (jumCol and leiCol), they can be placed in each other to, but not in themselfs. </p>
  
  <p>But now I am failing to convert it to a array of structures with child members. The structure of the HTML tree generated as output in the example is exactly what I want for my frontend JSON.
  Because of the recursion I don't get, how to store it in an array outside of the function. 
  My goal is a final return as serzializeJson(array).</p>