SQL Array with Null

Question

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?

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
– T. Peter, Commented Jan 13, 2021 at 9:04
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
– neydroydrec, Commented Jan 13, 2021 at 9:06

Mikhail Berlyant · Accepted Answer · 2021-01-13 10:14:35Z

2

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

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

answered Jan 13, 2021 at 10:14

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

neydroydrec Over a year ago

Thanks! I had no idea you could pass an entire row in FORMAT() that's very cool. Also I often work with REPLACE() or REGEX_REPLACE() but discovering now TRANSLATE(), which is a little mad and very useful. This solves the quite a bit of the problem. However, if there's an index on each row, I could not avoid feeding it to FORMAT() right? I'd have to pop it out after the array is constructed?

neydroydrec · Accepted Answer · 2021-01-13 10:10:38Z

0

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

answered Jan 13, 2021 at 10:10

neydroydrec

7,38110 gold badges64 silver badges94 bronze badges

1 Comment

neydroydrec Over a year ago

Still I can't quite understand why there's no way to have a null in an array while the documentation says it can. Also the documentation has clear examples of array with NULL, such as SELECT ["coffee", NULL, "milk" ] as list UNION ALL SELECT ["cake", "pie"]

Vibhor Gupta · Accepted Answer · 2021-01-13 11:09:20Z

0

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

answered Jan 13, 2021 at 11:09

Vibhor Gupta

6991 gold badge7 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL Array with Null

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related