Redshift SUPER type: accessing camel case fields returning null result-CodePudding

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?

CodePudding user response：

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