OBJECT_TRANSFORM function - Amazon Redshift


Transforms a SUPER object.


OBJECT_TRANSFORM( input [KEEP path1, ...] [SET path1, value1, ..., ... ] )



An expression that resolves to a SUPER type object.


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.


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


Usage notes

OBJECT_TRANSFORM 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_TRANSFORM returns input.

If input isn’t a SUPER type object, OBJECT_TRANSFORM returns input, regardless of any KEEP or SET values.


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" }