Convert PostgreSQL table to structured JSON output

Question

I have been having a horrible time dumping this example output into a neatly structured JSON output.

Here is some example data

CREATE TEMP TABLE isuck (path text[], city text);
INSERT INTO isuck(path, city)
VALUES
 (ARRAY['Planet Earth','North America','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');

I would like to be able to dump this output to JSON in the following format.

{
 "United States": {
 "Eastern": {
 "New York": [
 "New York City",
 "Manhattan"
 ]
 },
 "Central": {
 "Missouri": [
 "St Louis",
 "Wentzville",
 "Kansas City"
 ],
 "Illinois": [
 "Chicago"
 ],
 "Texas": [
 "Houston",
 "Dallas",
 "San Antonio"
 ]
 }
 },
 "Mexico": {
 "Jalisco": [
 "Puerto Vallarta",
 "Guadalajara"
 ],
 "Baja California Sur": [
 "Cabo San Lucas"
 ]
 }
}

Here is where I have gotten to.

SELECT jsonb_build_object(path[1], jsonb_build_object(path[2], jsonb_build_object(path[3], jsonb_agg(city))))
FROM isuck
GROUP BY path[1],
 path[2],
 path[3]

But this fails with the error

argument 1: key must not be null

I know it is because cities in Mexico are only 2 levels deep whereas cities in the US are 3 levels deep.

Thanks so much for helping steer me in the right direction!

@Erwin,

This is brilliant! One thing is that in my example table, it had a max of 3 levels deep, whereas my real data actually has up to 5. I added two additional sections and it appears to work well but I fear that it might now be the most efficient?

 SELECT path1, jsonb_object_agg(path2, val) AS val FROM (
 SELECT path1, path2, jsonb_object_agg(path3, val) AS val FROM (
 SELECT path[1] AS path1, path[2] AS path2, path[3] AS path3, jsonb_object_agg(path[4], val) AS val
 FROM cte
 WHERE i = 4
 GROUP BY 1, 2, 3
 ) four_1
 GROUP BY 1, 2
 ) four_2
 GROUP BY 1
 UNION ALL 
 SELECT path1, jsonb_object_agg(path2, val) AS val FROM (
 SELECT path[1] AS path1, path[2] AS path2, jsonb_object_agg(path[3], val) AS val
 FROM cte
 WHERE i = 3
 GROUP BY 1, 2
 ) three_1
 GROUP BY 1
 UNION ALL

• mustaccio: May be you can use coalesce to handle nulls? Something like coalesce(path[3],path[2]) instead of just path[3].
• McNets: Please tag your PostgreSQL version. 1-Can you modify actual data? 2-Are you trying to get a single row with all the json text?
• Erwin Brandstetter: I assume the result should just read "Texas": ["Houston"]. "Dallas" and "San Antonio" are not in the table.

Answer 1

For the three levels you show in your sample data:

WITH cte AS (
 SELECT path[0:i-1] AS path, i-1 AS i, json_object_agg(path[i], cities) AS val
 FROM (
 SELECT path, cardinality(path) AS i, json_agg(city) AS cities
 FROM isuck
 GROUP BY 1
 ) sub
 GROUP BY 1, 2
 )
SELECT json_object_agg(key, val) AS result
FROM (
 SELECT path[1] AS key, json_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;

Produces the desired result.

db<>fiddle here - with prettified jsonb

For details on the used JSON functions read the manual here.

Related:

• Greater than or equal to ALL() and equal to MAX() speed

• How to turn a set of flat trees into a single tree with multiple leaves?