Return multiple columns of the same row as JSON array of objects

Question

I have the following table MyTable:

 id │ value_two │ value_three │ value_four 
────┼───────────┼─────────────┼────────────
  1 │ a         │ A           │ AA
  2 │ a         │ A2          │ AA2
  3 │ b         │ A3          │ AA3
  4 │ a         │ A4          │ AA4
  5 │ b         │ A5          │ AA5

I want to query an array of objects { value_three, value_four } grouped by value_two. value_two should be present on its own in the result. The result should look like this:

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

It does not matter whether it uses json_agg() or array_agg().

However the best I can do is:

with MyCTE as ( select value_two, value_three, value_four from MyTable ) 
select value_two, json_agg(row_to_json(MyCTE)) value_four 
from MyCTE 
group by value_two;

Which returns:

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_two":"a","value_three":"A","value_four":"AA"}, {"value_two":"a","value_three":"A2","value_four":"AA2"}, {"value_two":"a","value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_two":"b","value_three":"A3","value_four":"AA3"}, {"value_two":"b","value_three":"A5","value_four":"AA5"}]

With an extra value_two key in the objects, which I would like to get rid of. Which SQL (Postgres) query should I use?

Erwin Brandstetter · Accepted Answer · 2021-04-09T01:43:16.863

92

`json_build_object()` in Postgres 9.4 or newer

Or jsonb_build_object() to return jsonb.

SELECT value_two, json_agg(json_build_object('value_three', value_three
                                           , 'value_four' , value_four)) AS value_four
FROM   mytable 
GROUP  BY value_two;

The manual:

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.

For any version (incl. Postgres 9.3)

row_to_json() with a ROW expression would do the trick:

SELECT value_two
     , json_agg(row_to_json((value_three, value_four))) AS value_four
FROM   mytable
GROUP  BY value_two;

But you lose original column names. A cast to a registered row type avoids that. (The row type of a temporary table serves for ad hoc queries, too.)

CREATE TYPE foo AS (value_three text, value_four text);  -- once in the same session

SELECT value_two
     , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
FROM   mytable
GROUP  BY value_two;

Or use a subselect instead of the ROW expression. More verbose, but without type cast:

SELECT value_two
     , json_agg(row_to_json((SELECT t FROM (SELECT value_three, value_four) t))) AS value_four
FROM   mytable
GROUP  BY value_two;

More explanation in Craig's related answer:

PostgreSQL 9.2 row_to_json() with nested joins

db<>fiddle here
_{Old sqlfiddle}

edited Apr 09 '21 at 01:43

answered Oct 21 '14 at 12:36

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks, but this returns the object with automatically generated keys: `{"f1":"a","f2":"AA"}`. How to rename `f1` to `value_three`? – ehmicky Oct 21 '14 at 12:41
@ehmicky: Right, if you want the column names, too, you need to cast the row to a well-known composite type. I'll add some more. – Erwin Brandstetter Oct 21 '14 at 12:42
1

A sub-select, or even OP's cte could be a more convenient way for aliasing the row's column names. http://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins – pozs Oct 21 '14 at 12:52
@pozs: Good point (and link), I added a code variant accordingly. – Erwin Brandstetter Oct 21 '14 at 13:13
2

As a style note, I think it's easier for newer folks when we put the updates at the top of the answer and the original notes below it with a note on what version they're applicable for. So long as you maintain the answer it's good forever, and the advice for older PostgreSQL's becomes less useful as time moves on. – Evan Carroll Apr 21 '17 at 20:35
1

@Evan: I agree. I don't bother to update all my old answers. But since this one needed an update anyway ... – Erwin Brandstetter Apr 22 '17 at 01:56
this worked for my case: `json_agg(to_json(items.*)) as "items"` – ricka Oct 17 '17 at 19:17
@ErwinBrandstetter Is there any way of doing this where the original type of a timestamp column is retained as a timestamp type rather than being converted to a string? If `value_three` in the example above is a timestamp, it gets converted to string in all of the options you suggested and the application needs to convert it back. While `value_two` in the example will be kept as a timestamp. Any way to have `value_three` handled like `value_two`? – user779159 Jan 21 '18 at 10:30
@user779159; No. JSON itself does not support `timestamp` as datatype. See: https://en.wikipedia.org/wiki/JSON#Data_types,_syntax_and_example and https://www.postgresql.org/docs/current/static/datatype-json.html#JSON-TYPE-MAPPING-TABLE. Your options are to store it as string (see: https://stackoverflow.com/a/9576170/939860) or as number (see: https://stackoverflow.com/a/17732668/939860). – Erwin Brandstetter Jan 21 '18 at 14:13
Hello,is there anyway to add DISTINCT in the following query:- json_agg( json_build_object ( 'id', ca.id, 'name',ca.name, 'url',ca.url) ) as authors – Saurabh Chauhan Jan 09 '19 at 13:38

score 0 · Answer 2 · answered Mar 21 '22 at 15:31

to_json with array_agg with composite type

begin;
create table  mytable(
id bigint, value_two text, value_three text, value_four  text);
insert into mytable(id,value_two, value_three,value_four)
values
 ( 1, 'a',       'A',           'AA'),
  (2, 'a'    ,     'A2'  ,       'AA2'),
  (3, 'b'  ,       'A3',         'AA3'),
 ( 4, 'a'   ,      'A4',          'AA4'),
  (5, 'b' ,        'A5',          'AA5');
commit;
create type mytable_type as (value_three text, value_four text);

select value_two,
       to_json( array_agg(row(value_three,value_four)::mytable_type))
from mytable
group by 1;

score 0 · Answer 3 · answered Apr 25 '23 at 09:35

use jsonb_agg and to_jsonb.

SELECT
    value_two,
    jsonb_agg(to_jsonb (t.*) - '{id,value_two}'::text[]) AS data
FROM
    mytable t
GROUP BY
    1
ORDER BY
    1;

based on manual reference

jsonb - text[] → jsonb

Deletes all matching keys or array elements from the left operand.

Return multiple columns of the same row as JSON array of objects

3 Answers3

`json_build_object()` in Postgres 9.4 or newer

For any version (incl. Postgres 9.3)

Linked

Related

Return multiple columns of the same row as JSON array of objects

3 Answers3

json_build_object() in Postgres 9.4 or newer

For any version (incl. Postgres 9.3)

Linked

Related

`json_build_object()` in Postgres 9.4 or newer