Get our latest book on "Top 10 artificial intelligence myths."
Download

Last updated

March 4, 2021

Table of contents

IQL functions

IQL has a rich library of functions, which are inspired in name and functionality by Excel, Google Sheets, and SQL. We're constantly adding to this list of functions, so don't hesitate to get in touch with us if there's a function that you'd like to use.

You can use these functions as part of an IQL query or read API request. If you're just getting started, be sure to hop over and check out How to use Impira Query Language (IQL).

Type conversion functions

to_bool

`to_bool(expr)`

Casts `expr` to a `BOOLEAN` type.

```language="js"
@`files`[to_bool(`Uploaded`>04-01-2021)]
```

This example returns a boolean for whether files were `Uploaded` after April 1st 2021.

to_number

`to_number(expr)`

Casts `expr` to a `NUMBER` type. Timestamps casted to numbers return the UNIX timestamp representation (seconds since the epoch).

```language="js"
@`files`[to_number(`Uploaded`)]
```

This example returns each value `Uploaded` cast to a number (UNIX timestamp).

to_string

`to_string(expr)`

Casts `expr` to a `STRING` type.

```language="js"
@`files`[to_string(`File size`)]
```

This example takes `File size` and casts it from a `NUMBER` to a `STRING` type.

to_timestamp

`to_timestamp(expr)`

Casts `expr` to a `TIMESTAMP` type.  A `NUMBER` cast to a `TIMESTAMP` is assumed to be a `UNIX` timestamp.

```language="js"
@`files`[to_timestamp('April 23 2021')]
```

This example takes a `STRING` and converts it to a `TIMESTAMP` type.

String functions

concat

`concat(arg1, arg2, arg3, ...)`

Concatenates to a `STRING` all of the arguments passed to the function.

```language="js"
@`file_collections::<uid>`[`Full Name`: concat(`First Name`, ' ', `Last Name`)]
```
This example concatenates the `First Name` and `Last Name` fields, separated by a space to a new field called "Full Name."

textjoin

`textjoin(delimiter, string_list)`

Joins together the strings in `string_list` with the `delimiter`.

```language="js"
@`files`[textjoin(' ', File.text.words.word)]
```
This example takes the `string_list` of OCR words, and joins them with a `' '` (space).

upper

`upper(text)`

Convert `text` to upper case.

```language="js"
@`files`[upper("The quick brown fox")] limit:1
```
This example converts the input string to upper case: `THE QUICK BROWN FOX`.

lower

`lower(text)`

Convert `text` to lower case.

```language="js"
@`files`[lower("The QUICK Brown Fox", 10)] limit:1
```
This example converts the input string to lower case: `the quick brown fox`.

left

`left(text, [num_chars])`

Extract the text from the left of the string. `num_chars` defaults to 1 if not provided.

```language="js"
@`files`[left("The quick brown fox", 10)] limit:1
```
This example takes the first 10 chars from the left of the input string and returns `The quick`.

right

`right(text, [num_chars])`

Extract the text from the left of the string. `num_chars` defaults to 1 if not provided.

```language="js"
@`files`[right("The quick brown fox", 10)] limit:1
```
This example takes the first 10 chars from the left of the input string and returns `brown fox`.

mid

`mid(text, start, num_chars)`

Extracts the text starting from `start` characters (indexed by 1) up to `num_chars` characters.

```language="js"
@`files`[mid("The quick brown fox", 5, 11)] limit:1
```
This example starts 5 chars from the start (`q`), reads 11 chars, and returns `brown fox`.

strip

`strip(text)`

Removes leading and trailing whitespace from `text`. This is similar to the `strip()` function in Python.

```language="js"
@`files`[strip(" The quick brown fox   ")] limit:1
```
This example removes leading/trailing whitespace and returns `The quick brown fox`.

trim

`trim(text)`

Removes all spaces in `text`, leaving just a single space between words. Like `strip`, `trim` removes leading/trailing whitespace, but also cleans up the spaces within the string.

```language="js"
@`files`[trim(" \\n The  quick\\tbrown\\t\\tfox  ")] limit:1
```
This example returns `The quick brown fox`.

proper

`proper(text)`

Convert `text` to title case, where all words begin with a capital letter.

```language="js"
@`files`[proper("The quick brown fox")] limit:1
```
This example converts the input string to title case and returns `The Quick Brown Fox`.

sentence

`sentence(text)`

