## Related terms

- ai\-recommendation
- aggregate
- segmentation
- expression
- attribute

See the [glossary](/glossary.md) for definitions.

---

# Functions


Functions allow you to perform some additional operations when building the IQL query string.

You can use functions inside functions, for example:
```plaintext
effectivePrice.value <= MULTIPLY(MIN(context.effectivePrice.value), 1.2)
```
In the above example:
1. `(MIN(context.effectivePrice.value)` gets the lowest value of an attribute from the context items.
2. `MULTIPLY(...), 1.2` multiplies that lowest value by `1.2`.
3. The resulting filter matches items whose `effectivePrice.value` is at least 20% higher than in the cheapest context item.

## CATEGORY {#category-function}

The `CATEGORY` function allows you to:
- access categories from the context
- access context attributes that are formatted as categories, but not named `category` (for example, a custom `favoriteCategory` attribute in a profile)
- manipulate category levels

The function takes two arguments:
- the source of the category value
- how many category levels you want to drop or include (`0` does not drop any levels).  
    Positive numbers **drop** levels from the bottom, negative numbers **include** from the top.  

**Example 1**:  
Accessing category from the item context and dropping one level from the bottom (right):
```plaintext
category == CATEGORY(context.category, 1)
```
If the context category is `X > Y > Z` the result of the function is the following filter:
```plaintext
category == "X > Y"
```

**Example 2**:  
Accessing category from the item context including two levels from the top (left):
```plaintext
category == CATEGORY(context.category, -1)
```
If the context category is `X > Y > Z` the result of the function is the following filter:
```plaintext
category == "X"
```

