json - Split a varchar column in Redshift

Question

I have a table sales with all the column has varchar as datatype.

 Name           items 
 Dave    {"item1": {"product": apple, "cost": 120}, "item2": {"product": orange, "cost": 80}}
 Nick      
 Jack    {"item1": {"product": melon, "cost": 100}, "item2": {"product": berries, "cost": 240}}

I goal is to split the column items as below using SELECT statement

Name    items1_product   items1_cost  items2_product  items2_cost 
 Dave        apple            120        orange           80     
 Nick     
 Jack        melon            100        berries         240

I tried to convert the datatype of items to Json by "select items::JSONB from sales " and I am getting error, type "jsonb" does not exist;

Is there any other way we can achieve this ?

Answer 1

Redshift doesn't have a native json datatype but it does have json parsing functions. What you have stored in the "items" column is text, not json. To extract an element from this json text you can use the Redshift json built-in functions which you can read about here - https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html

So to extract item1's product name for each person you can:

select select json_extract_path_text(items, 'item1', 'product') as items1_product from <table>;

Now your json doesn't look to be properly formatted as word "apple" is a string value and should be quoted. I expect this was just an oversight in writing up the example for your question. If this isn't the case and you have some json cleanup to do there is a is_valid_json() function that you can use to ensure that your table json data is correct before trying to parse it.