Handling Functions in Custom Formulas in Wizard

Updated on Jun 19, 2025

To use any function in Wizard, you can leverage the Custom Formula functionality. In this mode, any custom SQL functions are supported, allowing you to create unique transformations by applying the formulas you need, even if they aren’t included in the standard UI dropdowns.

CustomField leverages AI within the custom filters, making the product highly flexible and allowing you to incorporate formulas that aren’t natively supported in the UI. This means you can easily extend the standard functionality, adapting it to fit your specific needs and cases.

Below we will cover the most common use cases together.

Use Cases

SplitByChar

The SplitByChar function enables you to divide a string into segments based on a specified delimiter (character or symbol). This function is particularly useful when you want to separate information stored in a single text field.

Formula to apply for campaign name:

splitByChar('-', campaign_name[1])

The only thing you need to change is the delimiter and position to get it to work on your data.

toFloat64 / toString

Sometimes, data can contain different types. For example, a campaign ID can be either a string or a number, depending on the data source. This creates a supertype problem because a table cannot have multiple data types within a single column. To resolve this issue, we can apply a custom formula to the problematic fields.

Formula to apply for a string that should be a numeric value:

toFloat64(clicks)

Formula to apply for a numeric value that should be a string:

toString(campaign_id)

replaceRegexpAll

The replaceRegexpAll function can be used to clean up numeric values stored as strings by removing unwanted characters, such as currency symbols or text, leaving only the numeric part. This is particularly useful for fields like costs, revenues, or other numeric values that might include symbols or text.

Let’s take a look at usage based on the example:

Let’s say you have a field that contains costs with a currency symbol (e.g., $123.45, €678,90, or USD 1000) and you want to remove the currency symbol or text and extract only the numeric value.

Formula to apply:

toFloat64(replaceRegexpAll(costs, '[^0-9.]', ''))

The formula will return the number without currency and make field as a numeric.

regexpExtract

The regexpExtract function is used to extract a specific part of a string that matches a regular expression pattern. It searches for text within a field that matches the provided regular expression and extracts a specific capturing group.

Let’s take a look on usage based on example:

Campaign name equal product-id-12345

Formula to apply for ID extraction:

regexpExtract(campaign_name, "product-id-(\\d+)", 1)

The formula will return just id ‘12345’.

lower / upper

The Lower and Upper functions adjust the case of text to lowercase or uppercase, respectively. This standardization is helpful when dealing with fields that may have inconsistent casing.

Formula to apply on the field to make entities in lower case:

lower(adset_name)

Formula to apply on the field to make entities in upper case:

upper(campaign_name)

Note

The following JSON and Trim use cases are applicable only for ETL, not for ELT.

Working with JSON Data in Recipes

In the Recipes product, you can process JSON data contained in string columns using special functions in the Custom Formula block. This guide describes the main functions for extracting and analyzing JSON data.

1. JSONExtractString - Extract String Values

Extracts a string value by the specified key from a JSON object.

Syntax:‍


JSONExtractString(json_column, 'key_path')

JSONExtractString(json_column, 'key1', 'key2', 'key3')  // for nested paths

Examples:‍

For column user_data containing:


{"name": "John", "email": "john@example.com", "settings": {"theme": "dark"}}

Formula Examples:


JSONExtractString(user_data, 'name')

Result: ‍John


JSONExtractString(user_data, 'email')

Result: john@example.com


JSONExtractString(user_data, 'settings', 'theme')

Result: dark

2. JSONExtractInt - Extract Integer Values

Extracts a numeric value and converts it to a 64-bit signed integer.

Syntax:


JSONExtractInt(json_column, 'key_path')

JSONExtractInt(json_column, 'key1', 'key2')  // for nested paths

Examples:‍

For column order_data containing:


{"order_id": 12345, "quantity": 3, "metadata": {"priority": 1}}

Formula Examples:


JSONExtractInt(order_data, 'order_id')

Result: 12345


JSONExtractInt(order_data, 'quantity')

Result: 3


JSONExtractInt(order_data, 'metadata', 'priority')

Result: 1

3. JSONExtractFloat - Extract Floating Point Numbers

Extracts a numeric value as a 64-bit floating point number.

Syntax:


JSONExtractFloat(json_column, 'key_path')

