CODEWITHSUNDEEP
sqlamazon-athena
Dick McManus
Dick McManus

Reputation: 789

Select and count array keys in athena

I have many rows of data that represent events in my database. Each row has a column "payload" that contains an array of keys and values. I can easily parse for a value by using

Select
payload.keyname
from Database

But I am trying to get a list and count of all the keys that appear in a given day.

|                   payload                               |    
|{id=a, gameid=x, gametype=1, sponserid=null}             |
|{id=b, gameid=y, gametype=2, action=jump, sponserid=null}|
|{id=c, gameid=z, action=jump, sponserid=null}            |

Desired Output

|    Key  |Count|
|id       |  3  |
|game     |  3  |
|gametype |  2  |
|action   |  2  |
|sponserid|  2  |

Is there some method to query an array for keys easily? Such as

Select
payload.*, count(*)
from Database
group by payload.*

Upvotes: 0

Views: 2248

Answers (2)

Nicolas Busca
Nicolas Busca

Reputation: 1305

You can use cross join unnest. The unnest will "unroll" the map and return a row for each map entry with key, value columns. If you want to count occurrences of each key you can group by key. For example

select key, count(*)
from mydb cross join unnest(payload) A(key, value)
group by 1

see the docs for more info.

----- EDIT ----

If your column is already in row format you can do instead:

select payload.keyname, count(*)
from mydb cross join payload
group by 1

Upvotes: 1

saifuddin778
saifuddin778

Reputation: 7288

You can use map_keys function to extract keys from payload and unnest on top of it.

select key, count(1) as count
from database.table, unnest(map_keys(payload)) as X(key)
group by 1

Upvotes: 2

Related Questions