CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-spanner
gsouf
gsouf

Reputation: 1113

Ignore null values in cloud spanner's aggregate functions

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

Upvotes: 0

Views: 987

Answers (2)

Colin Le Nost
Colin Le Nost

Reputation: 490

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])

Upvotes: 1

adi
adi

Reputation: 590

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.

Upvotes: 2

Related Questions