PostgreSQL return result set as JSON array

Question

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.

User · Answer

Also if you want selected field from table and aggregated then as 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'}]

User · Answer

TL DR  SELECT json agg t  FROM t   for a JSON array of objects  and   SELECT     json build object           a   json agg t a            b   json agg t b        FROM t   for a JSON object of arrays   List of objects  This section describes how to generate a JSON array of objects  with each row being converted to a single object  The result looks like this      a  1  b   value1     a  2  b   value2     a  3  b   value3      9 3 and up  The json agg function produces this result out of the box  It automatically figures out how to convert its input into JSON and aggregates it into an array   SELECT json agg t  FROM t   There is no jsonb  introduced in 9 4  version of json agg  You can either aggregate the rows into an array and then convert them   SELECT to jsonb array agg t   FROM t   or combine json agg with a cast   SELECT json agg t   jsonb FROM t   My testing suggests that aggregating them into an array first is a little faster  I suspect that this is because the cast has to parse the entire JSON result   9 2  9 2 does not have the json agg or to json functions  so you need to use the older array to json   SELECT array to json array agg t   FROM t   You can optionally include a row to json call in the query   SELECT array to json array agg row to json t    FROM t   This converts each row to a JSON object  aggregates the JSON objects as an array  and then converts the array to a JSON array   I wasn t able to discern any significant performance difference between the two   Object of lists  This section describes how to generate a JSON object  with each key being a column in the table and each value being an array of the values of the column  It s the result that looks like this     a   1 2 3    b    value1   value2   value3      9 5 and up  We can leverage the json build object function   SELECT     json build object           a   json agg t a            b   json agg t b        FROM t   You can also aggregate the columns  creating a single row  and then convert that into an object   SELECT to json r  FROM       SELECT         json agg t a  AS a          json agg t b  AS b     FROM t   r   Note that aliasing the arrays is absolutely required to ensure that the object has the desired names   Which one is clearer is a matter of opinion  If using the json build object function  I highly recommend putting one key value pair on a line to improve readability   You could also use array agg in place of json agg  but my testing indicates that json agg is slightly faster   There is no jsonb version of the json build object function  You can aggregate into a single row and convert   SELECT to jsonb r  FROM       SELECT         array agg t a  AS a          array agg t b  AS b     FROM t   r   Unlike the other queries for this kind of result  array agg seems to be a little faster when using to jsonb  I suspect this is due to overhead parsing and validating the JSON result of json agg   Or you can use an explicit cast   SELECT     json build object           a   json agg t a            b   json agg t b         jsonb FROM t   The to jsonb version allows you to avoid the cast and is faster  according to my testing  again  I suspect this is due to overhead of parsing and validating the result   9 4 and 9 3  The json build object function was new to 9 5  so you have to aggregate and convert to an object in previous versions   SELECT to json r  FROM       SELECT         json agg t a  AS a          json agg t b  AS b     FROM t   r   or  SELECT to jsonb r  FROM       SELECT         array agg t a  AS a          array agg t b  AS b     FROM t   r   depending on whether you want json or jsonb    9 3 does not have jsonb    9 2  In 9 2  not even to json exists  You must use row to json   SELECT row to json r  FROM       SELECT         array agg t a  AS a          array agg t b  AS b     FROM t   r   Documentation  Find the documentation for the JSON functions in JSON functions   json agg is on the aggregate functions page   Design  If performance is important  ensure you benchmark your queries against your own schema and data  rather than trust my testing   Whether it s a good design or not really depends on your specific application  In terms of maintainability  I don t see any particular problem  It simplifies your app code and means there s less to maintain in that portion of the app  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   Nulls  Aggregate functions typically give back NULL when they operate over zero rows  If this is a possibility  you might want to use COALESCE to avoid them  A couple of examples   SELECT COALESCE json agg t         json  FROM t   Or  SELECT to jsonb COALESCE array agg t   ARRAY    t     FROM t   Credit to Hannes Landeholm for pointing this out

[json] PostgreSQL return result set as JSON array?

The answer is

Examples related to json

Examples related to postgresql

Tags