Selecting data based on key in map column

user_12530993 0
Viewed 223

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]}               |
hiveql hive dictionary sql
1 Answers

Just filter info[123] is not null:

select * 
from table
where info[123] is not null
user_27003440
Related