Parse_Json gives invalid when it try to parse "NaN" values which is null values from pandas datafram

user_7790568 0
Viewed 88
    select
parse_json('{"fullName":"Niks",
      "age":15,
      "address": {
            "areaCode":"1234",
                },
       "state": NaN ,
      "movies": [
          {"name":"Iron Man","budget":"$150M","producer":"Avi"},
          {"name":"Sherlock Holmes","budget":"$200M","producer":"Joel Silver"},]}');

Img show the invalid json

I face an issue when parsing JSON having NaN values in snowflake. it says invalid JSON and so we can not parse or use JSON function properly especially when we manipulate.

currently, we tried with the

case when x = 'NaN' then NULL else x end.

but that is a temporary way to solve it. is there any other way/ snowflake function that helps me out. ?

Note: I have tried with try_parse_json, but still the issue remains the same.

snowflake-cloud-data-platform nan
2 Answers

Your JSON data is invalid and just check any JSON validator , it will throw out error and it is same behaviour what is shown in your screenshot.Your json doc has below errors

enter image description here

and below valid json after fixing comma and quotes.

create
    or replace table vartab (n number(2), v variant);

insert into
    vartab
select
    column1 as n,
    parse_json(column2) as v
from
values
    (
        1,
        '{
   "fullName":"Niks",
   "age":15,
   "address":{
      "areaCode":"1234"
   },
   "state":"NaN",
   "movies":[
      {
         "name":"Iron Man",
         "budget":"$150M",
         "producer":"Avi"
      },
      {
         "name":"Sherlock Holmes",
         "budget":"$200M",
         "producer":"Joel Silver"
      }
   ]
}'
    ) as vals;
Select
    *,
    v:state
from
    vartab;

enter image description here

user_144426640

NaN handling

NaN is a valid double value, thus if you parse it with TRY_TO_DOUBLE you get correct value:

select column1, 
    try_to_number(column1) as as_number, 
    try_to_double(column1) as as_double
from values
    ('1.0'),
    ('NaN');

gives:

COLUMN1 AS_NUMBER AS_DOUBLE
1 1 1
NaN null NaN

but if you are wanting to replace it:

String replacing

you can do that string form:

select
    regexp_replace(column1,'NaN', 'null') as nan_free
    ,parse_json(column1):state as var_state_nan
    ,parse_json(nan_free):state as var_state_normal
    ,is_null_value(var_state_normal) as is_json_null
from values ('{"fullName":"Niks",
      "age":15,
      "address": {
            "areaCode":"1234",
                },
       "state": NaN ,
      "movies": [
          {"name":"Iron Man","budget":"$150M","producer":"Avi"},
          {"name":"Sherlock Holmes","budget":"$200M","producer":"Joel Silver"},]}')
NAN_FREE VAR_STATE_NAN VAR_STATE_NORMAL IS_JSON_NULL
{"fullName":"Niks", "age":15, "address": { "areaCode":"1234", }, "state": null , "movies": [ {"name":"Iron Man","budget":"$150M","producer":"Avi"}, {"name":"Sherlock Holmes","budget":"$200M","producer":"Joel Silver"},]} NaN null TRUE

JSON NaN processing

try_to_double as can be used, the trick is json value are varaint, and to_double wants a TEXT/STRING/VARCHAR input, thus you need to cast it to string via ::text

And you can test for a double against NaN by comparing directly, the doc's have notes about how this is different to IEEE 754 behavior.

But the NULLIF function does not correctly handle this, but you can use IFF instead of CASE as per you question.

select
    parse_json(column1):state as var_state_nan
    ,try_to_double(var_state_nan::text) as double_val
    ,double_val = 'NaN' as is_nan
    ,NULLIF(double_val, 'NaN') as should_work
    ,IFF(double_val = 'NaN', null, double_val) as does_work
from values ('{"fullName":"Niks",
      "age":15,
      "address": {
            "areaCode":"1234",
                },
       "state": NaN ,
      "movies": [
          {"name":"Iron Man","budget":"$150M","producer":"Avi"},
          {"name":"Sherlock Holmes","budget":"$200M","producer":"Joel Silver"},]}')
VAR_STATE_NAN DOUBLE_VAL IS_NAN SHOULD_WORK DOES_WORK
NaN NaN TRUE NaN null
user_439920
Related