postgresql - query to build up json

Question

Running: PostgreSQL 9.6.2

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:

{
  "cogs":{
     "props1": {
       "value": 100    
      }
  }
}

I'd like to somehow reconstruct a json object via a SQL query if possible. Here is the test data set:

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);

The output I'm looking for is in this format:

{
    "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]
        }
    }
}

Some issues I'm facing:

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.
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.
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.
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...

postgresql - query to build up json

1 Answers