How to extract a json element from each json in an array in RedShift?

Question

I have a table table with super column scans with values like [{"A": 1}, {"A": 2}], [{"A": 3}, {"A": 4}, {"A": 5}].

How do i make a column with values like this out of it [1, 2], [3, 4, 5]?

I tried something like

SELECT
    scans[r]."A"
FROM table t
cross join (select row_number() OVER (ORDER BY true) r from some_table) x
where r < get_array_length(t.scans)

But getting an error

[XX000] ERROR: Query unsupported due to an internal error. 
Detail: SQL reparse error. Where: function get_array_path(super, bigint) does not exist.

Answer 1

You likely need to start with unnesting the super array - https://docs.aws.amazon.com/redshift/latest/dg/query-super.html

This will give you a row for each row in the original table cross joined with each element in the array for that row. From there you can just pull out the "A" value. Something like:

select s.A 
from table t, t.scans s;

Remember that since you are using capital letters in the super you will need to turn on case sensitivity for your Redshift session.

SET enable_case_sensitive_identifier TO true;