Using JSON_EXTRACT_SCALAR in Trino SQL

Probably a few good ways to do this with Trino, but my mind likes to think in classic wide tables, so the answer below (see https://hafizbadrie.medium.com/prestodb-convert-json-array-of-objects-into-rows-d9c916724dfc for more descriptions of what is going on) should work for you...

-- create 3 rows of the test data 
WITH fake_table AS (
select 'X' AS panel, 
       '[{"name":"x1","user_text":"","user_text_overflow":""},{"name":"y1","user_text":"","user_text_overflow":""},{"name":"z1","user_text":"","user_text_overflow":""}]' AS alert,
       '2024-12-10' AS the_date
UNION
select 'X' AS panel, 
       '[{"name":"x2","user_text":"","user_text_overflow":""},{"name":"x1","user_text":"","user_text_overflow":""},{"name":"z2","user_text":"","user_text_overflow":""}]' AS alert,
       '2024-12-10' AS the_date
UNION
select 'X' AS panel, 
       '[{"name":"x1","user_text":"","user_text_overflow":""},{"name":"x2","user_text":"","user_text_overflow":""},{"name":"x3","user_text":"","user_text_overflow":""}]' AS alert,
       '2024-12-11' AS the_date
),

-- unwind it all to a deep and wide table representation
flattened_out_alerts AS (
select panel, the_date, 
       alert['name'] AS alert_name,
       alert['user_text'] AS alert_user_text,
       alert['user_text_overflow'] AS alert_user_text_overflow
  from (
        select panel, the_date,
               cast(json_extract(alert, '$') AS ARRAY<MAP<VARCHAR,VARCHAR>>) AS alerts
  from fake_table
  )
cross join UNNEST(alerts) AS alerts(alert)
)

-- run a normal group_by query
select the_date, alert_name, count(alert_name) AS the_count
  from flattened_out_alerts
 where alert_name = 'x1'
 group by the_date, alert_name;

It returns the following.