clear markdown compare help best fiddles feedback dbanow.uk
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. 2805502 fiddles created (40748 in the last week).

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');
10 rows affected
 hidden batch(es)


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
result
{ "Mexico": { "Jalisco": [ "Puerto Vallarta", "Guadalajara" ], "Baja California": [ "Tijuana" ] }, "United States": { "Central": { "Texas": [ "Houston" ], "Illinois": [ "Chicaco" ], "Missouri": [ "St Louis", "Wenztville", "Kansas City" ] }, "Eastern": { "New York": [ "New York City", "Manhattan" ] } } }
 hidden batch(es)