# Constructor functions
<a name="sql-functions-constructor"></a>

A SQL constructor function is a function that is used to create new data structures, such as arrays or maps.

 They take some input values and return a new data structure object. Constructor functions are typically named after the data type they create, such as ARRAY or MAP.

Constructor functions are different from scalar functions or aggregate functions, which operate on existing data and return a single value. Constructor functions are used to create new data structures that can then be used in further data processing or analysis.

AWS Clean Rooms supports the following constructor functions:

**Topics**
+ [MAP constructor function](map_function.md)
+ [NAMED\$1STRUCT constructor function](named-struct_function.md)
+ [STRUCT constructor function](struct_function.md)

# MAP constructor function
<a name="map_function"></a>

The MAP constructor function creates a map with the given key/value pairs.

Constructor functions like MAP are useful when you need to create new data structures programmatically within your SQL queries. They allow you to build complex data structures that can be used in further data processing or analysis. 

## Syntax
<a name="map_function-syntax"></a>

```
map(key0, value0, key1, value1, ...)
```

## Arguments
<a name="map_function-arguments"></a>

 *key0*   
An expression of any comparable type. All *key0* must share a least common type.

 *value0*   
An expression of any type. All *valueN* must share a least common type.

## Returns
<a name="map_function-returns"></a>

The MAP function returns a MAP with keys typed as the least common type of *key0* and values typed as the least common type of *value0*.

## Examples
<a name="map_function-examples"></a>

The following example creates a new map with two key-value pairs: The key `1.0` is associated with the value `'2'`. The key `3.0` is associated with the value `'4'`. The resulting map is then returned as the output of the SQL statement. 

```
SELECT map(1.0, '2', 3.0, '4');
 {1.0:"2",3.0:"4"}
```

# NAMED\$1STRUCT constructor function
<a name="named-struct_function"></a>

The NAMED\$1STRUCT constructor function creates a struct with the given field names and values.

Constructor functions like NAMED\$1STRUCT are useful when you need to create new data structures programmatically within your SQL queries. They allow you to build complex data structures, such as structs or records, that can be used in further data processing or analysis.

## Syntax
<a name="named-struct_function-syntax"></a>

```
named_struct(name1, val1, name2, val2, ...)
```

## Arguments
<a name="snamed-truct_function-arguments"></a>

 *name1*   
A STRING literal naming field 1.

 *val1*   
An expression of any type specifying the value for field 1.

## Returns
<a name="named-struct_function-returns"></a>

The NAMED\$1STRUCT function returns a struct with field 1 matching the type of *val1*. 

## Examples
<a name="named-struct_function-examples"></a>

The following example creates a new struct with three named fields: The field `"a"` is assigned the value `1`. The field `"b"` is assigned the value `2.` The field `"c"` is assigned the value `3`. The resulting struct is then returned as the output of the SQL statement. 

```
SELECT named_struct("a", 1, "b", 2, "c", 3);
 {"a":1,"b":2,"c":3}
```

# STRUCT constructor function
<a name="struct_function"></a>

The STRUCT constructor function creates a struct with the given field values.

Constructor functions like STRUCT are useful when you need to create new data structures programmatically within your SQL queries. They allow you to build complex data structures, such as structs or records, that can be used in further data processing or analysis.

## Syntax
<a name="struct_function-syntax"></a>

```
struct(col1, col2, col3, ...)
```

## Arguments
<a name="struct_function-arguments"></a>

 *col1*   
A column name or any valid expression.

## Returns
<a name="struct_function-returns"></a>

The STRUCT function returns a struct with *field1* matching the type of *expr1*.

If the arguments are named references, the names are used to name the field. Otherwise, the fields are named *colN*, where N is the position of the field in the struct.

## Examples
<a name="struct_function-examples"></a>

The following example creates a new struct with three fields: The first field is assigned the value 1. The second field is assigned the value 2. The third field is assigned the value 3. By default, the fields in the resulting struct are named `col1`, `col2`, and `col3`, based on their position in the argument list. The resulting struct is then returned as the output of the SQL statement.

```
SELECT struct(1, 2, 3);
 {"col1":1,"col2":2,"col3":3}
```