Snowflake中OBJECT_CONSTRUCT函数无法正确过滤NULL字段问题

问题背景 #

你使用OBJECT_CONSTRUCT生成JSON时，希望自动移除值为NULL或空的字段，但发现部分字段（如DIF_FLAG）未被过滤——原因是该字段存储的是字符串"NULL"而非Snowflake的原生SQL NULL值，而OBJECT_CONSTRUCT仅会自动排除SQL原生NULL的键值对。

输入JSON：

{"DIFID":122,"DIF_FLAG":"NULL","DIF_TYPE":"asian/white","FOCAL_COUNT":2370,"REFERENCE_COUNT":17304}

期望输出JSON：

{"DIFID":122,"DIF_TYPE":"asian/white","FOCAL_COUNT":2370,"REFERENCE_COUNT":17304}

原查询语句：

select distinct ITEMSTATID,
object_construct( 
    'DIFID',DIFID, 
    'DIF_TYPE',DIF_TYPE, 
    'DIF_FLAG',DIF_FLAG, 
    'FOCAL_COUNT',FOCAL_COUNT::integer, 
    'REFERENCE_COUNT',REFERENCE_COUNT::integer, 
    'DIF_METHOD',DIF_METHOD, 
    'DIF_VALUE',DIF_VALUE
) DIF 
from DEV_IPM.STAGEVAULT.DIF_STATISTICS;

解决方案 #

先将字符串形式的"NULL"、空字符串''等无效值转换为SQL NULL，再调用OBJECT_CONSTRUCT即可自动过滤这些字段：

select distinct ITEMSTATID,
object_construct( 
    'DIFID', DIFID, 
    'DIF_TYPE', DIF_TYPE, 
    'DIF_FLAG', IFF(DIF_FLAG IN ('NULL', ''), NULL, DIF_FLAG),
    'FOCAL_COUNT', IFF(FOCAL_COUNT IN ('NULL', ''), NULL, FOCAL_COUNT::integer),
    'REFERENCE_COUNT', IFF(REFERENCE_COUNT IN ('NULL', ''), NULL, REFERENCE_COUNT::integer),
    'DIF_METHOD', IFF(DIF_METHOD IN ('NULL', ''), NULL, DIF_METHOD),
    'DIF_VALUE', IFF(DIF_VALUE IN ('NULL', ''), NULL, DIF_VALUE)
) DIF 
from DEV_IPM.STAGEVAULT.DIF_STATISTICS;

补充说明 #

• 使用IFF函数判断字段值是否为需要过滤的无效值（字符串"NULL"、空字符串），是则替换为SQL NULL，否则保留原内容
• 如果存在其他需要过滤的无效值（如'NA'），可直接在IN条件中添加
• 对于数值类型转换（如FOCAL_COUNT::integer），先过滤无效字符串能避免转换报错

内容的提问来源于stack exchange，提问作者Nidhi