SQL Array with Null

1
votes

I'm trying to group BigQuery columns using an array like so:

with test as (
   select 1 as A, 2 as B
   union all
   select 3, null
)

select *,
       [A,B] as grouped_columns
from test

However, this won't work, since there is a null value in column B row 2.

In fact this won't work either:

select [1, null] as test_array

When reading the documentation on BigQuery though, it says Nulls should be allowed.

In BigQuery, an array is an ordered list consisting of zero or more values of the same data type. You can construct arrays of simple data types, such as INT64, and complex data types, such as STRUCTs. The current exception to this is the ARRAY data type: arrays of arrays are not supported. Arrays can include NULL values.

There doesn't seem to be any attributes or safe prefix to be used with ARRAY() to handle nulls.

So what is the best approach for this?

3
sqlarraysgoogle-bigquery
I have no experience about BigQuery, but just for bypass null maybe use if null function to convert it into a non-null value represent null? like if column B never should be negative set it to -1? this is just a method if I need to have a data represent "null" but can't actually use "null" . - T. Peter
Right. So I have to group 20 columns this way? It should work but not efficient. When I look at documentation there are plenty of examples with [1, 2, NULL] arrays... - neydroydrec

3 Answers

2
votes

Per documentation - for Array type

Currently, BigQuery has two following limitations with respect to NULLs and ARRAYs:

  • BigQuery raises an error if query result has ARRAYs which contain NULL elements, although such ARRAYs can be used inside the query.

  • BigQuery translates NULL ARRAY into empty ARRAY in the query result, although inside the query NULL and empty ARRAYs are two distinct values.

So, as of your example - you can use below "trick"

with test as (
   select 1 as A, 2 as B union all
   select 3, null
)
select *, 
  array(select cast(el as int64) el
    from unnest(split(translate(format('%t', t), '()', ''), ', ')) el
    where el != 'NULL'
  ) as grouped_columns
from test t

above gives below output

enter image description here

Note: above approach does not require explicit referencing to all involved columns!

0
votes

My current solution---and I'm not a fan of it---is to use a combo of IFNULL(), UNNEST() and ARRAY() like so:

select 
       *,
       array(
           select * 
           from unnest(
               [
                   ifnull(A, ''), 
                   ifnull(B, '')
                   ]
               ) as grouping 
           where grouping <> ''
           ) as grouped_columns
from test
0
votes

An alternative way, you can replace NULL value to some NON-NULL figures using function IFNULL(null, 0) as given below:-

with test as (
   select 1 as A, 2 as B
   union all
   select 3, IFNULL(null, 0)
)

select *,
       [A,B] as grouped_columns
from test