Q:

Combine columns from a table into a JSON object

I have a table in athena with data 
type   state   city  zipcode
-------------------------------
hot     az     phx    85281
hot     tx     dal    12345
cool    wa     sea    67890
cool    ny     nyc    67856

I want the output to be like
type   Data 
-------------
hot    {state: az, city:phx, zipcode: 85281}
hot    {state: tx, city:dal, zipcode: 12345}
cool   {state: wa, city:sea, zipcode: 67890}
cool   {state: ny, city:nyc, zipcode: 67856}

I am trying to use the aggregate function but i'm unable to use it.  
SELECT type, CAST(MAP(ARRAY['state', 'city', 'zipcode'], ARRAY[state,city,zipcode] ) AS JSON) FROM "test". "alldata"
but the query fails. 

A:

For Hive:
with mydata as (
select stack(4,
    'hot', 'az', 'phx', 85281,
    'hot', 'tx', 'dal', 12345,
    'cool', 'wa', 'sea', 67890,
    'cool', 'ny', 'nyc', 67856
) as  (type, state, city, zipcode)
)

select type, map('state', state, 'city', city,'zipcode',zipcode) as data
 from mydata;

Result:
type    data
hot     {"state":"az","city":"phx","zipcode":"85281"}
hot     {"state":"tx","city":"dal","zipcode":"12345"}
cool    {"state":"wa","city":"sea","zipcode":"67890"}
cool    {"state":"ny","city":"nyc","zipcode":"67856"}

If you need string type, use brickhouse library:
add jar /path/brickhouse-0.7.0-SNAPSHOT.jar; --compile jar and load it to the distributed cache
CREATE TEMPORARY FUNCTION to_json AS 'brickhouse.udf.json. ToJsonUDF';

select type, to_json(map('state', state, 'city', city,'zipcode',zipcode)) as data
 from mydata;

