I have a nested JSON field that looks like this:
trend
{"trend":0,"abs":0,"per":null}
I want to query and get the values that are inside, so I have a case statement in order to get the values:
select
(CASE WHEN trend like '%"trend"%' THEN json_extract_path_text(trend, 'trend') ELSE NULL END)::SMALLINT,
(CASE WHEN trend like '%"abs"%' THEN json_extract_path_text(trend, 'abs') ELSE NULL END)::INTEGER,
(CASE WHEN trend like '%"per"%' THEN json_extract_path_text(trend, 'per') ELSE NULL END)::DOUBLE PRECISION
from staging_raw.table
The issue is in the last field per, because it's null already I am getting this error message in Redshift:
I tried to use the clause:
(CASE WHEN trend like '%"per"%' and json_extract_path_text(trend, 'per') is not NULL THEN json_extract_path_text(trend, 'per') ELSE NULL END)::DOUBLE PRECISION
but I still get the same error. How can this be resolved, if the source is like this?