jsonamazon-web-servicesamazon-redshift

Redshift SUPER type: accessing camel case fields returning null result

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?

Solution

  • 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