amazon-redshiftReading JSON array in Redshift


Introduction

Currently, reading all elements in JSON array is not possible in Redshift. For e.g. if you want to read Manufacturer and model as columns from following JSON

Remarks

dim_idnumberinfomanufacturermodel 2001Nissan~Sentra^Nissan~Maxima^Ford~Taurus^Ford~Escort^NissanSentra 2002Nissan~Sentra^Nissan~Maxima^Ford~Taurus^Ford~Escort^NissanMaxima 2003Nissan~Sentra^Nissan~Maxima^Ford~Taurus^Ford~Escort^FordTaurus 2004Nissan~Sentra^Nissan~Maxima^Ford~Taurus^Ford~Escort^FordEscort