Hive: How to test and find for null map entries?

Question

If using the map type in a Hive table, how can I test for a null entry (key exists, but value is null)?

With table:

 test1 (id string, m map<string, string>)

I have a few entries that look like this:

id1 {"b":"B","c":null} 
id2 {"b":"B"}

If I run the query:

select * from test1 where m["c"] is null;

I will get both rows back since expression evaluates true each time.

How can I test between key exists and value is null?

Answer 1

I've come up with 2 solutions

To find the rows where the map actually contains a specific key and its is null:

select * from test1 where array_contains(map_keys(m),'c') and m["c"] is null;

To find any key where the value is null:

select id,k from test1 LATERAL VIEW explode(m) et as k,v where v is null;