Pivoting AWS Redshift SUPER data

Question

I have a following table structure:

server_id	server_databases
1	[{"name": "mssql", "count": 12},{"name": "postgresql", "count": 1}]
2	[]
3	null

What I want to receive as a result(I want to keep servers 2 and 3 if it would be null or empty object doesn't matter):

server_id	databases
1	{"mssql": 12, "postgresql": 1}
2	null
3	null

I've tried to build json myself

SELECT server_id,
       (
           select '{' || listagg('"' || x.name || '":' || x.count, ',') || '}' as clientdatabases
           from (
                    select cb."name"::varchar as name, sum(cb."count")::int as count from e.server_databases as cb group by name
                ) x
       )
FROM my_table e

But it fails with interestiong error

[XX000] ERROR: Query unsupported due to an internal error. Detail: Unsupported witness case Where: nested_decorrelate_calc_witness_unsupported|calc_witness

It looks like PartiQL supports such cases, but I have no idea how to implement it. I will use UDF for now. But, if you can help me with a "native" solution, it would be amazing.

Update SQL script for case reproduction:

CREATE table my_table(server_id int, server_databases super);

insert into my_table(server_id, server_databases) values (
                                                    1, json_parse('[{"name": "mssql", "count": 12},{"name": "postgresql", "count": 1}]')
                                                   ),
                                                   (2, json_parse('[]')),
                                                   (3, null);
SELECT server_id,
       (
           select '{' || listagg('"' || x.name || '":' || x.count, ',') || '}' as clientdatabases
           from (
                    select cb."name"::varchar as name, sum(cb."count")::int as count from e.server_databases as cb group by name
                ) x
       )
FROM my_table e;

Answer 1

Use ISNULL in count :

SELECT server_id,
       (
           select '{' || listagg('"' || x.name || '":' || x.count, ',') || '}' as clientdatabases
           from (
                    select cb."name"::varchar as name, ISNULL(sum(cb."count")::int,0) as count from e.server_databases as cb group by name
                ) x
       )
FROM my_table e

Answer 2

You can use left join for handling null cases

with b as (select server_id,
                  ('{' || listagg('"' || x.name::text || '":' || x.count::int, ',') || '}') as clientdatabases
           from my_table as e, e.server_databases as x
           group by server_id)
select a.server_id, a.server_databases, json_parse(b.clientdatabases)
from my_table as a
    left join b
        on a.server_id = b.server_id;