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

Solution

TRY_CAST will return null if cast fails:

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

Comments

[Admin]

Please edit and format this wall of unreadable text.

[joeylearningpython]

figured out, just replace cast with try_cast

[Dain Sundstrom]

I believe the problem isn't null, but is that you have an empty string.

[joeylearningpython]

@ Dain Sundstrom yea,you are right! I have empty string so I put coalesce to transfer those empty string into -999999, but still not working.. so I finally use try_cast, which resolved the problem.