CODEWITHSUNDEEP
sqltrinojson-extract
Shivang
Shivang

Reputation: 1

Using JSON_EXTRACT_SCALAR in Trino SQL

panel alert date
X [{"name":"x1","user_text":"","user_text_overflow":""},{"name":"y1","user_text":"","user_text_overflow":""},{"name":"z1","user_text":"","user_text_overflow":""}] 2024-12-10
X [{"name":"x2","user_text":"","user_text_overflow":""},{"name":"x1","user_text":"","user_text_overflow":""},{"name":"z2","user_text":"","user_text_overflow":""}] 2024-12-10
X [{"name":"x1","user_text":"","user_text_overflow":""},{"name":"x2","user_text":"","user_text_overflow":""},{"name":"x3","user_text":"","user_text_overflow":""}] 2024-12-11

Given the table above, I am trying to extract the "name" value when its set to "x1". I have tried using json_extract_scalar but I keep getting NULL values. I also want to get a sum of the number of times "name" is "x1" on a given date

The expected outcome given the example above should be:

panel name = x1 date
X 2 2024-12-10
X 1 2024-12-11

Upvotes: 0

Views: 71

Answers (1)

Lester Martin
Lester Martin

Reputation: 331

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.

the_date alert_name the_count
2024-12-11 x1 1
2024-12-10 x1 2

Upvotes: 0

Related Questions