current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 41 down vote favorite
18

I would like to have PostgreSQL return the result of a query as one JSON array. Given

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 have 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 I am close, but not there really. Should I be looking at other documentation except for 9.15. JSON Functions and Operators?

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.

share|improve this question
    
+1, didn't psql has this feature – Fabricator Jun 3 '14 at 2:44
1  
PG 9.4, now available in beta 1 release, has improved support for JSON, including binary I/O. If you are on a development machine you might want to check it out. – Patrick Jun 3 '14 at 2:57
    
@Patrick: thank you, it does look like json_object() is a new function in 9.4 and I would try something like SELECT json_object(array_agg(t.a),array_agg(t.b)) FROM t , if I had it – engineerX Jun 3 '14 at 4:28
up vote 64 down vote accepted

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 a SQLFiddle: http://sqlfiddle.com/#!15/5860d/11/0. The SQLFiddle results have some weird "Value"/"Type" thing going on in a JSON object and it escapes the result string (which is mapped to "Value"), but that doesn't seem to happen when running it on plain PostgreSQL. It seems to be some quirk of SQLFiddle.

As for whether it's a good design or not really depends on your specific application. In general, benchmarking would be the best way to tell if this works for you in terms of performance. In terms of maintainability, I don't see any particular problem. Quite the opposite. It simplifies your app code and means there's less to maintain, 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 of to not use it would be performance considerations. Don't reinvent the wheel and all.

Edit:

I didn't realize you were looking for queries for both results.

First, for your second result, you can use:

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

The subquery allows you to control the 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

Second, in my digging, I've discovered 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 in place of either 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

share|improve this answer
1  
Thank you for your answer. You inspired me to find the answer to my second question, SELECT row_to_json(row(array_agg(t.a),array_agg(t.b))) FROM t , though the result has "f1" and "f2" as labels instead of a and b. – engineerX Jun 3 '14 at 4:06
    
@engineerX I've expanded my answer. – jpmc26 Jun 3 '14 at 4:29
2  
It can be undesirable in some cases to get NULL back instead of an empty JSON array when the inner select (from t) returns zero rows. This is caused by aggregate functions always returning NULL when the selecting over no rows and solvable by coalesce: array_to_json(coalesce(array_agg(t), array[]::record[])). – Hannes Landeholm Mar 3 '15 at 13:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.