keysnowflake-cloud-data-platformexists

Checking if a key exists in a Snowflake variant


Is there a function to check if a key exists in a Snowflake variant field?


Solution

  • You can use IS_NULL_VALUE to see if the key exists. If the key does not exist, the result will be NULL. If the key exists, the result will be TRUE if the value is JSON null or FALSE if there's a non-null JSON value:

    select  parse_json('{hello: NULL, world: 123}') as V,
            V:hello, 
            V:world,
            IS_NULL_VALUE(v:hello),
            IS_NULL_VALUE(v:world),
            IS_NULL_VALUE(v:goodbye),
            IFF(IS_NULL_VALUE(v:non_existing_key) is null, 'Key does not exist', 'Key exists'),
            IFF(IS_NULL_VALUE(v:hello) is null, 'Key does not exist', 'Key exists'),
            IFF(IS_NULL_VALUE(v:world) is null, 'Key does not exist', 'Key exists')
    ;
    

    Also, the STRIP_NULL_VALUE function can be used to verify is field json null or absent

    select  parse_json('{hello: NULL, world: 123}') as V,
        STRIP_NULL_VALUE(v:non_existing_key),
        STRIP_NULL_VALUE(v:hello),
        STRIP_NULL_VALUE(v:world);