Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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

CREATE TABLE

INSERT 0 5

CREATE TYPE

-- 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"}]

SELECT 2

-- 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"}]

SELECT 2

-- 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"}]

SELECT 2

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"}]

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"}]