Final Compute Expressions & Operators#

The operators below can be used to build expressions that are evaluated in the get_rows piplines, exporter presets and query placeholders.

When used with the $final_compute stage of a get_rows pipeline the expressions will be applied for each row, so many of the operators are for accessing and modifiying data in a row.

Example of a get_rows call that uses a pipeline with an expression

The example tables col_settings#

{
    "c0": {
        "name": "Column 0 Text",
        "display_type": "string"
    },
    "c1": {
        "name": "Column 1 Number",
        "display_type": "integer"
    }
}

Data in the table#

[
    { "c0": "Frank", "c1": 12, "c2": 100 },
    { "c0": "Niels", "c1": 34, "c2": 100 },
    { "c0": "Heye", "c1": 56, "c2": 100 },
    { "c0": "Richard", "c1": 78, "c2": 100 },
]

a get_rows call with a small pipeline that uses operators in a $final_compute stage#

{
    "table_id": "...",
    "pipeline": [
        {"$final_compute": {"c1": "c1 + c2"}}
    ]
}

Resulting rows#

[
    { "c0": "Frank", "c1": 112, "c2": 100 },
    { "c0": "Niels", "c1": 134, "c2": 100 },
    { "c0": "Heye", "c1": 156, "c2": 100 },
    { "c0": "Richard", "c1": 178, "c2": 100 },
]

Operator: column-values#

Implicit Operator c0-99: any cx will be resolved into the value from the input row and has the corresponding type (number, string, list-of-rows (subtable), list-of-values)

Operator: $filter#

Operator $filter(cx, filter_expr) returns the list of row_docs that match the filter_expr

example#

$filter(c3, {'c0':'spoon'})

cx must contain a subtable
Note the usage of ‘ and missing “ compared to regular programming.
The filter_expr is parsed as json, and validated the same as a regular filter.
Placeholders may be used in the filter_expr
Operator + with a left and right side parameter. Returns the same type as the larger parameter.
- Example: c3 + c4
- e.g. list + list -> combined list of values (e.g. row_docs of subtables)
- e.g. list + integer -> the list. If a value in the list is a number the integer will be added to each.
- e.g. number + number -> addition of both numbers
- e.g. string + number -> try to parse the string as number and add the two, otherwise convert number to a string
Operator col(list_of_rows, cx) returns a list of values, extracted from the list of rows
- Example: $col(c3, c0)
- if c3 is a subtable, this returns a list of the c0 values from the subtable.
Operator sum(list_of_values) builds the sum over all numbers in the given list.
- Example $sum($col(c3, c1))
- Since it requires a list of values, it is best used with the $col operator, or it may use a column built by another pipeline step (e.g. $distict with merge operator list)
Operator count(list_of_values) accepts a list and counts the items.
- Example $count($col(c3, c1))
Operator min(list_of_values) accepts a list and returns the smallest number.
- Example $min($col(c3, c1))
Operator max(list_of_values) accepts a list and returns the highest number.
- Example $max($col(c3, c1))
Operator avg(list_of_values) accepts a list and builds the average of the numbers inside.
- Example $count($avg(c3, c1))
Operator squash(list_of_values) accepts a list and returns a comma separated list of the items (string)
- Example $squash($col(c3, c0))
Operator format(list_of_rows, format_str) generates a comma separated list (string) from the subtable rows list_of_rows.
- Example $format(c3, c0-c3 (c1))
- May produce results like plate-white(45), knife-blue(4)
- list_of_rows
Operator format_time(timestamp_value, format_str) generates a formatted date
- Example $format_time(c3, '%d.%m.%y %H:%M:%S')
- May produce results like 10.06.24 14:19:15
- timestamp_value an expression, that evaluates to an integer (timestamp)
- format_str an expression, that evaluates to a format string
- The format string uses this syntax: strftime format codes
Operator format_seconds(seconds_value, format_str) similar to format_time, but accepts seconds to format a time-span rather than a timestamp
- Example $format_time(c3, '%H:%M:%S')
- May produce results like 14:19:15
- seconds_value an expression, that evaluates to an integer (timestamp)
- format_str an expression, that evaluates to a format string
- The format string can only use these codes:
  
  %H hours, zero padded
  
  %M minutes, zero padded
  
  %S seconds, zero padded
  
  %-H hours, no padding
  
  %-M minutes, no padding
  
  %-S seconds, no padding
Operator $kw returns the current calendar week as integer.
- Example $kw returns e.g. 22
Operator $KW returns current calendar week, but with a KW prefix.
- Example $KW returns e.g. KW22
Operator $monat returns the current month as string in german.
- Example $monat returns e.g. Juni
Operator $wochentag returns current weekday as string in german.
- Example $wochentag returns e.g. Freitag
Operator $wtag retuns current weekday in a short form in german.
- Example $wtag returns e.g. Fr
Operator $year returns current year as integer
- Example $year returns e.g. 2024
Operator $row_number returns position of the row in the reply as integer, starting with 1.
- Example $row_number returns e.g. 1 for the first row
Operator $split(expr, delimiter) builds a list (of strings) from a string
- Example $split(c0, ';')
- E.g. c0 contains “a;b” this will return [“a”,”b”]
- expr an expression, that evaluates to a string
- delimiter a delimiter used to split the string, this is also a string
Operator $list_make_unique(expr) removes double entries from a list of values of any kind
- Example $list_make_unique(c0)
- expr an expression, that evaluates to a list
Operator $list_to_string(expr, delimiter) builds a string from a list of strings
- Example $list_to_string(c0, ', ')
- E.g. c0 contains [“a”,”b”] this will return “a, b”
- expr an expression, that evaluates to a list of strings
- delimiter a delimiter used to concatenate the list values
Operator $replace(expr, old, new) replaces all occurences of ‘old’ in a string with ‘new’
- Example $replace(c0, ';', ', ')
- E.g. c0 contains “a;b;c” this will return “a, b, c”
- expr an expression, that evaluates to a string
- old characters to replace
- new characters to fill in