PostgreSQL: How to Generate JSON from GROUP BY ROLLUP data

Question

Please I need some help in aggregating a Group By ROLLUP(...) into nested JSON.

My Query (with the result) is as shown below

---

Query #1

SELECT main, sub, subsub, count(*) FROM test 
GROUP BY ROLLUP(main, sub, subsub)
ORDER BY main, sub, subsub;

Result

| main | sub | subsub | count |
| ---- | --- | ------ | ----- |
| c    | c-1 | c-1-1  | 1     |
| c    | c-1 | c-1-2  | 1     |
| c    | c-1 |        | 2     |
| c    |     |        | 2     |
| d    | d-1 | d-1-1  | 1     |
| d    | d-1 |        | 1     |
| d    |     |        | 1     |
|      |     |        | 3     |

---

But I will like to have its result in json like below

{
  c: {
    'total': 2,
    c-1: {
      'total': 2,
      'c-1-1': 1,
      'c-1-2': 1,
    }
  },

  d: {
    'total': 1,
    'd-1': {
      'total': 1,
      'd-1-1': 1
    }
  }
}

I've tried json_build_object and the likes but I couldn't manipulate the ROLLUP data. Any Help is highly appreciated!

Here's a link to the fiddle (PostgreSQL V10)

Answer 1

You need a hierarchical query:

with totals as (
    select main, sub, subsub, count(*) 
    from test 
    group by rollup(main, sub, subsub)
    order by main, sub, subsub
)

select jsonb_object_agg(main, sub) as main
from (
    select 
        main, 
        jsonb_object_agg(
            coalesce(sub, 'total'), 
            case when sub is null 
                then subsub->'total' 
                else subsub end
        ) as sub
    from (
        select 
            main, sub, 
            jsonb_object_agg(
                coalesce(subsub, 'total'), count
            ) as subsub
        from totals
        group by main, sub
        having main is not null
        ) s
    group by main
    ) m
where main is not null

db-fiddle.

The version without cte:

select jsonb_object_agg(main, sub) as main
from (
    select 
        main, 
        jsonb_object_agg(
            coalesce(sub, 'total'), 
            case when sub is null 
                then subsub->'total' 
                else subsub end
        ) as sub
    from (
        select 
            main, sub, 
            jsonb_object_agg(
                coalesce(subsub, 'total'), 
                count
            ) as subsub
        from (
            select main, sub, subsub, count(*) 
            from test 
            group by rollup(main, sub, subsub)
            ) subsub
        group by main, sub
        having main is not null
        ) sub
    group by main
    ) main
where main is not null