CODEWITHSUNDEEP
jsonhivehiveql
Vinny
Vinny

Reputation: 865

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.

Upvotes: 4

Views: 1781

Answers (2)

leftjoin
leftjoin

Reputation: 38325

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;

Upvotes: 3

nbk
nbk

Reputation: 49375

If you have a mysql Version with 5.7 or higher : use

SELECT `type`,json_object('state', `state`, 'city', `city`,'zipcode',`zipcode`)
FROM alldata;

CREATE TABLE alldata
    (`type` varchar(4), `state` varchar(2), `city` varchar(3), `zipcode` int)
;
    
INSERT INTO alldata
    (`type`, `state`, `city`, `zipcode`)
VALUES
    ('hot', 'az', 'phx', 85281),
    ('hot', 'tx', 'dal', 12345),
    ('cool', 'wa', 'sea', 67890),
    ('cool', 'ny', 'nyc', 67856)
;

✓

✓

SELECT `type`,json_object('state', `state`, 'city', `city`,'zipcode',`zipcode`)
FROM alldata;

type | json_object('state', `state`, 'city', `city`,'zipcode',`zipcode`)
:--- | :----------------------------------------------------------------
hot  | {"city": "phx", "state": "az", "zipcode": 85281}                 
hot  | {"city": "dal", "state": "tx", "zipcode": 12345}                 
cool | {"city": "sea", "state": "wa", "zipcode": 67890}                 
cool | {"city": "nyc", "state": "ny", "zipcode": 67856}

db<>fiddle here

Upvotes: 0

Related Questions