Combine columns from a table into a JSON object

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    datahot     {"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 cacheCREATE TEMPORARY FUNCTION to_json AS 'brickhouse.udf.json.ToJsonUDF';select type, to_json(map('state', state, 'city', city,'zipcode',zipcode)) as data from mydata;

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