0

Trying to use function ARRAY_AGG and to get it to ignore null values but the doc does not give any info about that. Tried using "IGNORE NULLS" that spanner recognizes but wont consider as valid.

Example:

SELECT ARRAY_AGG(x IGNORE NULLS) FROM UNNEST([1,NULL,2,3]) x

That throws: IGNORE NULLS and RESPECT NULLS in aggregate functions are not supported

Maxim
  • 4,075
  • 1
  • 14
  • 23
gsouf
  • 901
  • 10
  • 25

2 Answers2

2

You can still explicitly filter out the nulls before aggregating:

  select array_agg(a) from unnest([1,2,3,cast(null as int64)])                                                      
  a where a is not null;

Will produce [1,2,3] as the result.

adi
  • 580
  • 2
  • 12
  • Unfortunately that wont work. My example was a simple case where it makes sense to filter things out, but for my use case I need to keep records having 0 row after a JOIN. Having 0 joined rows means the value is null and if I filter them out, I filter out the whole row. – gsouf Nov 29 '18 at 20:25
  • @SoufianeGhzal Maybe [this](https://stackoverflow.com/a/42584757/8456296) solution works for you as you can substitute the NULL value by some other default value for example -999: SELECT ARRAY_AGG(IFNULL(x,-999)) FROM UNNEST([1,NULL,2,3]) x – dhauptman Dec 06 '18 at 12:20
1

In case you missed it, Spanner now supports the IGNORE NULLS statement as described in doc.

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])
Colin Le Nost
  • 460
  • 4
  • 10