I've found more recent information on this. You can now use the following syntax according to the AWS Redshift documentation:

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;
SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index;

https://docs.aws.amazon.com/redshift/latest/dg/query-super.html