LOWER_ATTRIBUTE_NAMES function - Amazon Redshift
SyntaxArgumentsReturn typeUsage notesExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

LOWER_ATTRIBUTE_NAMES function

Converts all applicable attribute names in a SUPER value to lowercase, using the same case conversion routine as the LOWER function. LOWER_ATTRIBUTE_NAMES supports UTF-8 multibyte characters, up to a maximum of four bytes per character.

To convert SUPER attribute names to uppercase, use the UPPER_ATTRIBUTE_NAMES function.

Syntax

LOWER_ATTRIBUTE_NAMES(super_expression)

Arguments

super_expression

A SUPER expression.

Return type

SUPER

Usage notes

In Amazon Redshift, column identifiers are traditionally case-insensitive and converted to lowercase. If you ingest data from case-sensitive data formats such as JSON, the data might contain mixed-case attribute names.

Consider the following example.

CREATE TABLE t1 (s) AS SELECT JSON_PARSE('{"AttributeName": "Value"}');


SELECT s.AttributeName FROM t1;  

attributename
-------------
NULL


SELECT s."AttributeName" FROM t1;

attributename
-------------
NULL

Amazon Redshift returns NULL for both queries. To query AttributeName, use LOWER_ATTRIBUTE_NAMES to convert the data’s attribute names to lowercase. Consider the following example.

CREATE TABLE t2 (s) AS SELECT LOWER_ATTRIBUTE_NAMES(s) FROM t1;


SELECT s.attributename FROM t2;

attributename
-------------
"Value"


SELECT s.AttributeName FROM t2; 

attributename
-------------
"Value"


SELECT s."attributename" FROM t2;

attributename
-------------
"Value"


SELECT s."AttributeName" FROM t2;

attributename
-------------
"Value"

A related option for working with mixed-case object attribute names is the enable_case_sensitive_super_attribute configuration option, which lets Amazon Redshift recognize case in SUPER attribute names. This can be an alternative solution to using LOWER_ATTRIBUTE_NAMES. For more information about enable_case_sensitive_super_attribute, go to enable_case_sensitive_super_attribute.

Examples

Converting SUPER attribute names to lowercase

The following example uses LOWER_ATTRIBUTE_NAMES to convert the attribute names of all SUPER values in a table.

-- Create a table and insert several SUPER values.
CREATE TABLE t (i INT, s SUPER);

INSERT INTO t VALUES
  (1, NULL), 
  (2, 'A'::SUPER),
  (3, JSON_PARSE('{"AttributeName": "B"}')),
  (4, JSON_PARSE(
     '[{"Subobject": {"C": "C"},
        "Subarray": [{"D": "D"}, "E"]
      }]'));

-- Convert all attribute names to lowercase.
UPDATE t SET s = LOWER_ATTRIBUTE_NAMES(s);

SELECT i, s FROM t ORDER BY i;

 i |                        s
---+--------------------------------------------------
 1 | NULL
 2 | "A"
 3 | {"attributename":"B"}
 4 | [{"subobject":{"c":"C"},"subarray":[{"d":"D"}, "E"]}]

Observe how LOWER_ATTRIBUTE_NAMES functions.

  • NULL values and scalar SUPER values such as "A" are unchanged.

  • In a SUPER object, all attribute names are changed to lowercase, but attribute values such as "B" remain unchanged.

  • LOWER_ATTRIBUTE_NAMES applies recursively to any SUPER object that is nested inside a SUPER array or inside another object.

Using LOWER_ATTRIBUTE_NAMES on a SUPER object with duplicate attribute names

If a SUPER object contains attributes whose names differ only in their case, LOWER_ATTRIBUTE_NAMES will raise an error. Consider the following example.

SELECT LOWER_ATTRIBUTE_NAMES(JSON_PARSE('{"A": "A", "a": "a"}'));      

error:   Invalid input
code:    8001
context: SUPER value has duplicate attributes after case conversion.