Is there a function to check if a key exists in a Snowflake variant field?
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);