'Check for null keys in map? Presto
I have a query that was running fine for a while now that was doing the following:
MAP_AGG(key, value(which is a map))
AS k_v1,
MAP_CONCAT(
k_v(some map),
MAP_UNION_SUM(
MAP(ARRAY[K], ARRAY[V])
) as k_v2
With some data from source that looks like this:
| key | value | k_v | K | V |
|---|---|---|---|---|
| id_2 | {"KEY2": "20"} | {"KEY4": "100"} | KEY8 | 100 |
| id_1 | {"KEY1": "96.25"} | {"KEY5": "150"} | KEY8 | 150 |
In which it provides a table as such:
| k_v1 | k_v2 |
|---|---|
| {"id_2":{{"KEY2": "20"}, "id_1":{"KEY1": "96.25"}} | {{"KEY4": "100"}, {"KEY5": "150"}, {"KEY8": "250"}} |
But now as a new job was running, I get an error stating that
"Failure": "map key cannot be null"
I'm trying to understand how to catch such a case with Presto, as it seems pretty verbose of a process to have to unnest these kinds of situations to check for null keys. Is there a more easier or built in solution to do this kind of check and remove that from the mapping?
Edit: I have hundred's of thousands of records that needs to be processed. The sample data above is to illustrate the schema.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
