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!

Hyruma92 · Accepted Answer · 2020-11-25 09:50:34Z

0

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

answered Nov 25, 2020 at 9:50

Hyruma92

9068 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying Redshift Spectrum array of string columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related