OBJECT_TRANSFORM function - Amazon Redshift
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).

OBJECT_TRANSFORM 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.

Returns

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.

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