Select field with Hyphen in Redshift Spectrum

Question

I am trying to extract a nested field with an Hyphen in the name through Redshift Spectrum

SELECT mystruct.mysubstruct.my-field.id
FROM my_external_schema.my_table 

I see in other DBMS is suggested to wrap the field name with double quotes:

"mystruct.mysubstruct.my-field.id"

or back ticks

`mystruct.mysubstruct.my-field.id`

but none of these worked for me.

Any suggesitons?

Answer 1

Since the double quotes permit to escape the special characters, doing "mystruct.mysubstruct.my-field.id" means that you are looking for the column named 'mystruct.mysubstruct.my-field.id' at top level and not as the nested column, because the dot is not used to extract the field.

What you have to do is

SELECT mystruct.mysubstruct."my-field".id
FROM my_external_schema.my_table