title: generate json with column value as json dict key
body: <p>I'm using postgres 9.2, and I'm trying to construct a query that generates json in a particular format. I've gotten pretty close with a simple <code>json_agg</code> expression, but now I'm stuck moving forwards.</p>
  
  <p>I have a simple three-table schema, the definition is:</p>
  
  <pre><code>CREATE TABLE project (
      id      INTEGER PRIMARY KEY,
      name    varchar(128) NOT NULL,
      UNIQUE (name)    
  );
  
  CREATE TABLE test (
      id          INTEGER PRIMARY KEY,
      name        varchar(128) NOT NULL,
      project_id  integer,
      FOREIGN KEY (project_id) REFERENCES project(id),
  );
  
  CREATE TABLE data (
      id              INTEGER PRIMARY KEY,
      date_entered    timestamp with time zone NOT NULL,
      data            json NOT NULL,
      test_id         integer,
      FOREIGN KEY (test_id) REFERENCES test(id)
  );
  </code></pre>
  
  <p>After inserting some data like so:</p>
  
  <pre><code>INSERT INTO project (id, name) VALUES (0, 'my_project');
  INSERT INTO test (id, name, project_id) VALUES (0, 'test0', 0);
  INSERT INTO data (date_entered, data, test_id) VALUES (TIMESTAMP WITH TIME ZONE '2014-04-15T09:34:41.454999 z', '["some", "data"]', 0);
  INSERT INTO test (id, name, project_id) VALUES (1, 'test1', 0);
  INSERT INTO data (date_entered, data, test_id) VALUES (TIMESTAMP WITH TIME ZONE '2014-04-15T09:34:41.454999 z', '["some", "data"]', 1);
  </code></pre>
  
  <p>I'd like to construct a query that returns:</p>
  
  <pre><code>{
    "test0": {
      "first_data": "2014-04-15 09:35:10.394+00",
      "data_points": 1
    },
    "test1": {
      "first_data": "2014-04-15 09:35:10.394+00",
      "data_points": 1
    }
  }
  </code></pre>
  
  <p>The closest I've gotten to this solution is with this query:</p>
  
  <pre><code>SELECT
      json_agg(data) as data
  FROM (
      SELECT
          test.name as test_name,
          min(data.date_entered) as first_data,
          count(data.id) as data_points
      FROM test
      INNER JOIN data on data.test_id = test.id
      INNER JOIN project on test.project_id = project.id
      WHERE project.name = 'my_project'
      GROUP BY test.name
  ) as data;
  </code></pre>
  
  <p>which returns this:</p>
  
  <pre><code>[
    {
      "test_name":"test0",
      "first_data":"2014-04-15 09:34:41.454999+00",
      "data_points":1
    },
    {
      "test_name":"test1",
      "first_data":"2014-04-15 09:34:41.454999+00",
      "data_points":1
    }
  ]
  </code></pre>
  
  <p>I've tried various odd uses of row_to_json and array_to_json, but I can't seem to shift the test_name value to be the key in the outer dictionary. </p>
  
  <p>Is this even possible? Am I abusing postgres' json generation functions?</p>