snowflake json data flatten in existing table fields

I have a snowflake table as below

table name: raw_data

Need to flattern data like below

I tried to use below code.

raw_data

select
   *,
   a.value:by::varchar as by,
   a.value:text::varchar as text,
   a.value:time::varchar as time
from raw_data
, lateral flatten(input => raw_data:transcript) a;

by,text,time values are showing as null, any help?

You can do it using parse_json() as follows :

select t.*,
   a.value:by::varchar as "by",
   a.value:text::varchar as "text",
   a.value:time::varchar as "time"
from raw_data t
, lateral flatten(input => parse_json(t.transcript)) a