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

SomeSchema

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
json_extract_path
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 "):

select
  id,
  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
from
    DB.SomeSchema
order by
    id desc
limit 100;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download