Does PostgreSQL return the result as a JSON array?

Question

I would like PostgreSQL to return the result of the query as a single JSON array. Considering,

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

I would like something similar to

 [{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}] 

or

 {"a":[1,2,3], "b":["value1","value2","value3"]} 

(actually it would be more useful to know both). I tried some things like

 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; 

And I feel that I'm close, but really not. Should I look for documentation other than 9.15. JSON functions and operators ?

By the way, I'm not sure about my idea. Is this a common design decision? My thinking is that I could, of course, take the result (for example) from the first of the above three queries and manipulate it a bit in the application before serving it to the client, but if PostgreSQL could directly create the final JSON object, it would be easier , because I still did not include any dependency on any JSON library in my application.

Answer 1

Try this query:

 SELECT array_to_json(array_agg(t)) FROM t 

The result is the following JSON:

 [{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}] 

Here's SQLFiddle: http://sqlfiddle.com/#!15/5860d/11/0 . The SQLFiddle results have some weird thing called "Value" / "Type" that happens in the JSON object and it eludes the result line (which maps to "Value" ), but it doesn't seem to happen when running on a simple PostgreSQL, It looks like it's some kind of SQLFiddle quirk.

As for good design or not depending on your specific application. Overall, benchmarking would be the best way to tell if this works for you in terms of performance. As for maintainability, I do not see any particular problems. On the contrary. This simplifies your application code and means that it is smaller, at least in my opinion. If PG can give you exactly the result you need out of the box, the only reason I can think not to use it is for performance reasons. Do not reinvent the wheel and that’s it.

Edit:

I did not understand that you are looking for queries for both results.

First, for your second result, you can use:

 SELECT row_to_json(r) FROM (SELECT array_agg(ta) AS a , array_agg(tb) AS b FROM t ) r 

The subquery allows you to manage key names in the resulting JSON object. This gives

 {"a":[1,2,3],"b":["value1","value2","value3"]} 

SQLFiddle: http://sqlfiddle.com/#!15/5860d/42/0

Secondly, in my digging, I found a couple of other functions introduced in 9.3 that you should consider:

1) json_agg : this does what you want for your first result out of the box.

 SELECT json_agg(t) FROM t 

SQLFiddle: http://sqlfiddle.com/#!15/5860d/38/0

2) to_json : This can be used instead of array_to_json or row_to_json and gives the same results.

 SELECT to_json(array_agg(t)) FROM t 

SQLFiddle: http://sqlfiddle.com/#!15/5860d/10/0

Answer 2

Also, if you want the selected field to be from the table and aggregated, and then as an array.

 SELECT json_agg(json_build_object('data_a',a, 'data_b',b, )) from t; 

The result will come.

  [{'data_a':1,'data_b':'value1'} {'data_a':2,'data_b':'value2'}]