title: postgresql - query to build up json
body: <p>Running: PostgreSQL 9.6.2</p>
  
  <p>I have data stored in a table that is in the form of a key/value pair.  The "key" is actually the path of a json object, each one being a property.  So for example if the key was "cogs","props1","value", then the json object would be like so:</p>
  
  <pre><code>{
    "cogs":{
       "props1": {
         "value": 100    
        }
    }
  }
  </code></pre>
  
  <p>I'd like to somehow reconstruct a json object via a SQL query if possible.  Here is the test data set:</p>
  
  <pre><code>drop table if exists test_table;
  CREATE TABLE test_table
  (
      id serial,
      file_id integer NOT NULL,
      key character varying[],
      value character varying,
      status character varying
  )
  WITH (
      OIDS = FALSE
  )
  TABLESPACE pg_default;
  
  insert into test_table (file_id, key, value, status)
  values (1, '{"cogs","description"}', 'some awesome cog', 'approved');
  insert into test_table (file_id, key, value, status)
  values (1, '{"cogs","display"}', 'Giant Cog', null);
  insert into test_table (file_id, key, value, status)
  values (1, '{"cogs","props1","value"}', '100', 'not verified');
  insert into test_table (file_id, key, value, status)
  values (1, '{"cogs","props1","id"}', 26, 'approved');
  insert into test_table (file_id, key, value, status)
  values (1, '{"cogs","props1","dimensions"}', '{"200", "300"}', null);
  insert into test_table (file_id, key, value, status)
  values (1, '{"cogs","props2","value"}', '200', 'not verified');
  insert into test_table (file_id, key, value, status)
  values (1, '{"cogs","props2","id"}', 27, 'approved');
  insert into test_table (file_id, key, value, status)
  values (1, '{"cogs","props2","dimensions"}', '{"700", "800"}', null);
  
  insert into test_table (file_id, key, value, status)
  values (1, '{"widgets","description"}', 'some awesome widget', 'approved');
  insert into test_table (file_id, key, value, status)
  values (1, '{"widgets","display"}', 'Giant Widget', null);
  insert into test_table (file_id, key, value, status)
  values (1, '{"widgets","props1","value"}', '100', 'not verified');
  insert into test_table (file_id, key, value, status)
  values (1, '{"widgets","props1","id"}', 28, 'approved');
  insert into test_table (file_id, key, value, status)
  values (1, '{"widgets","props1","dimensions"}', '{"200", "300"}', null);
  insert into test_table (file_id, key, value, status)
  values (1, '{"widgets","props2","value"}', '200', 'not verified');
  insert into test_table (file_id, key, value, status)
  values (1, '{"widgets","props2","id"}', 29, 'approved');
  insert into test_table (file_id, key, value, status)
  values (1, '{"widgets","props2","dimensions"}', '{"900", "1000"}', null);
  </code></pre>
  
  <p>The output I'm looking for is in this format:</p>
  
  <pre><code>{
      "cogs": {
          "description": "some awesome cog",
          "display": "Giant Cog",
          "props1": {
              "value": 100,
              "id": 26,
              "dimensions": [200, 300]
          },
          "props2": {
              "value": 200,
              "id": 27,
              "dimensions": [700, 800]
          }
      },
      "widgets": {
          "description": "some awesome widget",
          "display": "Giant Widget",
          "props1": {
              "value": 100,
              "id": 28,
              "dimensions": [200, 300]
          },
          "props2": {
              "value": 200,
              "id": 29,
              "dimensions": [900, 1000]
          }
      }
  }
  </code></pre>
  
  <p>Some issues I'm facing:</p>
  
  <ol>
  <li><p>The "value" column can hold text, numbers, and an array.  For whatever reason, the server-side code using knex.js is storing an array of integers (ie, [100,300]) into postgres as the following format: {"100","300"}.  I need to ensure I extract this out as an array of integers as well.</p></li>
  <li><p>Trying to make this dynamic as possible.  Maybe a recursive procedure to figure out what depth of the "key" path exists.... rather than hard-coding array lookup values.  </p></li>
  <li><p>json_object_agg works well to group together properties into a single object.  However it breaks when hitting a null value.  So if the "key" column has only two values (ie, "cogs","description"), and I attempt to aggregate up an array of length three (ie, "cogs","props1","value"), it will break unless I filter on only arrays of length 3.</p></li>
  <li><p>Preserve the ordering of the input.  @klin solution below is amazing and gets me 95% of the way there.  However I failed to mention to also preserve the ordering...</p></li>
  </ol>