I have a complex data type as a column, it is a map with a nested array: map<int,array<int>>. The data looks like this:
| date | id | info |
|------------|----|---------------------------|
| 2021-07-01 | 1 | {123:[a,b,c],111:[3,6,9]} |
| 2021-07-01 | 2 | null |
| 2021-07-01 | 3 | {123:[1,3]} |
| 2021-07-01 | 4 | {40:[2]} |
How can I filter based on the key of the info column? For example, to return all rows with 123 as a key? The documentation that I see doesn't seem to have examples - https://cwiki.apache.org/confluence/display/hive/languagemanual+types#LanguageManualTypes-ComplexTypes
Expected output would look like this:
| date | id | info |
|------------|----|---------------------------|
| 2021-07-01 | 1 | {123:[a,b,c],111:[3,6,9]} |
| 2021-07-01 | 3 | {123:[1,3]} |
Just filter info[123] is not null:
select *
from table
where info[123] is not null