CODEWITHSUNDEEP
jsonsnowflake-cloud-data-platform
Mark G
Mark G

Reputation: 47

Snowflake how to accommodate an unparseable JSON row

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?

Upvotes: 1

Views: 49

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175884

Using TRY_PARSE_JSON:

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.

Upvotes: 3

Related Questions