title: Is there a sql only way to turn a hierarchical table into a set of json strings?
body: <p>I have a hierarchical table a simplified version of which might look like this:</p>
  
  <pre><code>id parentid text
  -- -------- ----
  1  null     A
  2  1        Ax
  3  1        Ay
  4  3        Ay2
  5  null     B
  6  5        Bx
  </code></pre>
  
  <p>I want to migrate all the data from this table in json form. The result of the table above should end up looking like:</p>
  
  <pre><code>{
    "text":"A",
    "children":
    [
      {
        "text":"Ax",
        "children":[]
      },
      {
        "text":"Ay",
        "children":
        [
          {
            "text":"Ay2",
            "children":[]
          }
        ]
      }
    ]
  }
  </code></pre>
  
  <p>(next record)</p>
  
  <pre><code>{
    "text":"B",
    "children":
    [
      {
        "text":"Bx",
        "children":[]
      }
    ]
  }
  </code></pre>
  
  <p>The table has several hundred thousand records and I can't really make an assumption as to how deep it recurses as this might change before I finally run it. I'd looked into using WITH to try to concatenate the child records but I'm really struggling. Can this be done with WITH or is there another way?</p>