I'm trying to access fields of a SUPER column which have camel case fields, so something like:
{"FirstName": "Mario", "LastName": "Maria"}
So let's say I store this field in Redshift as column my_json, then I'd query it with
SELECT my_json.FirstName
FROM my_table
Then I'd get only null result instead of the real value.
How to handle this use case?
Second Redshift defaults to lower case for all column names so FirstName is being seen as firstname. You can enable case sensitive column names by setting the enable_case_sensitive_identifier connection variable to true and quoting all column names that require upper characters:
SET enable_case_sensitive_identifier TO true;
and changing my_json.FirstName to my_json."FirstName".
See:
https://docs.aws.amazon.com/redshift/latest/dg/r_enable_case_sensitive_identifier.html
https://docs.aws.amazon.com/redshift/latest/dg/super-configurations.html