Cannot cast '' to bigint in presto

Question

I tried to clear 'null' in my query, but there is still error when run it, keep telling me that 'cannot cast '' to bigint, is there any way to fix it?

myquery

select m.app, m.hour,m.user_id, 
 m.avg_minutes_watched, n.userid, n.watched_mins, n.active FROM 

(SELECT app,
substr(hour,1,8) hour, 
CAST(COALESCE(json_extract_scalar(json, '$.user_id'), '-999999') as BigInt) user_id,
CAST(COALESCE(json_extract_scalar(json, '$.playback_time'), '-999999') as BigInt) /60000 avg_minutes_watched
FROM prod
WHERE event_type = 'user_session_complete' AND hour > '20180331' and hour < '20180501')m

left join

(select userid, watched/60000 watched_mins,
(case when watched/60000 >= 2 then 'active' else 'not_active' end) active  from est where realdate > '2018-03-31' and realdate < '2018-05-01') n

on m.user_id = n.userid
order by m.hour, m.user_id;

error

Query 20180510_220127_17857_bxg5s, FAILED, 72 nodes
Splits: 5,178 total, 644 done (12.44%)
0:04 [39.2M rows, 1.93GB] [9.32M rows/s, 469MB/s]

Query 20180510_220127_17857_bxg5s failed: Can not cast '' to BIGINT

Answer 1

TRY_CAST will return null if cast fails:

TRY_CAST(json_extract_scalar(json, '$.user_id') as BigInt) user_id