How to Use Scalar Functions in ksqlDB

In ksqlDB, scalar functions are used to apply transformations to single values.

Examples

Below are listed some use cases of when to use scalar functions

How to truncate a timestamp

How to find scalar functions?

When you list all the functions available in your cluster, scalar functions are the ones that don't belong to the TABLE or AGGREGATE categories.

This is the list of all the scalar functions that come with ksqlDB by default:

Function Name         | Category
--------------------------------------------
 ARRAY_CONCAT          | ARRAY
 ARRAY_CONTAINS        | ARRAY
 ARRAY_DISTINCT        | ARRAY
 ARRAY_EXCEPT          | ARRAY
 ARRAY_INTERSECT       | ARRAY
 ARRAY_JOIN            | ARRAY
 ARRAY_LENGTH          | ARRAY
 ARRAY_MAX             | ARRAY
 ARRAY_MIN             | ARRAY
 ARRAY_REMOVE          | ARRAY
 ARRAY_SORT            | ARRAY
 ARRAY_UNION           | ARRAY
 GENERATE_SERIES       | ARRAY
 SLICE                 | ARRAY
                       |
 COALESCE              | CONDITIONAL
 IFNULL                | CONDITIONAL
 NULLIF                | CONDITIONAL
                       |
 BIGINT_FROM_BYTES     | CONVERSIONS
 DOUBLE_FROM_BYTES     | CONVERSIONS
 INT_FROM_BYTES        | CONVERSIONS
                       |
 CONVERT_TZ            | DATE / TIME
 DATEADD               | DATE / TIME
 DATESUB               | DATE / TIME
 DATETOSTRING          | DATE / TIME
 FORMAT_DATE           | DATE / TIME
 FORMAT_TIME           | DATE / TIME
 FORMAT_TIMESTAMP      | DATE / TIME
 FROM_DAYS             | DATE / TIME
 FROM_UNIXTIME         | DATE / TIME
 PARSE_DATE            | DATE / TIME
 PARSE_TIME            | DATE / TIME
 PARSE_TIMESTAMP       | DATE / TIME
 STRINGTODATE          | DATE / TIME
 STRINGTOTIMESTAMP     | DATE / TIME
 TIMEADD               | DATE / TIME
 TIMESTAMPADD          | DATE / TIME
 TIMESTAMPSUB          | DATE / TIME
 TIMESTAMPTOSTRING     | DATE / TIME
 TIMESUB               | DATE / TIME
 UNIX_DATE             | DATE / TIME
 UNIX_TIMESTAMP        | DATE / TIME
                       |
 EXTRACTJSONFIELD      | JSON
 IS_JSON_STRING        | JSON
 JSON_ARRAY_CONTAINS   | JSON
 JSON_ARRAY_LENGTH     | JSON
 JSON_CONCAT           | JSON
 JSON_KEYS             | JSON
 JSON_RECORDS          | JSON
 TO_JSON_STRING        | JSON
                       |
 FILTER                | LAMBDA
 REDUCE                | LAMBDA
 TRANSFORM             | LAMBDA
                       |
 AS_MAP                | MAP
 ENTRIES               | MAP
 MAP_KEYS              | MAP
 MAP_UNION             | MAP
 MAP_VALUES            | MAP
                       |
 ABS                   | MATHEMATICAL
 CEIL                  | MATHEMATICAL
 EXP                   | MATHEMATICAL
 FLOOR                 | MATHEMATICAL
 GREATEST              | MATHEMATICAL
 LEAST                 | MATHEMATICAL
 LN                    | MATHEMATICAL
 RANDOM                | MATHEMATICAL
 ROUND                 | MATHEMATICAL
 SIGN                  | MATHEMATICAL
 SQRT                  | MATHEMATICAL
                       |
 AS_VALUE              | OTHER
 GEO_DISTANCE          | OTHER
                       |
 REGEXP_EXTRACT        | REGULAR EXPRESSION
 REGEXP_EXTRACT_ALL    | REGULAR EXPRESSION
 REGEXP_REPLACE        | REGULAR EXPRESSION
 REGEXP_SPLIT_TO_ARRAY | REGULAR EXPRESSION
                       |
 CHR                   | STRING
 CONCAT                | STRING
 CONCAT_WS             | STRING
 ELT                   | STRING
 ENCODE                | STRING
 FIELD                 | STRING
 FROM_BYTES            | STRING
 INITCAP               | STRING
 INSTR                 | STRING
 LCASE                 | STRING
 LEN                   | STRING
 LPAD                  | STRING
 MASK                  | STRING
 MASK_KEEP_LEFT        | STRING
 MASK_KEEP_RIGHT       | STRING
 MASK_LEFT             | STRING
 MASK_RIGHT            | STRING
 REPLACE               | STRING
 RPAD                  | STRING
 SPLIT                 | STRING
 SPLIT_TO_MAP          | STRING
 SUBSTRING             | STRING
 TO_BYTES              | STRING
 TRIM                  | STRING
 UCASE                 | STRING
 UUID                  | STRING
                       |
 URL_DECODE_PARAM      | URL
 URL_ENCODE_PARAM      | URL
 URL_EXTRACT_FRAGMENT  | URL
 URL_EXTRACT_HOST      | URL
 URL_EXTRACT_PARAMETER | URL
 URL_EXTRACT_PATH      | URL
 URL_EXTRACT_PORT      | URL
 URL_EXTRACT_PROTOCOL  | URL
 URL_EXTRACT_QUERY     | UR

Was this article helpful?