How to use mixed case json without enable_case_sensitive_super_attribute

Question

My SQL client (PopSQL) does not allow setting parameters per query.

According to RedShift [documentation](https://docs.aws.amazon.com/redshift/latest/dg/r_enable_case_sensitive_identifier.html) default should be case insensitive but in practice it looks like it does not accept any casing:

```sql
with q as (select json_parse('{"loHi":1}') o)
select o."loHi", o.loHi, o.lohi, o."lohi", o."LOHI", o.LOHI from q
```

Is this a bug?

Justus_M · Accepted Answer

In Amazon Redshift, the default value for `enable_case_sensitive_identifier` and `enable_case_sensitive_super_attribute` is `false`.

When JSON attribute names are in uppercase or mixedcase, you must navigate SUPER type structures in a case sensitive way.
To do that, you can configure enable_case_sensitive_identifier to TRUE and wrap the uppercase and mixedcase attribute names with double quotation marks. You can also configure enable_case_sensitive_super_attribute to TRUE. This is by design as per the documentation here [1].

Since the SQL client does not allow setting parameters, you can use LOWER_ATTRIBUTE_NAMES function which converts all applicable attribute names in a SUPER value to lowercase as shown below:

`with q as (select LOWER_ATTRIBUTE_NAMES(json_parse('{"loHi":1}')) o)
    select  o."loHi", o.loHi, o.lohi, o."lohi", o."LOHI", o.LOHI 
    from q ;`

----------------------------------OUTPUT----------------------------
 
```
lohi	lohi	lohi	lohi	lohi	lohi
  1      1        1       1       1       1 
```
 
Resources:
1. [JSON mixedcase/uppercase attribute names](https://docs.aws.amazon.com/redshift/latest/dg/super-configurations.html)
2. [enable_case_sensitive_identifier documentation](https://docs.aws.amazon.com/redshift/latest/dg/r_enable_case_sensitive_identifier.html)
3. [enable_case_sensitive_super_attribute documentation](https://docs.aws.amazon.com/redshift/latest/dg/r_enable_case_sensitive_super_attribute.html)

re:Post Agent · Answer

If you cannot use the `enable_case_sensitive_super_attribute` parameter due to limitations in your SQL client, there are alternative approaches to handle mixed case JSON in Amazon Redshift:

1. Use the LOWER_ATTRIBUTE_NAMES function: This function converts all attribute names in a SUPER value to lowercase, allowing you to query the data consistently. Here's how you can modify your query:

```sql
with q as (select LOWER_ATTRIBUTE_NAMES(json_parse('{"loHi":1}')) as o)
select o.lohi from q
```

This approach will return the value 1 for the 'lohi' attribute.

2. Use double quotes and enable_case_sensitive_identifier: If you can set the `enable_case_sensitive_identifier` parameter to TRUE (which is different from `enable_case_sensitive_super_attribute`), you can use double quotes to specify the exact case of the attribute name:

```sql
SET enable_case_sensitive_identifier to TRUE;

with q as (select json_parse('{"loHi":1}') o)
select o."loHi" from q
```

This should return the value 1 for the 'loHi' attribute.

3. Parse the JSON as needed: If the above options are not available, you can parse the JSON string directly in your query:

```sql
with q as (select '{"loHi":1}' as json_string)
select json_extract_path_text(json_string, 'loHi') as lohi from q
```

This approach allows you to specify the exact case of the JSON key without relying on SUPER type or configuration parameters.

By using one of these methods, you should be able to work with mixed case JSON in Redshift without needing to set the `enable_case_sensitive_super_attribute` parameter.  
**Sources**  
[LOWER_ATTRIBUTE_NAMES function - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_lower_attribute_names.html)  
[SUPER configurations - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/super-configurations.html)  
[JSON functions - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html)

How to use mixed case json without enable_case_sensitive_super_attribute

Add your answer

Relevant content