Checking if a key exists in a Snowflake variant

Solution 1:

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')
;

Solution 2:

If you are looking for a way to find an attribute within a nested JSON structure that might be complex, you could run something like this:

SELECT COUNT(*)
FROM x
,LATERAL FLATTEN(parse_json(x.var), recursive => True) x2
WHERE x2.key = 'test';

This flattens the whole structure out and gives you every key available in the JSON at any level. If the count is 0, then it doesn't exist. If run a straight select, rather than a COUNT(*) it'll show you the value, as well, and you can determine where in the nesting the attribute exists.