clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130805 fiddles created (16558 in the last week).

CREATE TABLE tbl (id int, value_two text, value_three text, value_four text); INSERT INTO tbl VALUES (1, 'a', 'A', 'AA'), (2, 'a', 'A2', 'AA2'), (3, 'b', 'A3', 'AA3'), (4, 'a', 'A4', 'AA4'), (5, 'b', 'A5', 'AA5'); CREATE TYPE foo AS (value_three text, value_four text);
5 rows affected
 hidden batch(es)


-- Postgres 9.4+: SELECT value_two, json_agg(json_build_object('value_three', value_three , 'value_four' , value_four)) AS value_four FROM tbl GROUP BY value_two;
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"}]
 hidden batch(es)


-- Postgres 9.2+ SELECT value_two , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four FROM tbl GROUP BY value_two;
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"}]
 hidden batch(es)


-- Or, without the well-known type: SELECT value_two , json_agg(row_to_json((SELECT t FROM (SELECT value_three, value_four) t))) AS value_four FROM tbl GROUP BY value_two;
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"}]
 hidden batch(es)