title: postgres How to generate a recursive json string from a normalized, self-referential table
body: <p>I have a self-referencing table like:</p>
  <pre><code>create table foo (
      id int primary key, 
      name varchar(10) not null, 
      parent_id int null references foo (id)
  );
  
  insert into foo values 
      (1, 'a', null), 
      (2, 'b', 1), 
      (3, 'c', 1), 
      (4, 'd', 2), 
      (5, 'e', 3), 
      (6, 'f', 3), 
      (7, 'g', 4)
  ;
  
  select * from foo;
  
  id  | name | parent_id
  1   |  a   | 
  2   |  b   | 1
  3   |  c   | 1
  4   |  d   | 2
  5   |  e   | 3
  6   |  f   | 3
  7   |  g   | 4
  
  </code></pre>
  <p>Given a top level row (where parent_id is null, so in this example <code>where id=1</code>), I would like to generate a recursive json string like this:</p>
  <pre><code>{
      &quot;1&quot;: {
          &quot;id&quot;: 1,
          &quot;name&quot;: &quot;a&quot;,
          &quot;children&quot;: {
              &quot;2&quot;: {
                  &quot;id&quot;: 2,
                  &quot;name&quot;: &quot;b&quot;,
                  &quot;children&quot;: {
                      &quot;4&quot;: {
                          &quot;id&quot;: 4,
                          &quot;name&quot;: &quot;d&quot;,
                          &quot;children&quot;: {
                              &quot;7&quot;: {
                                  &quot;id&quot;: 7,
                                  &quot;name&quot;: &quot;g&quot;,
                                  &quot;children&quot;: {}
                              }
                          }
                      }
                  }
              },
              &quot;3&quot;: {
                  &quot;id&quot;: 3,
                  &quot;name&quot;: &quot;c&quot;,
                  &quot;children&quot;: {
                      &quot;5&quot;: {
                          &quot;id&quot;: 5,
                          &quot;name&quot;: &quot;e&quot;,
                          &quot;children&quot;: {}
                      },
                      &quot;6&quot;: {
                          &quot;id&quot;: 6,
                          &quot;name&quot;: &quot;f&quot;,
                          &quot;children&quot;: {}
                      }
                  }
              }
          }
      }
  }
  </code></pre>