# Conditional & utility functions

Source: https://www.hotdata.dev/docs/sql-functions-conditional
Site index: https://www.hotdata.dev/llms.txt

Reference for conditional & utility functions in [HotSQL](/docs/sql). Names, signatures, and examples match the engine exactly.

### arrow_cast

Casts a value to a specific Arrow data type.

```
arrow_cast(expression, datatype)
```

**Arguments**

- `expression`: Expression to cast. The expression can be a constant, column, or function, and any combination of operators.
- `datatype`: [Arrow data type](https://docs.rs/arrow/latest/arrow/datatypes/enum.DataType.html) name to cast to, as a string. The format is the same as that returned by [`arrow_typeof`]

```sql
> select
  arrow_cast(-5,    'Int8') as a,
  arrow_cast('foo', 'Dictionary(Int32, Utf8)') as b,
  arrow_cast('bar', 'LargeUtf8') as c;

+----+-----+-----+
| a  | b   | c   |
+----+-----+-----+
| -5 | foo | bar |
+----+-----+-----+

> select
  arrow_cast('2023-01-02T12:53:02', 'Timestamp(µs, "+08:00")') as d,
  arrow_cast('2023-01-02T12:53:02', 'Timestamp(µs)') as e;

+---------------------------+---------------------+
| d                         | e                   |
+---------------------------+---------------------+
| 2023-01-02T12:53:02+08:00 | 2023-01-02T12:53:02 |
+---------------------------+---------------------+
```

### arrow_field

Returns a struct containing the Arrow field information of the expression, including name, data type, nullability, and metadata.

```
arrow_field(expression)
```

**Arguments**

- `expression`: Expression to evaluate. The expression can be a constant, column, or function, and any combination of operators.

```sql
> select arrow_field(1);
+-------------------------------------------------------------+
| arrow_field(Int64(1))                                       |
+-------------------------------------------------------------+
| {name: lit, data_type: Int64, nullable: false, metadata: {}} |
+-------------------------------------------------------------+

> select arrow_field(1)['data_type'];
+-----------------------------------+
| arrow_field(Int64(1))[data_type]  |
+-----------------------------------+
| Int64                             |
+-----------------------------------+
```

### arrow_metadata

Returns the metadata of the input expression. If a key is provided, returns the value for that key. If no key is provided, returns a Map of all metadata.

```
arrow_metadata(expression[, key])
```

**Arguments**

- `expression`: The expression to retrieve metadata from. Can be a column or other expression.
- `key`: Optional. The specific metadata key to retrieve.

```sql
> select arrow_metadata(col) from table;
+----------------------------+
| arrow_metadata(table.col)  |
+----------------------------+
| {k: v}                     |
+----------------------------+
> select arrow_metadata(col, 'k') from table;
+-------------------------------+
| arrow_metadata(table.col, 'k')|
+-------------------------------+
| v                             |
+-------------------------------+
```

### arrow_try_cast

Casts a value to a specific Arrow data type, returning NULL if the cast fails.

```
arrow_try_cast(expression, datatype)
```

**Arguments**

- `expression`: Expression to cast. The expression can be a constant, column, or function, and any combination of operators.
- `datatype`: [Arrow data type](https://docs.rs/arrow/latest/arrow/datatypes/enum.DataType.html) name to cast to, as a string. The format is the same as that returned by [`arrow_typeof`]

```sql
> select arrow_try_cast('123', 'Int64') as a,
         arrow_try_cast('not_a_number', 'Int64') as b;

+-----+------+
| a   | b    |
+-----+------+
| 123 | NULL |
+-----+------+
```

### arrow_typeof

Returns the name of the underlying [Arrow data type](https://docs.rs/arrow/latest/arrow/datatypes/enum.DataType.html) of the expression.

```
arrow_typeof(expression)
```

**Arguments**

- `expression`: Expression to evaluate. The expression can be a constant, column, or function, and any combination of operators.

```sql
> select arrow_typeof('foo'), arrow_typeof(1);
+---------------------------+------------------------+
| arrow_typeof(Utf8("foo")) | arrow_typeof(Int64(1)) |
+---------------------------+------------------------+
| Utf8                      | Int64                  |
+---------------------------+------------------------+
```

### cast_to_type

Casts the first argument to the data type of the second argument. Only the type of the second argument is used; its value is ignored.

```
cast_to_type(expression, reference)
```

**Arguments**

- `expression`: The expression to cast. It can be a constant, column, or function, and any combination of operators.
- `reference`: Reference expression whose data type determines the target cast type. The value is ignored.

```sql
> select cast_to_type('42', NULL::INTEGER) as a;
+----+
| a  |
+----+
| 42 |
+----+

> select cast_to_type(1 + 2, NULL::DOUBLE) as b;
+-----+
| b   |
+-----+
| 3.0 |
+-----+
```

### coalesce

Returns the first of its arguments that is not _null_. Returns _null_ if all arguments are _null_. This function is often used to substitute a default value for _null_ values.

```
coalesce(expression1[, ..., expression_n])
```

**Arguments**

- `expression1, expression_n`: Expression to use if previous expressions are _null_. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.

### get_field

Returns a field within a map or a struct with the given key.
    Supports nested field access by providing multiple field names.
    Note: most users invoke `get_field` indirectly via field access
    syntax such as `my_struct_col['field_name']` which results in a call to
    `get_field(my_struct_col, 'field_name')`.
    Nested access like `my_struct['a']['b']` is optimized to a single call:
    `get_field(my_struct, 'a', 'b')`.

```
get_field(expression, field_name[, field_name2, ...])
```

**Arguments**

- `expression`: The map or struct to retrieve a field from.
- `field_name`: The field name(s) to access, in order for nested access. Must evaluate to strings.

```sql
> -- Access a field from a struct column
> create table test( struct_col) as values
    ({name: 'Alice', age: 30}),
    ({name: 'Bob', age: 25});
> select struct_col from test;
+-----------------------------+
| struct_col                  |
+-----------------------------+
| {name: Alice, age: 30}      |
| {name: Bob, age: 25}        |
+-----------------------------+
> select struct_col['name'] as name from test;
+-------+
| name  |
+-------+
| Alice |
| Bob   |
+-------+

> -- Nested field access with multiple arguments
> create table test(struct_col) as values
    ({outer: {inner_val: 42}});
> select struct_col['outer']['inner_val'] as result from test;
+--------+
| result |
+--------+
| 42     |
+--------+
```

### greatest

Returns the greatest value in a list of expressions. Returns _null_ if all expressions are _null_.

```
greatest(expression1[, ..., expression_n])
```

**Arguments**

- `expression1, expression_n`: Expressions to compare and return the greatest value.. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.

```sql
> select greatest(4, 7, 5);
+---------------------------+
| greatest(4,7,5)           |
+---------------------------+
| 7                         |
+---------------------------+
```

### least

Returns the smallest value in a list of expressions. Returns _null_ if all expressions are _null_.

```
least(expression1[, ..., expression_n])
```

**Arguments**

- `expression1, expression_n`: Expressions to compare and return the smallest value. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.

```sql
> select least(4, 7, 5);
+---------------------------+
| least(4,7,5)              |
+---------------------------+
| 4                         |
+---------------------------+
```

### nullif

Returns _null_ if _expression1_ equals _expression2_; otherwise it returns _expression1_.
This can be used to perform the inverse operation of [`coalesce`](#coalesce).

```
nullif(expression1, expression2)
```

**Arguments**

- `expression1`: Expression to compare and return if equal to expression2. Can be a constant, column, or function, and any combination of operators.
- `expression2`: Expression to compare to expression1. Can be a constant, column, or function, and any combination of operators.

### nvl

Returns _expression2_ if _expression1_ is NULL otherwise it returns _expression1_ and _expression2_ is not evaluated. This function can be used to substitute a default value for NULL values.

```
nvl(expression1, expression2)
```

**Arguments**

- `expression1`: Expression to return if not null. Can be a constant, column, or function, and any combination of operators.
- `expression2`: Expression to return if expr1 is null. Can be a constant, column, or function, and any combination of operators.

```sql
> select nvl(null, 'a');
+---------------------+
| nvl(NULL,Utf8("a")) |
+---------------------+
| a                   |
+---------------------+\
> select nvl('b', 'a');
+--------------------------+
| nvl(Utf8("b"),Utf8("a")) |
+--------------------------+
| b                        |
+--------------------------+
```

### nvl2

Returns _expression2_ if _expression1_ is not NULL; otherwise it returns _expression3_.

```
nvl2(expression1, expression2, expression3)
```

**Arguments**

- `expression1`: Expression to test for null. Can be a constant, column, or function, and any combination of operators.
- `expression2`: Expression to return if expr1 is not null. Can be a constant, column, or function, and any combination of operators.
- `expression3`: Expression to return if expr1 is null. Can be a constant, column, or function, and any combination of operators.

```sql
> select nvl2(null, 'a', 'b');
+--------------------------------+
| nvl2(NULL,Utf8("a"),Utf8("b")) |
+--------------------------------+
| b                              |
+--------------------------------+
> select nvl2('data', 'a', 'b');
+----------------------------------------+
| nvl2(Utf8("data"),Utf8("a"),Utf8("b")) |
+----------------------------------------+
| a                                      |
+----------------------------------------+
```

### try_cast_to_type

Casts the first argument to the data type of the second argument, returning NULL if the cast fails. Only the type of the second argument is used; its value is ignored.

```
try_cast_to_type(expression, reference)
```

**Arguments**

- `expression`: The expression to cast. It can be a constant, column, or function, and any combination of operators.
- `reference`: Reference expression whose data type determines the target cast type. The value is ignored.

```sql
> select try_cast_to_type('123', NULL::INTEGER) as a,
         try_cast_to_type('not_a_number', NULL::INTEGER) as b;

+-----+------+
| a   | b    |
+-----+------+
| 123 | NULL |
+-----+------+
```

### version

Returns the engine version string.

```
version()
```

### with_metadata

Attaches Arrow field metadata (key/value pairs) to the input expression. Keys must be non-empty constant strings and values must be constant strings (empty values are allowed). Existing metadata on the input field is preserved; new keys overwrite on collision. This is the inverse of `arrow_metadata`.

```
with_metadata(expression, key1, value1[, key2, value2, ...])
```

**Arguments**

- `expression`: The expression whose output Arrow field should be annotated. Values flow through unchanged.
- `key`: Metadata key. Must be a non-empty constant string literal.
- `value`: Metadata value. Must be a constant string literal (may be empty).

```sql
> select arrow_metadata(with_metadata(column1, 'unit', 'ms'), 'unit') from (values (1));
+---------------------------------------------------------------+
| arrow_metadata(with_metadata(column1,Utf8("unit"),Utf8("ms")),Utf8("unit")) |
+---------------------------------------------------------------+
| ms                                                            |
+---------------------------------------------------------------+
> select arrow_metadata(with_metadata(column1, 'unit', 'ms', 'source', 'sensor')) from (values (1));
+--------------------------+
| {source: sensor, unit: ms} |
+--------------------------+
```