How to remove NULL from array in Athena

Question

I have arrays aggregated with this query:

select field_name1, 
       field_name2, 
       "array_agg"(DISTINCT <field_name3>) "array_agg"
from <table_name>
group by <field_name1>

Problem is that some outputs have NULL values in them.

['element1', 'element2', NULL]

How can we remove the NULL from array when aggregate values.

I have tried

select field_name1, 
       field_name2, 
       "array_remove"("array_agg"(DISTINCT <field_name1>), NULL) "array_agg_drop_NULL""array_agg"
from <table_name>
group by <field_name1>

but it returns output with all blanks.

Answer 1

You can use filter function:

select filter(array['element1', 'element2', NULL], el -> el is not null) filtered

Output:

filtered
[element1, element2]