title: PostgreSQL return result set as JSON array?
body: <p>I would like to have PostgreSQL return the result of a query as one JSON array. Given</p>
  
  <pre><code>create table t (a int primary key, b text);
  
  insert into t values (1, 'value1');
  insert into t values (2, 'value2');
  insert into t values (3, 'value3');
  </code></pre>
  
  <p>I would like something similar to</p>
  
  <pre><code>[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]
  </code></pre>
  
  <p>or </p>
  
  <pre><code>{"a":[1,2,3], "b":["value1","value2","value3"]}
  </code></pre>
  
  <p>(actually it would be more useful to know both). I have tried some things like</p>
  
  <pre><code>select row_to_json(row) from (select * from t) row;
  select array_agg(row) from (select * from t) row;
  select array_to_string(array_agg(row), '') from (select * from t) row;
  </code></pre>
  
  <p>And I feel I am close, but not there really. Should I be looking at other documentation except for <a href="http://www.postgresql.org/docs/9.3/static/functions-json.html" rel="noreferrer" title="9.15. JSON Functions and Operators">9.15. JSON Functions and Operators</a>?</p>
  
  <p>By the way, I am not sure about my idea. Is this a usual design decision? My thinking is that I could, of course, take the result (for example) of the first of the above 3 queries and manipulate it slightly in the application before serving it to the client, but if PostgreSQL can create the final JSON object directly, it would be simpler, because I still have not included any dependency on any JSON library in my application. </p>