jsonamazon-redshiftextractsupertype

get values from super type in Redshift

In my table I have super type column named zip. Column in my opinion contains a dictionary, it is not valid json. How can I extract values as separate columns. I mean zip.zip4, zip.zip5

enter image description here

I tried use,

JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(zip), 'zip4')

but it is not valid JSON, first I have to remove brackets [], but type is super so I can't do that.

code with data:

with cte as(
select JSON_PARSE('[{"zip1":"07192","zip2":""}]') as zip
union all
select JSON_PARSE('[{"zip1":"09102","zip2":"53"}]') as zip
)
select * from cte;
Solution

  • First off it is bad form to post data as an image. Please don't do this.

    You JSON is perfectly valid. You just have an array as the top level element of you JSON.

    As long as these arrays always only have 1 element then the following will parse this data (per your example code). Note the '[0]' when referencing this data as this indexes the first elements of these arrays.

    with cte as(
select JSON_PARSE('[{"zip1":"07192","zip2":""}]') as zip
union all
select JSON_PARSE('[{"zip1":"09102","zip2":"53"}]') as zip
)
select zip[0]."zip1" as zip1, zip[0]."zip2" as zip2 from cte;