How do I ignore invalid JSON when using json_parse with PrestoDB?

Question

I am fairly new to Presto, and am trying to parse a bunch of records containing JSON data. It appears that some of the data is invalid, which causes Presto to abort the query during the call to json_parse. Is it possible to somehow return NULL instead of throwing an error in this case?

It seems like previously you could use try_cast(value as json), but that was removed in favor of json_parse. Is there any sort of configuration I can change to resolve this, or do I need to resort to creating a custom SerDe?

Answer 1

It looks like json_extract(data, '$') will return NULL for invalid JSON:

presto:default> select json_extract('{', '$');
 _col0 
-------
 NULL  
(1 row)