mu 無 mu 無 - 4 years ago 78
JSON Question

expand a JSON data into new columns in a generic fashion in Redshift

I have a DB table like


ID Params
1234 {'normalized_CR': 1.111434628975265, 'Rating': 0.0, Rank': 1410}
1235 {'normalized_CR': 1.123142131, 'Rating': 1.0, Rank': 210}

How can I expand this data into individual columns by same name in Redshift?

I am googling online, but get results mostly for
which can get only one key.

Answer Source

After much googling, turns out that there is no simple way to do this as of now, and the brute force way is the way ahead. Also, the data above was not valid Json (' instead "):

  json_extract_path_text(REPLACE(Params, '\'', '"'), 'normalized_CR') as normalized_CR,
  json_extract_path_text(REPLACE(Params, '\'', '"'), 'Rating') as Rating,
  json_extract_path_text(REPLACE(Params, '\'', '"'), 'Rank') as Rank
order by
    id desc
limit 100;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download