I am trying to collect a column with NULL
s along with some values in that column...But collect_list
ignores the NULL
s and collects only the ones with values in it. Is there a way to retrieve the NULL
s along with other values ?
SELECT col1, col2, collect_list(col3) as col3
FROM (SELECT * FROM table_1 ORDER BY col1, col2, col3)
GROUP BY col1, col2;
Actual col3 values
0.9
NULL
NULL
0.7
0.6
Resulting col3 values
[0.9, 0.7, 0.6]
I was hoping that there is a hive solution that looks like this [0.9, NULL, NULL, 0.7, 0.6]
after applying the collect_list.
This function works like this, but I've found the following workaround. Add a case when statement to your query to check and keep NULLs.
SELECT col1,
col2,
collect_list(CASE WHEN col3 IS NULL THEN 'NULL' ELSE col3 END) as col3
FROM (SELECT * FROM table_1 ORDER BY col1, col2, col3)
GROUP BY col1, col2
Now, because you had a string element ('NULL') the whole result set is an array of strings. At the end just convert the array of strings to an array of double values.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With