当 Parse_Json 尝试解析“NaN”时，会给出 invalid 值。值是 pandas 数据帧中的空值

Question

    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 显示无效的 json

在解析雪花中具有 NaN 值的 JSON 时遇到问题。它说无效的 JSON，因此我们无法正确解析或使用 JSON 函数，尤其是在操作时。

目前，我们尝试了

x = 'NaN' then NULL else x end 的情况。

但这只是解决问题的临时方法。有没有其他方法/雪花功能可以帮助我。 ？

注意：我已尝试使用 try_parse_json，但问题仍然相同。

Answer 1

您的 JSON 数据无效，只需检查任何 JSON 验证器，它就会抛出错误，并且行为与屏幕截图中显示的行为相同。您的 json 文档有以下错误

以及修复逗号后的有效 json和 引号。

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;
   

Answer 2

NaN 处理

NaN 是一个有效的双精度值，因此，如果您使用 TRY_TO_DOUBLE 解析它，您会得到正确的值：

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

给出：

COLUMN1	AS_NUMBER	AS_DOUBLE
1	1	1
NaN	null	NaN

但如果您想替换它：

String replacement

您可以采用该字符串形式：

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":"尼克斯", "年龄":15, "地址": { "areaCode":"1234", }, "状态": null , "电影": [ {"名称":"钢铁侠" ,"预算":"1.5 亿美元","制作人":"Avi"}, {"name":"夏洛克Holmes","budget":"$200M"," Producer":"Joel Silver"},]}	NaN	null	TRUE

JSON NaN 处理

try_to_double 可以使用，技巧是 json 值是 varaint，并且 to_double 想要一个 TEXT/ STRING/VARCHAR 输入，因此您需要通过 ::text 将其转换为字符串

并且您可以通过直接比较来测试双精度值与 NaN，文档有关于这是如何的注释 与 IEEE 754 不同行为。

但 NULLIF 函数无法正确处理此问题，但您可以根据您的问题使用 IFF 而不是 CASE。

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