Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Object functions Following are the SQL object functions that Amazon Redshift supports to create and operate on SUPER type objects: **Topics** + [GET\$1NUMBER\$1ATTRIBUTES function](get_number_attributes.md) + [LOWER\$1ATTRIBUTE\$1NAMES function](r_lower_attribute_names.md) + [OBJECT function](r_object_function.md) + [OBJECT\$1TRANSFORM function](r_object_transform_function.md) + [UPPER\$1ATTRIBUTE\$1NAMES function](r_upper_attribute_names.md) # GET\$1NUMBER\$1ATTRIBUTES function Returns a count of how many key-value pairs exist at the root level of a dictionary object. ## Syntax ``` GET_NUMBER_ATTRIBUTES( super_expression ) ``` ## Arguments *super\$1expression* A SUPER expression of dictionary form. ## Return type The GET\$1NUMBER\$1ATTRIBUTES function returns an INT type. ## Note This function only counts direct attributes and does not include pairs within nested dictionaries. ## Example The following example shows the GET\$1NUMBER\$1ATTRIBUTES function. ``` SELECT GET_NUMBER_ATTRIBUTES(JSON_PARSE('{"a": 1, "b": 2, "c": 3}')); get_number_attributes ----------------------- 3 (1 row) ``` The GET\$1NUMBER\$1ATTRIBUTES function only operates on the first level of the dictionary. ``` SELECT GET_NUMBER_ATTRIBUTES(JSON_PARSE('{"a": 1, "b": {"c": 3}}')); get_number_attributes ----------------------- 2 (1 row) ``` # LOWER\$1ATTRIBUTE\$1NAMES function Converts all applicable attribute names in a SUPER value to lowercase, using the same case conversion routine as the [LOWER function](r_LOWER.md). LOWER\$1ATTRIBUTE\$1NAMES supports UTF-8 multibyte characters, up to a maximum of four bytes per character. To convert SUPER attribute names to uppercase, use the [UPPER\$1ATTRIBUTE\$1NAMES function](r_upper_attribute_names.md). ## Syntax ``` LOWER_ATTRIBUTE_NAMES( super_expression ) ``` ## Arguments *super\$1expression* 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\$1ATTRIBUTE\$1NAMES 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\$1ATTRIBUTE\$1NAMES. For more information about `enable_case_sensitive_super_attribute`, go to [enable\$1case\$1sensitive\$1super\$1attribute](r_enable_case_sensitive_super_attribute.md). ## Examples **Converting SUPER attribute names to lowercase** The following example uses LOWER\$1ATTRIBUTE\$1NAMES 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\$1ATTRIBUTE\$1NAMES 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\$1ATTRIBUTE\$1NAMES applies recursively to any SUPER object that is nested inside a SUPER array or inside another object. **Using LOWER\$1ATTRIBUTE\$1NAMES on a SUPER object with duplicate attribute names** If a SUPER object contains attributes whose names differ only in their case, LOWER\$1ATTRIBUTE\$1NAMES 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. ``` # OBJECT function Creates an object of the SUPER data type. ## Syntax ``` OBJECT ( [ key1, value1 ], [ key2, value2 ...] ) ``` ## Arguments *key1, key2* Expressions that evaluate to VARCHAR type strings. *value1, value2* Expressions of any Amazon Redshift data type except datetime types, since Amazon Redshift doesn't cast datetime types to the SUPER data type. For more information on datetime types, see [Datetime types](r_Datetime_types.md). `value` expressions in an object don't need to be of the same data type. ## Return type `SUPER` ## Example ``` -- Creates an empty object. select object(); object -------- {} (1 row) -- Creates objects with different keys and values. select object('a', 1, 'b', true, 'c', 3.14); object --------------------------- {"a":1,"b":true,"c":3.14} (1 row) select object('a', object('aa', 1), 'b', array(2,3), 'c', json_parse('{}')); object --------------------------------- {"a":{"aa":1},"b":[2,3],"c":{}} (1 row) -- Creates objects using columns from a table. create table bar (k varchar, v super); insert into bar values ('k1', json_parse('[1]')), ('k2', json_parse('{}')); select object(k, v) from bar; object ------------ {"k1":[1]} {"k2":{}} (2 rows) -- Errors out because DATE type values can't be converted to SUPER type. select object('k', '2008-12-31'::date); ERROR: OBJECT could not convert type date to super ``` # OBJECT\$1TRANSFORM function Transforms a SUPER object. ## Syntax ``` OBJECT_TRANSFORM( input [KEEP path1, ...] [SET path1, value1, ..., ... ] ) ``` ## Arguments *input* An expression that resolves to a SUPER type object. *KEEP* All *path* values specified in this clause are kept and carried over to the output object. This clause is optional. *path1*, *path2*, ... Constant string literals, in the format of double-quoted path components delimited by periods. For example, `'"a"."b"."c"'` is a valid path value. This applies to the path parameter in both the KEEP and SET clauses. *SET* *path* and *value* pairs to modify an exiting path or add a new path, and set the value of that path in the output object. This clause is optional. *value1*, *value2*, ... Expressions that resolve to SUPER type values. Note that numeric, text, and Boolean types are resolvable to SUPER. ## Return type `SUPER` ## Usage notes OBJECT\$1TRANSFORM returns a SUPER type object containing the path values from *input* that were specified in KEEP and the *path* and *value* pairs that were specified in SET. If both KEEP and SET are empty, OBJECT\$1TRANSFORM returns *input*. If *input* isn’t a SUPER type *object*, OBJECT\$1TRANSFORM returns *input*, regardless of any KEEP or SET values. ## Example The following example transforms a SUPER object into another SUPER object. ``` CREATE TABLE employees ( col_person SUPER ); INSERT INTO employees VALUES ( json_parse(' { "name": { "first": "John", "last": "Doe" }, "age": 25, "ssn": "111-22-3333", "company": "Company Inc.", "country": "U.S." } ') ), ( json_parse(' { "name": { "first": "Jane", "last": "Appleseed" }, "age": 34, "ssn": "444-55-7777", "company": "Organization Org.", "country": "Ukraine" } ') ) ; SELECT OBJECT_TRANSFORM( col_person KEEP '"name"."first"', '"age"', '"company"', '"country"' SET '"name"."first"', UPPER(col_person.name.first::TEXT), '"age"', col_person.age + 5, '"company"', 'Amazon' ) AS col_person_transformed FROM employees; --This result is formatted for ease of reading. col_person_transformed ------------------------------------------------------------- { "name": { "first": "JOHN" }, "age": 30, "company": "Amazon", "country": "U.S." } { "name": { "first": "JANE" }, "age": 39, "company": "Amazon", "country": "Ukraine" } ``` # UPPER\$1ATTRIBUTE\$1NAMES function Converts all applicable attribute names in a SUPER value to uppercase, using the same case conversion routine as the [UPPER function](r_UPPER.md). UPPER\$1ATTRIBUTE\$1NAMES supports UTF-8 multibyte characters, up to a maximum of four bytes per character. To convert SUPER attribute names to lowercase, use the [LOWER\$1ATTRIBUTE\$1NAMES function](r_lower_attribute_names.md). ## Syntax ``` UPPER_ATTRIBUTE_NAMES( super_expression ) ``` ## Arguments *super\$1expression* A SUPER expression. ## Return type `SUPER` ## Examples **Converting SUPER attribute names to uppercase** The following example uses UPPER\$1ATTRIBUTE\$1NAMES 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 uppercase. UPDATE t SET s = UPPER_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 UPPER\$1ATTRIBUTE\$1NAMES functions. + NULL values and scalar SUPER values such as `"a"` are unchanged. + In a SUPER object, all attribute names are changed to uppercase, but attribute values such as `"b"` remain unchanged. + UPPER\$1ATTRIBUTE\$1NAMES applies recursively to any SUPER object that is nested inside a SUPER array or inside another object. **Using UPPER\$1ATTRIBUTE\$1NAMES on a SUPER object with duplicate attribute names** If a SUPER object contains attributes whose names differ only in their case, UPPER\$1ATTRIBUTE\$1NAMES will raise an error. Consider the following example. ``` SELECT UPPER_ATTRIBUTE_NAMES(JSON_PARSE('{"A": "A", "a": "a"}')); error: Invalid input code: 8001 context: SUPER value has duplicate attributes after case conversion. ```