query.sql

with tbl2_view as (
	with tbl3_view as (
		with tbl4_view as (
			select * from tbl4
		) 
		select tbl3.*, json_agg(tbl4_view) as tbl4 from tbl3
		inner join tbl4_view on tbl4_view.tbl3_id = tbl3.id
		group by tbl3.id
	)

	select tbl2.*, json_agg(tbl3_view) as tbl3 from tbl2	
	inner join tbl3_view on tbl3_view.tbl2_id = tbl2.id	
	group by tbl2.id
)
select tbl1.*, json_agg(tbl2_view) as tbl2 from tbl1
inner join tbl2_view on tbl2_view.tbl1_id = tbl1.id
group by tbl1.id;

schema.sql

CREATE TABLE tbl1 (
    id serial,
    name text
);


CREATE TABLE tbl2 (
    id serial,
    name text,
    tbl1_id integer
);

CREATE TABLE tbl3 (
    id serial,
    name text,
    tbl2_id integer
);

CREATE TABLE tbl4 (
    id serial,
    name text,
    tbl3_id integer
);


INSERT INTO tbl1 VALUES (1, 'tbl1_foo');

SELECT pg_catalog.setval('tbl1_id_seq', 1, true);


INSERT INTO tbl2 VALUES (1, 'tbl2_foo', 1);
INSERT INTO tbl2 VALUES (2, 'tbl2_bar', 1);
SELECT pg_catalog.setval('tbl2_id_seq', 2, true);

INSERT INTO tbl3 VALUES (1, 'tbl3_foo', 1);
INSERT INTO tbl3 VALUES (2, 'tbl3_bar', 2);
SELECT pg_catalog.setval('tbl3_id_seq', 2, true);

INSERT INTO tbl4 VALUES (1, 'tbl4_foo', 1);
SELECT pg_catalog.setval('tbl4_id_seq', 1, true);

ALTER TABLE ONLY tbl1
    ADD CONSTRAINT tbl1_pkey PRIMARY KEY (id);

ALTER TABLE ONLY tbl2
    ADD CONSTRAINT tbl2_pkey PRIMARY KEY (id);

ALTER TABLE ONLY tbl3
    ADD CONSTRAINT tbl3_pkey PRIMARY KEY (id);

ALTER TABLE ONLY tbl4
    ADD CONSTRAINT tbl4_pkey PRIMARY KEY (id);