Redshift SUPER type: accessing camel case fields returning null result

Question

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?

Answer 1

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

Answer 2

adding to @Bill's answer above, there is a less general flag you can enable for SUPER json fields.

In your case, you can enable enable_case_sensitive_super_attribute like so:

SET enable_case_sensitive_super_attribute to TRUE;
 -- Accessing JSON attribute names with uppercase and mixedcase names

For your case, this is better than enable_case_sensitive_identifier, since that flag affects databases, tables, columns, etc.

https://docs.aws.amazon.com/redshift/latest/dg/super-configurations.html