JSONExtractFloat(json_column, 'key1', 'key2')  // for nested paths

Examples:‍

For column product_data containing:


{"price": 99.99, "discount": 0.15, "stats": {"rating": 4.7}}

Formula Examples:‍


JSONExtractFloat(product_data, 'price')

Result: 99.99


JSONExtractFloat(product_data, 'discount')

Result: 0.15


JSONExtractFloat(product_data, 'stats', 'rating')

Result: 4.7

4. JSONExtractBool - Extract Boolean Values

Extracts a boolean value (true/false). Returns 0 for false, 1 for true.

Syntax:‍


JSONExtractBool(json_column, 'key_path')

JSONExtractBool(json_column, 'key1', 'key2')  // for nested paths

Examples:‍

For column settings_data containing:


{"active": true, "notifications": false, "features": {"premium": true}}

Formula Examples:


JSONExtractBool(settings_data, 'active')

Result: 1 (true)


JSONExtractBool(settings_data, 'notifications')

Result: 0 (false)


JSONExtractBool(settings_data, 'features', 'premium')

Result: 1 (true)

5. JSONExtractArrayRaw - Extract Arrays

Extracts array data as a JSON string. Returns the raw JSON representation of the array.

Syntax:


JSONExtractArrayRaw(json_column, 'key_path')

JSONExtractArrayRaw(json_column, 'key1', 'key2')  // for nested paths

Examples:‍

For column user_profile containing:


{"tags": ["new", "premium", "active"], "scores": [95, 87, 92]}

Formula Examples:


JSONExtractArrayRaw(user_profile, 'tags')

Result: ["new", "premium", "active"]


JSONExtractArrayRaw(user_profile, 'scores')

Result: [95, 87, 92]

6. JSONHas - Check Key Existence

Checks if the specified key exists in the JSON object. Returns 1 if exists, 0 if not.

Syntax:


JSONHas(json_column, 'key_path')

JSONHas(json_column, 'key1', 'key2')  // for nested paths

Examples:

For column event_data containing:


{"event": "purchase", "amount": 150, "metadata": {"source": "mobile"}}

Formula Examples:


JSONHas(event_data, 'event')

Result: 1 (true)


JSONHas(event_data, 'user_id')

Result: 0 (false)


JSONHas(event_data, 'metadata', 'source')

Result: 1 (true)

7. JSONLength - Get JSON Object or Array Length

Returns the number of elements in a JSON object or array as UInt64.

Syntax:


JSONLength(json_column, 'key_path')

JSONLength(json_column, 'key1', 'key2')  // for nested paths

Examples:‍

For column data containing:


{"items": ["a", "b", "c"], "properties": {"x": 1, "y": 2, "z": 3}}

Formula Examples:


JSONLength(data, 'items')

Result: 3


JSONLength(data, 'properties')

Result: 3


JSONLength(data)

Result: 2 (number of top-level keys)

8. JSONType - Determine Value Type

Returns the type of a value in JSON. Possible return values: "String", "Int64", "Float64", "Bool", "Array", "Object", "Null".

Syntax:


JSONType(json_column, 'key_path')

JSONType(json_column, 'key1', 'key2')  // for nested paths

Examples:‍

For column mixed_data containing:


{"name": "test", "count": 42, "active": true, "tags": [], "meta": null}

Formula Examples:‍


JSONType(mixed_data, 'name')

Result: String


JSONType(mixed_data, 'count')

Result: Int64


JSONType(mixed_data, 'active')

Result: Bool


JSONType(mixed_data, 'tags')

Result: Array


JSONType(mixed_data, 'meta')

Result: Null

Useful Function Combinations

Check Existence and Extract Value


if(JSONHas(json_column, 'optional_field'),

  JSONExtractString(json_column, 'optional_field'),

  'Default value')

Working with Nested Structures


JSONExtractString(data, 'level1', 'level2', 'target_field')

Conditional Extraction Based on Type


if(JSONType(data, 'value') = 'String',

  JSONExtractString(data, 'value'),

  toString(JSONExtractInt(data, 'value')))

Error Handling

If the specified key is not found or the JSON is invalid, functions return:

JSONExtractString → empty string ""
JSONExtractInt → 0
JSONExtractFloat → 0.0
JSONExtractBool → 0 (false)
JSONHas → 0 (false)
JSONLength → 0
JSONType → "Null"

