Querying Redshift Spectrum array of string columns

Question

I have an external (s3) table in my redshift cluster with an array of string column. It's literally just a list of strings. I can query and only select the array column no worries. I can query all 3 of the array columns no worries but as soon as I try to query other columns that are not arrays I get the following:

error: Spectrum Scan Error

I have tried the following as I saw it on some other stack overflow questions

select id_col, b
from test.test_table as a, a.array_col as b 

but when I run the above I get: navigation on array_col is not allowed as it is not a struct/tuple type

Of course, this error message makes sense as it isn't a struct or tuple type but I am lost as to how on earth I can query a simple array of strings and have found no documentation on how to do this. Any help or advice would be greatly appreciated!

Answer 1

Since you renamed the external table, you have to use that name for all the fields that you want to retrieve; in the same way you did with the array column. Your query will be:

select a.id_col, b
from test.test_table as a, a.array_col as b