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

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?