Usage Recommendations

1. Always validate JSON using JSONHas before extracting values

2. Use multiple arguments for nested paths instead of complex string paths

3. Handle missing keys with conditional operators

4. Cache results of complex JSON operations for better performance

5. Use appropriate extraction function for your expected data type

Real-World Scenarios

Extracting User Information

Sample JSON:


{"user": {"id": 123, "profile": {"name": "Anna", "age": 28, "premium": true}}}

Formulas:


JSONExtractInt(user_data, 'user', 'id')

JSONExtractString(user_data, 'user', 'profile', 'name')

JSONExtractInt(user_data, 'user', 'profile', 'age')

JSONExtractBool(user_data, 'user', 'profile', 'premium')

E-commerce Event Analysis

Sample JSON:


{"event": "purchase", "amount": 299.99, "items": ["item1", "item2"], "payment": {"method": "card", "currency": "USD"}}

Formulas:


JSONExtractString(event_data, 'event')

JSONExtractFloat(event_data, 'amount')

JSONLength(event_data, 'items')

JSONExtractString(event_data, 'payment', 'method')

JSONExtractString(event_data, 'payment', 'currency')

Quick Reference Table

Function	Purpose	Return Type	Example Usage
JSONExtractString	Extract text values	String	JSONExtractString(data, 'name')
JSONExtractInt	Extract whole numbers	Int64	JSONExtractInt(data, 'count')
JSONExtractFloat	Extract decimal numbers	Float64	JSONExtractFloat(data, 'price')
JSONExtractBool	Extract true/false	UInt8 (0/1)	JSONExtractBool(data, 'active')
JSONExtractArrayRaw	Extract arrays	String (JSON)	JSONExtractArrayRaw(data, 'items')
JSONHas	Check if key exists	UInt8 (0/1)	JSONHas(data, 'field')
JSONLength	Count elements	UInt64	JSONLength(data, 'array')
JSONType	Get value type	String	JSONType(data, 'field')

Important Notes

All functions support nested access using multiple string arguments
Functions return default values (empty string, 0, false) for missing keys
Boolean values are returned as integers: 1 for true, 0 for false
For complex nested structures, use multiple arguments instead of dot notation in paths

Trim Functions

In the Recipes product, you can clean and process string data using powerful trimming functions in the Custom Formula block. This guide covers the essential trimming functions available in ClickHouse and advanced techniques for custom character removal.

These functions are designed to remove space characters (ASCII 32) from your strings. They do not remove other whitespace like tabs (\\t) or newlines (\\n) by default.

1. trim

Removes space characters from both the beginning and end of a string.

Syntax:


trim(string_column)

Examples:

For column user_input

Formula Examples:


trim(user_input)

Input: " Hello World "

Output: "Hello World"

2. ltrim

Removes space characters only from the beginning (left side) of a string.

Syntax:

ltrim(string_column)

Examples:

For column product_name

Formula Examples:

ltrim(product_name)

Input: " iPhone 15"

Output: "IPhone 15"

Input: " Coffee Beans "

Output: "Coffee Beans " (trailing spaces remain)

3. rtrim

Removes space characters only from the end (right side) of a string.

Syntax:

rtrim(string_column)

Examples:

For column description

Formula Examples:

rtrim(description)

Input: "Premium Product "

Output: "Premium Product"

Input: " Great Item "

Output: " Great Item" (leading spaces remain)

Quick Reference Table

Function	Purpose	Syntax
trim(s)	Remove space characters from both sides	trim(column_name)
ltrim(s)	Remove space characters from left side	ltrim(column_name)
rtrim(s)	Remove space characters from right side	rtrim(column_name)

Conclusion

Custom functions within a Custom Formula offer the flexibility to tailor transformations precisely to your needs. The examples provided demonstrate the range of possibilities, you can implement any custom formula as long as it’s a valid SQL function. This means there are essentially no limitations beyond what SQL itself supports, giving you the freedom to apply the transformations you need without restriction.

Was this article helpful?

On this page

Use Cases
SplitByChar
toFloat64 / toString
replaceRegexpAll
regexpExtract
lower / upper
Working with JSON Data in Recipes
Trim Functions
Conclusion