Checking if a key exists in a Snowflake variant

user_160406 0
Viewed 4185

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

snowflake-cloud-data-platform exists key
3 Answers

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

You can simply use a combination of OBJECT_KEYS function, which outputs an array of only the existing keys in the Variant & ARRAY_CONTAINS function to return the Boolean value. See below.

SELECT ARRAY_CONTAINS('<key_to_search_for>'::VARIANT, OBJECT_KEYS(<variant_column>))
FROM <table_name>;
user_134279240

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.

user_121789800
Related