Convert `text` to sentence case, where most words are lowercased and the first word in a sentence is capitalized.

```language="js"
@`files`[sentence("the QUICK brown FOX")] limit:1
```
This example converts the input string to sentence case and returns `The quick brown fox`.

split

`split(text, separator)`

Split the input `text` using the specified `separator` and returns an array of the parts.

```language="js"
@`files`[split(File.name, '-')]
```
This example will split `[File.name](<http://file.name>)' using a hyphen `-` as a separator.

stem

`stem(text)`

Takes the input `text` and returns a stemmed version of the words.

```language="js"
@`files`[ stem("disappeared") ] limit:1
```
This example takes the word `disappeared` and returns the root stem of the word,  `disappear`.

clean_stopwords

`clean_stopwords(text)`

Remove `stopwords` (commonly used words, for example: I, we, you, me, them, their, the) that are filtered from the input `text`. This can be useful when searching across OCR keywords.

```language="js"
@`files`[clean_stopwords("The quick brown fox jumps over the lazy brown dog")] limit:1
```
The above example filters out the `stopwords` from the input string and returns `quick brown fox lazy brown dog`.

Conditional functions

coalesce

`coalesce(arg1, arg2, arg3, ...)`

Returns the first non-null argument. All arguments must be either scalar or objects with the same structure. Coalesce is useful for combining fields where some values may be null.

```language="js"
@`files`[coalesce(`Source name`, `Source type`)]
```
This example will return the `Source name` if it is non-`NULL`. Otherwise, it returns `Source type`.

if

`if(condition, trueCase, falseCase)`

Returns `trueCase` if `condition == true`, else returns `falseCase`.

```language="js"
@`files`[if(`Uploaded`>2021, "Latest and greatest", "Pre 2021")]
```
This example checks if a `File` was `Uploaded` in or after the year 2021. It returns either `Latest` and `greatest` if true, and otherwise returns `Pre 2021`.

_and

`_and(x, y)`

`AND`'s together two values. This is the equivalent to using `x and y`. Evaluates to `NULL` if any of the values are `NULL`.

```language="js"
@`files`[`Image uploaded via browser`: _and(`File type`='Image', `Source type`='Browser upload')]
```
This example returns the true if expressions `x` and `y` both evaluate as `true`. We're checking for whether a `File type` is an Image and the `Source type` is Browser upload.

_or

`_or(x, y)`

`OR`'s together two values. Evaluates to `NULL` if any of the values are `NULL`.

```language="js"
@`files`[`Valid`: _or(`File type`='Image', `File type`='Document')]
```
This example returns `true` if either the `File` is an Image `OR` a Document.

eq (or equals)

`eq(x, y)`

Compares `x` with `y` and returns a `boolean` as a result. This function works with all input types (STRING, NUMBER, TIMESTAMP, BOOLEAN, ENTITY).

```language="js"
@`files`[`Is Document`: eq(`File type`, 'Document')]
```
This example compares the `File type` with the value of the second parameter `Document` and returns a `boolean` for each File.

in

`in(val, x, y, ...)`

Compares `val` with the remaining values (`x`, `y`, and so on) and returns a `boolean` if `val` equals any of them. This is semantically the same as `(val=x) or (val=y) ....` This function works with all input types (STRING, NUMBER, TIMESTAMP, BOOLEAN, ENTITY).

```language="js"
@`files`[`Is Media`: in(`File type`, 'Document', 'Image')]
```
This example compares the `File type` with `'Document'` and `'Image'` and returns a `boolean` for each File that matches either.

in_list

`in_list(val, list)`

Compares `val` with the values in `list` and returns a `boolean` if `val` equals any of them. This is semantically the same as `in(val, index(list, 0), index(list, 1), ...)`. This function works with all input types (STRING, NUMBER, TIMESTAMP, BOOLEAN, ENTITY).

```language="js"
@`files`[`media_types`: build_array('Document', 'Image'), `Is Media`: in_list(`File type`, `media_types`)]
```
This example compares the `File type` with each value in the `media_types` list and returns a `boolean` for each File that matches either.

not

`not(expr)`

Returns a boolean for the opposite of the input expression when the expression evaluates as a boolean. If a binary number `0` or `1` is entered as the expression they are treated as boolean values.

```language="js"
@`files`[`Not a Document`: not(eq(`File type`, 'Document'))]
```
This example returns `true` for all Files that are `NOT` a `Document`.

Date and time functions

year

`year(timestamp)`

Returns the `timestamp` truncated to the year.

```language="js"
@`files`[year(`Uploaded`)] limit:1
```
This example returns the year part of `Uploaded` date timestamp.

month

`month(timestamp)`

Returns the timestamp truncated to the month.

```language="js"
@`files`[month(`Uploaded`)] limit:1
```
This example returns the month part of Uploaded date timestamp.

week

`week(timestamp)`

Returns the `timestamp` truncated to the week.

```language="js"
@`files`[week(`Uploaded`)] limit:1
```
This example returns the week part of `Uploaded` date timestamp.

day

`day(timestamp)`

Returns the timestamp truncated to the day.

```language="js"
@`files`[day(`Uploaded`)] limit:1
```
This example returns the day part of Uploaded date timestamp.

hour

`hour(timestamp)`

Returns the `timestamp` truncated to the hour.

```language="js"
@`files`[hour(`Uploaded`)] limit:1
```
This example returns the hour part of Uploaded date timestamp.

minute

`minute(timestamp)`

Returns the `timestamp` truncated to the minute.

```language="js"
@`files`[minute(`Uploaded`)] limit:1
```
This example returns the minute part of `Uploaded` date timestamp.

second

`second(timestamp)`

Returns the `timestamp` truncated to the second.

```language="js"
@`files`[second(`Uploaded`)] limit:1
```
This example returns the second part of Uploaded date timestamp.

add_interval

`add_interval(timestamp, amount, timestamp_part)`

Add the number specified by `amount` to the `timestamp_part` of the input `timestamp`. `timestamp_part` can be one of `year`, `month`, `day`, `hour`, `minute`, `second`.

```language="js"
@`files`[add_interval(`Uploaded`, 1, 'month')] limit:1
```
This example increments the `month` part of the `Uploaded` timestamp by 1.

sub_interval

`sub_interval(timestamp, amount, part)`

Subtract the number specified by `amount` from the `part` of the input `timestamp`. `part` can be one of `year`, `month`, `day`, `hour`, `minute`, `second`.

```language="js"
@`files`[sub_interval(`Uploaded`, 1, 'month')] limit:1
```
This example subtracts `1` `month` from the `Uploaded` timestamp.

datedif

`datedif(timestamp1, timestamp1, part)`

Compares the difference of the specified timestamp `part` between the two `timestamp`'s. `part` can be one of `year`, `month`, `day`, `hour`, `minute`, `second`.

```language="js"
@`files`[datedif(`Uploaded`, now(), 'day')] limit:1
```
This example compares how many `day`'s difference there are between the `Uploaded` date and `now()`.

Array and entity functions

array_agg

`array_agg(expr, [sort_expr])`

An aggregate function used to return an array from values evaluated by the input expression `expr`. An optional second param `sort_expr` can be specified to sort the results. Results are:

```language="js"
@`files`[`File type`, array_agg(File.name, Uploaded)] rollup:true
```
This example returns an array of `[File.name](<http://file.name>)`, sorted by `Uploaded` date, grouped by `File type`.

array_to_object

`array_to_object(field)`

Given a field of an object that is part of a list, `array_to_object` will construct a new object where the keys are the values of `field` in the original objects in the list, and the values are the objects themselves.

```language="js"
@`files`[array_to_object(`array_field`.`id`)]
```
If `array_field = [{"id": "a1", "name": "Machine"},{"id": "a2", "name": "Learning"}]`, then the above example would output `{"a1": {"id": "a1", "name": "Machine"}, "a2": {"id": "a2", "name": "Learning"}}`.

array_window

`array_window(array, lag, lead)`

For each value in the `array` returns the number of lagging and leading values specified by `lag` and `lead` (the values before and after each value in the array).

```language="js"
@`files`[array_window(build_array(1,2,3,4,5,6,7,8,9,10), 2, 2)] limit:1
```
This example will show the `2` values before and after each item in the array.

build_array

`build_array(arg1, arg2, arg3, ...)`

Constructs an array with the provided arguments. The arguments must be either all entities or all non-entities. `build_array()` with no arguments returns an empty array.

```language="js"
@`files`[build_array(`File size`, 2, 3)] limit:1
```
This example constructs the array `[File size, 2, 3]`.

build_entity

`build_entity(field1, value1, field2, value2, ...)`

Constructs an object with the provided arguments. Arguments should be provided in pairs, where the first argument in the pair is the field name and the second is the value.

```language="js"
@`files`[build_entity("File name", File.name, "Download url", File.download_url)] limit:1
```
This example constructs an object that looks like:

```language="js"
{"File name": "my_file.pdf", "Download url": "<http://url.com/file.pdf>"}
```

concat_array

`concat_array(array1, array2, array3,...)`

Concatenates and flattens multiple arrays into a single array.

```language="js"
@`files`[concat_array(build_array(1,2,3), build_array(4,5,6))] limit:1
```
This example takes two hypothetical arrays and joins them into a single array `[1,2,3,4,5,6]`.

index

`index(array, idx)`

Returns the value at the `idx` position (zero-indexed) of the `array`. If `idx` is out of bounds, the function returns `null`.

```language="js"
@`files`[index(build_array("Impira", "Auto", "ML"), 0)] limit:1
```
This example returns the first element of the array at `idx` 0 which is `Impira`.

merge

`merge(obj1, obj2, obj3, ...)`

Merges the input objects together into a single object. `merge` ignores fields in objects with `null` values.

```language="js"
@`files`[ a:{"name": "Impira"}, b:{"solution": "AutoML"}, c:merge(a, b)] limit:1
```
This example merges the inline objects `a` and `b` into a single object, `c`.

merge_many

`merge_many(objArray1, objArray2, objArray3, ...)`

Merges and flattens lists of objects together into a single object. `merge_many` ignores empty arrays.

```language="js"
@`files`[ a: build_array({"foo", 1}), b: build_array({"bar", 2}), c: merge_many(a,b)] limit:1
```
This example takes the arrays `a` and `b` and merges the the contents into a single object `c`.

merge_unnest

`merge_unnest(obj, array)`

This function is used to expand an `array` into a set of rows, and merge each row with the same `obj`.

```language="js"
@`files`[merge_unnest({file_id: File.file_id}, File.text.words[word])] limit:1
```
This example un-nests the array of OCR words available at `File.text.words` and merges each individual `word` with the `obj` defined as `{file_id: File.file_id}`. The result is an array of new objects that look like `{"file_id": 12345, "word": "Impira"}`.

strip_nulls

`strip_nulls(obj)`

This function looks at all key value pairs anywhere in the hierarchy of the object `obj` and returns an entity where each pair whose value was `null` has been removed.

```language="js"
@`files`[my_object: {"id": 1, "solution": "Impira", "optional": null}, strip_nulls(my_object)] limit:1
```
This example creates an inline object `my_object` with the key `optional` set to `null`. The `strip_nulls` function will remove the `optional` key from the entity, and will return `{"id":1, "solution": "Impira"}`

Regular expression functions

regexextract

`regexextract(str, reg)`

Returns the left-most match of regular expression `reg` on string `str`. Returns `null` if there is no match.

```language="js"
@`files`[regexextract(File.name, '[^.]+$')] limit:1
```
This example extracts anything after the last period in the `File.name`. This is a simple example of extracting the file extension from the filename.

regexmatch

`regexmatch(str, reg)`

Returns `true` if `str` matches the regular expression `reg`. Returns `null` if there is no match.

```language="js"
@`files`[regexmatch(File.name, '^(abc|ABC)')] limit:1
```
This example returns `true` if the `[File.name](<http://file.name>)` begins with `abc` or `ABC`.

regexreplace

`regexreplace(str, reg, replacement)`

Replaces matches of the regular expression `reg` in `str` with `replacement`.  `regexreplace` also supports capture groups using parenthesis `()` and `\1`, `\2`, `\3` , etc., for group numbers.

```language="js"
@`files`[regexreplace(`Uploaded`, "(\\d{4}).(\\d{2}).(\\d{2})", "")] limit:1
```
This example replaces the date part of the `Uploaded` date with an empty string, leaving only the time part of the timestamp.

Mathematical functions

add

`add(expr1, expr2)`

Adds the values of expression `expr1` plus expression `expr2`.  Expressions must evaluate to a `NUMBER` else `add` will return `NULL`.

```language="js"
@`files`[add(1, 2)] limit:1
```
This example adds the values `1` and `2`.

sub

`sub(expr1, expr2)`

Subtracts the value of expression `expr1` from the value of expression `expr2`.

```language="js"
@`files`[sub(2, 1)] limit:1
```
This example subtracts `1` from `2`.

mul

`mul(expr1, expr2)`

Multiplies the value of expression `expr1` by the value of expression `expr2`.

```language="js"
@`files`[mul(2, 5)] limit:1
```
This example multiples the value `2` by `5`.

div

`div(expr1, expr2)`

Divides the value of expression `expr1` by the value of expression `expr2`.

```language="js"
@`files`[div(10, 5)] limit:1
```
This example can be read as `10` divided by `5`.

least

`least(expr1, expr2, expr3 ...)`

Returns the lowest value of all of the the calculated expressions passed to the function.

```language="js"
@`files`[least(1, 2, 3, 0)] limit:1
```
This example returns the lowest value of `1, 2, 3, 0`  which is `0`.

greatest

`least(expr1, expr2, expr3 ...)`

Returns the highest value of all of the the calculated expressions passed to the function.

```language="js"
@`files`[greatest(1, 2, 3, 0)] limit:1
```
This example returns the lowest value of `1, 2, 3, 0`  which is `3`.

round

`round(expr)`

Rounds the value of the expression `expr` to the nearest 2 decimal places.

Aggregate functions

count

`count(expr)`

Aggregates by counting the number of distinct values for the specified `expr`. This is the same behavior as `COUNT(DISTINCT expr)` in SQL. If no argument is passed, `count()` will count the number of entities. This is the same behavior as `COUNT(*)` in SQL.

```language="js"
@`files`[count()] `File type`='Document'
```
This example counts the number of `Files` where the `File type` is a `Document`.

count_non_null

`count_non_null(expr)`

`count_non_null(expr)` will count the number of entities where `expr` is not `null`. This is the same behavior as `COUNT(expr)` in SQL.

```language="js"
@`files`[count(`File`.`name`)] `File type`='Document'
```
This example counts the number of `Files` where the `File type` is a `Document`.

distinct

`distinct(expr)`

Returns all distinct values for the specified `expr` as a list.

```language="js"
@`files`[distinct(`File type`)]
```
This example returns a list of the current `File types` of the `File` entity.

min

`min(expr)`

Returns the minimum value for the specified `expr`.

```language="js"
@`files`[min(`File size`)]
```
This example returns the minimum value for `File size` across all `File` entities.

max

`max(expr)`

Returns the maximum value for the specified `expr`.

```language="js"
@`files`[min(`File size`)]
```
This example returns the maximum value for `File size` across all `File` entities.

mean

`mean(expr)`

Returns the mean value for the specified `expr`. This is a synonym for `avg`.

```language="js"
@`files`[mean(`File size`)]
```
This example returns the mean value for `File size` across all `File` entities.

median

`median(expr)`

Returns the median value for the specified `expr`.

```language="js"
@`files`[median(`File size`)]
```
This example returns the median value for `File size` across all `File` entities.

sum

`sum(expr)`

Computes as aggregate sum over all of the values of the specified `expr`. This function mirrors the `SUM()` function in SQL.

```language="js"
@`files`[sum(`File size`)]
```
This example provides the `sum` of the total `File size` for all `File` entities.

Join functions

join_one

`join_one(target_entity, source_field, target_field)`

This function joins the calling entity to the `target_entity` where the `source_field` matches the `target_field`. This is a one-to-one relationship and functions like a LEFT JOIN in SQL.

```language="js"
@`file_collection::uid`[join_one(`My Dataset`, `Reference number`, id)]
```
This example assumes a `Dataset` called `My Dataset` which has a field called `id` and a `Collection` with an extracted field called `Reference number`. When called, this function will join the matching row from the dataset to the matching row in the Collection where the `Reference number` equals the `id` in the dataset.

join_many

`join_many(target_entity, source_field, target_field)`

This function joins the calling entity to the `target_entity` where the `source_field` matches the `target_field`. This is a one-to-many relationship and functions like a LEFT JOIN in SQL.

```language="js"
@`file_collection::uid`[join_many(`My Dataset`, `Reference number`, id)]
```
This example assumes a `Dataset` called `My Dataset` which has a field called `id` , and a `Collection` with an extracted field called `Reference number`. When called, this function will join the matching rows from the dataset to the corresponding row in the Collection where the `Reference number` equals the `id` in the dataset.

Other general purpose functions

hash

`hash(arg1, arg2, arg3, ...)`

Hashes one or more arguments together. IQL uses the Go implementation of crc64.

```language="js"
@`files`[hash(File.`Name`, `Uploaded`)]
```
This example returns the hash of `File.`Name`` and ``Uploaded``.