Query nested reserved-word attributes from Redshift

Question

I have a data repository in JSON that includes an attribute called "timestamp" (which is a reserved word). This attribute is part of a nested JSON/struct.

The way I query the data is by creating a Glue Table schema and using SQL from Spectrum, beautiful! The problem is that the only way I was able to create this nested attribute was by wrapping the column name with backticks (timestamp). However, it's not possible to query this attribute with Redshift Spectrum just like that.

I've tried using this query, but the result shows null values in the column:

SELECT DISTINCT
       e.data.user_id::int AS userid,
       e.data.api AS platform,
       l."`timestamp`",
FROM "glue-geofencing"."events_raw" e
LEFT JOIN e.data.events l ON true;

I also tried l."timestamp" but it raises an error

This is a sample of the Glue schema:

    STRUCT<
      api:STRING,
      events:ARRAY<
        STRUCT<
          places:ARRAY<
              ID:STRING,
              name:STRING
            >
          >,
          `timestamp`:STRING,
        >
      >,
      user_id:STRING,
      device_id:STRING

So far, the only solution has been going directly to the Glue Table schema and remove the backticks from the timestamp column. (I wonder why this is not possible from creation).

I couldn't find anything in the documentation about handling these reserved-words + nested-attributes issues in Redshift Spectrum. Only https://docs.aws.amazon.com/redshift/latest/dg/r_pg_keywords.html

My hope is that there's a simple solution to access this data directly from SQL or being able to create the table in Glue without this extra characters.

Note: I don't have control over the data, so renaming the attribute is not an option.

Thanks

re:Post Agent · Answer

When working with nested JSON data in Redshift Spectrum that contains reserved words like "timestamp" as attribute names, you have a few options to handle this situation:

1. **Use case-sensitive identifiers**: You can enable case-sensitive identifiers in your Redshift session by setting:
```sql
SET enable_case_sensitive_identifier to TRUE;
```
Then query using double quotes around the attribute name:
```sql
SELECT e.data.user_id::int AS userid,
e.data.api AS platform,
l."timestamp"
FROM "glue-geofencing"."events_raw" e
LEFT JOIN e.data.events l ON true;
```

2. **Use LOWER_ATTRIBUTE_NAMES function**: If your JSON data contains mixed-case attribute names, you can use the LOWER_ATTRIBUTE_NAMES function to convert all attribute names to lowercase, which might help with the reserved word issue:
```sql
CREATE TABLE normalized_data AS 
SELECT LOWER_ATTRIBUTE_NAMES(data) AS data 
FROM "glue-geofencing"."events_raw";
```
Then query the normalized data.

3. **Enable case-sensitive SUPER attributes**: As an alternative to LOWER_ATTRIBUTE_NAMES, you can use the `enable_case_sensitive_super_attribute` configuration option, which allows Redshift to recognize case in SUPER attribute names.

Note that when working with nested data in Redshift Spectrum, there are limitations to be aware of, such as maximum nesting depth of 100 and restrictions on how you can query nested arrays and maps.

If you're still having issues, you might need to adjust your Glue table definition. While removing backticks worked as a workaround, ideally you should be able to define the schema with proper quoting for reserved words.  
**Sources**  
[LOWER_ATTRIBUTE_NAMES function - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_lower_attribute_names.html)  
[Nested data limitations (preview) - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/nested-data-restrictions.html)  
[SUPER configurations - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/super-configurations.html)

orintalver · Answer

✅ Problem:

Glue schema uses backticks for timestamp.

Redshift Spectrum cannot query it directly.

💡 Fix:

When you create the table, avoid backticks — use double quotes + aliasing:

SELECT l."timestamp" AS event_timestamp
FROM "glue-geofencing"."events_raw" e
CROSS JOIN UNNEST(e.data.events) AS t(l);

If Glue insists on using backticks:

Edit schema in Glue to rename the column to something like timestamp_col (supported via AWS CLI).

Or create a Redshift Spectrum view:

CREATE VIEW events_vw AS
SELECT json_extract_path_text(event, 'timestamp') AS event_timestamp
FROM "glue-geofencing"."events_raw";

AlexC · Answer

After taking another close look, I actually realized that it's possible to create the Glue Table reserved-word attributes without the backticks and it perfectly works on Redshift. So, This is not an issue in reality. Sorry

AlexC

answered a year ago

Query nested reserved-word attributes from Redshift

Add your answer

Relevant content