Menu
Is there a function to check if a key exists in a Snowflake variant field?
210
Checks the validity of a JSON document. If the input string is a valid JSON document or a NULL, the output is NULL (i.e. no error). If the input cannot be translated to a valid JSON value, the output string contains the error message.
In a Snowflake OBJECT, each key is a VARCHAR, and each value is a VARIANT. Because VARIANT can store any other data type, different values (in different key-value pairs) can have different underlying data types. For example, an OBJECT can hold a person's name as a VARCHAR and a person's age as an INTEGER.
Create a table and add VARCHAR, generic VARIANT, and JSON-compatible VARIANT data. The INSERT statement inserts a VARCHAR value, and the UPDATE statement generates a JSON value that corresponds to that VARCHAR. Although both PARSE_JSON and TO_VARIANT can take a string and return a variant, they are not equivalent.
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')
;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
