Checking to see if array field is null or empty?

Question

In snowflake, how can I filter for null or empty array fields in a column?

Column has an empty [] or string of values in that bracket, tried using array_size(column_name, 1) > 0 but array_size does not function,

Thanks

Answer 1

Are you trying to filter them in or out?

Either way the array_size should work. although there's no second argument where column_name is not null and array_size(column_name) != 0 worked for me

If you're specifically looking to filter to the records that have an empty array, this approach works too, although it's a little odd. where column_name = array_construct()

Edit: It seems like your issue is that your column is a string. There's a few ways to work around this

• Change your column's datatype to a variant or array
• Parse your column before using array functions array_size(TRY_PARSE_JSON(column_name)) != 0
• Compare to a string instead column_name is not null and column_name != '[]'