**Example 3**:  
Adding an [OR statement](/developers/iql/logic#or) and the `additionalCategories` attribute, no levels dropped:
```plaintext
category == CATEGORY(context.category, 0) OR category == CATEGORY(context.additionalCategories, 0)
```

**Example 4**:  
Accessing a category saved as a custom profile attribute `"favoriteCategory": "foo > bar"`:
```plaintext
category == CATEGORY(client.attributes.favoriteCategory, 0)
```
The resulting filter is:
```plaintext
category == "foo > bar"
```

## Check if value is null

The `IS DEFINED` operator allows you to check if an attribute exists and has a non-null value.

For example, the following filter matches items in which `winterPromotion` has a non-null value: 

```plaintext
winterPromotion IS DEFINED
```
You can also check contexts:
```plaintext
IF(context.thisAttributeDoesNotExist IS DEFINED, discount > 0, discount == 0)
```

## TO_ARRAY
If your filters use an aggregate that returns a list of values, you must run the result of that aggregate through the `TO_ARRAY` function.

This is because Synerise aggregates return arrays as strings: `"["foo","bar"]"`

```plaintext
brand IN TO_ARRAY(client.aggregates.35bb6e95-443a-3303-af07-cacbb063acfe)
```

## REQUIRED
Returns an error instead of null when a profile context is unavailable. Recommendations in the affected slot are not displayed at all. You can use this to make sure that recommendations are only displayed when the context is available.

Can be used with these context elements:
- aggregates  
    Example: `brand IN TO_ARRAY(REQUIRED(client.aggregates.17c9339d-c938-4059-9bfc-f990d64d3501))`
- expressions  
    Example: `price.value > REQUIRED(client.expressions.UUID)`
- segmentations  
    ```IF(REQUIRED(client.segmentations) HAS "8c1b3540-2a7f-11ec-8d3d-0242ac130003", flag == "premium", flag == "regular")```
- profile attribute  
    Example: `brand == REQUIRED(client.attributes.favouriteBrand)`
- profile tags  
    Example: `tag IN REQUIRED(client.tags)`

## Numeric functions

You can perform mathematical operations on item context and profile context attributes.

### MIN

Returns the lowest value from an array.
```plaintext
attributeName >= MIN([])
```
**Example**:  
In the following example, the context is a few items with a `size.width` attribute:
```plaintext
size.width >= MIN(context.size.width)
```
The resulting filter is:
```plaintext
size.width >= MIN([10.0,14.0,20.0])
```
which calculates into:
```plaintext
size.width >= 10.0
```
### MAX

Returns the highest value from an array.
```plaintext
attributeName >= MAX([])
```
**Example**:  
In the following example, the context is a few items with a `size.width` attribute:
```plaintext
size.width >= MAX(context.size.width)
```
The resulting filter is:
```plaintext
size.width >= MAX([10.0,14.0,20.0])
```
which calculates into:
```plaintext
size.width >= 20.0
```

### AVG

Returns the average value of an array.
```plaintext
attributeName >= AVG([])
```
**Example**:  
In the following example, the context is 3 items with a `size.width` attribute:
```plaintext
size.width >= AVG(context.size.width)
```
The resulting filter is:
```plaintext
size.width >= AVG([10.0,14.0,20.0])
```
which calculates into:
```plaintext
size.width >= 14.666666666666666
```

### SUM

Returns the sum of all elements in an array. All elements must be static numbers or a context that returns an array of numbers.
```plaintext
attributeName == SUM([])
```
In the following example, the context is 3 items with a `regularPrice` attribute.
```plaintext
price > SUM(context.regularPrice)
```
The resulting filter is:
```plaintext
price > SUM([10.0,15.65,14.30])
```
which calculates into:
```plaintext
price > 39.95
```

### ADD

Adds one value to one other value. If you want to add more values at once, use [`SUM`](#sum). 
Only one of the arguments can be a value from a context (item or profile context), the other must be a static number.
```plaintext
attributeName == ADD(value,number)
```
**Example 1**:  
In the following example, the context is one item with `"size": 10`:
```plaintext
size > ADD(context.size,5)
```
The resulting filter is:
```plaintext
size > ADD(10.0,5.0)
```
which evaluates to:
```plaintext
size > 15
```

### MULTIPLY
Multiplies values.  
The first argument is a context attribute or a number, the second attribute is a number.
```plaintext
attributeName == MULTIPLY(value,number)
```

**Example**:  
In the following example, the context is one item with `"price.value": 5`:
```plaintext
price.value > MULTIPLY(context.price.value,0.7)
```
The resulting filter is:
```plaintext
price.value > MULTIPLY(5.0,0.7)
```
which evaluates to:
```plaintext
price.value > 3.5
```

## TOP/BOTTOM values

The `TOP_K` and `BOTTOM_K` functions let you retrieve a number of items with the top/bottom values of an attribute. You can add a filter to the items.

**Syntax**:  
```plaintext
TOP_K(value, attribute, filter)
BOTTOM_K(value, attribute, filter)
```
where:
- `value` is the number of items to retrieve
- `attribute` is the tested attribute
- `filter` is a filter string

**Example 1:**  
Retrieve 10 items with the lowest prices:
```plaintext
BOTTOM_K(10, price.value , ALL)
```

**Example 2:**  
Retrieve 10 red items with the highest prices:
```plaintext
TOP_K(10, price.value , color == red)
```

**Example 3:**  
Retrieve 10 available items with the highest [value of a metric](/developers/iql/filters#metrics):
```plaintext
TOP_K(10, extra.metrics.9 , availability == true)
```

## Time functions
These functions let you use dates and times in a filter.

### TIMESTAMP
Converts a date-time string or a timestamp (string) into a timestamp (integer).

**Syntax**:  
```plaintext
TIMESTAMP(string)
```
where `string` can be:
- an ISO 8601 date-time string, for example `2024-01-01T10:00:00Z`  
    If the string doesn't declare a timezone, the timezone of the workspace is applied. `Z` declares UTC as the timezone.
- a timestamp in seconds as a string, for example `1727337789`

The result is a timestamp as an integer, in seconds.

### NOW
Gets current time as a timestamp (integer) in seconds.

**Syntax**:  
```plaintext
NOW()
```
The function doesn't have any arguments.

### DATE_ADD
Adds or subtracts from a date-time (string).

**Syntax**:
```plaintext
DATE_ADD(unit, number, time)
```
where:
- `unit` is one of: `minutes`, `hours`, `days`, `months`, `years`  
    This argument is NOT case-sensitive. `months` takes into account the different number of days each month and `years` handles leap years.
- `number` is the value to add.  
    Negative values result in subtraction.
- `time` is an ISO 8601 date-time string, a timestamp in seconds (as string or integer), or the `NOW()` function.  
    If date-time doesn't declare a timezone, the timezone of the workspace is applied. `Z` declares UTC as the timezone.

**Examples**:  
- Add 31 days to a fixed date:
    ```
    DATE_ADD("days", 31, "2024-01-01T10:00:00Z")
    ```
    ```
    DATE_ADD("days", 31, "1704099600")
    ```
    ```
    DATE_ADD("days", 31, 1704099600)
    ```
- Subtract 3 months from current time:
    ```
    DATE_ADD("MONTHS", -3, NOW())
    ```

## ALL/NONE

This special function lets include or exclude all items from the results.

- `ALL` - take all items
- `NONE` - take no items

This is especially useful in the [IF statement](/developers/iql/logic#if).