Snowflake - Querying null values in variant

0
votes

I would like to filter off records with certain keys within variant having null values

variant column structure

Column name: specs

{
"color": 32,
"browser": null,
"language": "en"
)

Want something like below:

select * from table where specs:browser is not null

But it doesn't work as this seem to be a json null and not SQL NULL. Is there a way that I can get the values stored as SQL NULL within the variant? I'm more interested in having a straightforward query to filter data rather than writing some functions for conversion.

Thanks!

1
nullsnowflake-cloud-data-platformsql-null

1 Answers

1
votes

Snowflake has different functions to detect database null and JSON null. The one you want is is_null_value:

create temp table t(v variant);
insert into t select parse_json('{"color": 32,"browser": null,"language": "en"}');

select is_null_value(v:browser) as IS_BROWSER_NULL from t;

You can then use in your where clause like this:

select * from t where not is_null_value(v:browser);