1

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
3
  • May be you can use coalesce to handle nulls? Something like coalesce(path[3],path[2]) instead of just path[3].
    – mustaccio
    Nov 18 at 18:24
  • 1
    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?
    – McNets
    Nov 18 at 22:26
  • I assume the result should just read "Texas": ["Houston"]. "Dallas" and "San Antonio" are not in the table. Nov 19 at 3:29
2

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:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.