Snowflake how to accommodate an unparseable JSON row

Question

Suppose this query

select gestimate, (get(parse_json(gestimate) :duration,'value')) / 60.0  AS rc_estimate 
from mytable;

blows up because 120M rows in, there is an unparseable row. How to adjust this query so it places a 0 into rc_estimate if it is unparseable?

Lukasz Szozda · Accepted Answer · 2022-12-14 19:22:37Z

3

select gestimate, (get(try_parse_json(gestimate) :duration,'value')) / 60.0  AS rc_estimate 
from mytable;

COALESCE(expr, 0), ZEROIFNULL(expr) could be used to change the result from NULL to 0.

answered Dec 14, 2022 at 19:22

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1