CREATE TABLE isuck (path text[], city text); INSERT INTO isuck(path, city) VALUES (ARRAY['United States','Eastern','New York'],'New York City'), (ARRAY['United States','Eastern','New York'],'Manhattan'), (ARRAY['United States','Central','Missouri'],'St Louis'), (ARRAY['United States','Central','Missouri'],'Wenztville'), (ARRAY['United States','Central','Missouri'],'Kansas City'), (ARRAY['United States','Central','Illinois'],'Chicaco'), (ARRAY['United States','Central','Texas'],'Houston'), (ARRAY['Mexico','Jalisco'],'Puerto Vallarta'), (ARRAY['Mexico','Jalisco'],'Guadalajara'), (ARRAY['Mexico','Baja California'],'Tijuana');

WITH cte AS ( SELECT path[0:i-1] AS path, i-1 AS i, jsonb_object_agg(path[i], cities) AS val FROM ( SELECT path, cardinality(path) AS i, jsonb_agg(city) AS cities FROM isuck GROUP BY 1 ) sub GROUP BY 1, 2 ) SELECT jsonb_pretty(jsonb_object_agg(key, val)) AS result FROM ( SELECT path[1] AS key, jsonb_object_agg(path[2], val) AS val FROM cte WHERE i = 2 GROUP BY 1 UNION ALL SELECT path[1], val FROM cte WHERE i = 1 ) sub