to_boolean ↗

Use the to_boolean() function to explicitly transform string representations of boolean values into their native boolean data type.

Syntax

to_boolean (<string>)

Parameters

Name Type Required Description
string string Yes The string field or literal value that you want to convert.

Returns

The to_boolean() function returns a boolean value (true or false).

Usage notes

  • The input string must be either "TRUE" or "FALSE".
  • The conversion is case-insensitive, meaning "true", "TRUE", "True", "false", "FALSE", "False", etc., are all valid inputs for conversion.
  • If the input string is not "TRUE" or "FALSE" (case-insensitive), or if the input is NULL, the function will return NULL.

Examples

Example 1: Converting a literal string "TRUE"

Goal: Convert the literal string "TRUE" to a boolean true value.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter is_active_flag = to_boolean("TRUE")
| fields event_id, is_active_flag
| limit 3

Explanation: This query creates a new field, is_active_flag, which holds the boolean true value for every record, derived from the literal string "TRUE".

Output:

EVENT_ID IS_ACTIVE_FLAG
101 true
102 true
103 true

Example 2: Converting a literal string "FALSE"

Goal: Convert the literal string "FALSE" to a boolean false value.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter is_blocked_flag = to_boolean("FALSE")
| fields event_id, is_blocked_flag
| limit 3

Explanation: This query creates a new field, is_blocked_flag, which holds the boolean false value for every record, derived from the literal string "FALSE".

Output:

EVENT_ID IS_BLOCKED_FLAG
101 false
102 false
103 false

Example 3: Converting a case-insensitive literal string

Goal: Convert mixed-case literal strings (for example, "true", "FaLsE") to their boolean equivalents.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter status_boolean_lower = to_boolean("true")
| alter status_boolean_mixed = to_boolean("FaLsE")
| fields event_id, status_boolean_lower, status_boolean_mixed
| limit 3

Explanation: The query demonstrates that to_boolean() successfully interprets both "true" and "FaLsE" (case-insensitively) into their respective boolean true and false values.

Output:

EVENT_ID STATUS_BOOLEAN_LOWER STATUS_BOOLEAN_MIXED
101 true false
102 true false
103 true false

Example 4: Converting a derived string field

Goal: Convert a string field ("TRUE"/"FALSE") derived from existing data into a boolean type.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter success_string = if(is_successful = true, "TRUE", "FALSE")
| alter converted_success = to_boolean(success_string)
| fields event_id, is_successful, success_string, converted_success
| limit 3

Explanation: The if() function first creates success_string as either "TRUE" or "FALSE" based on is_successful. Then, to_boolean() correctly converts this string representation into a native boolean value in converted_success.

Output:

EVENT_ID IS_SUCCESSFUL SUCCESS_STRING CONVERTED_SUCCESS
101 true "TRUE" true
102 false "FALSE" false
103 true "TRUE" true

Example 5: Handling invalid string input

Goal: Demonstrate the behavior when attempting to convert an invalid string or a NULL value.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter invalid_conversion = to_boolean("not_a_boolean_string")
| alter null_input_conversion = to_boolean(NULL)
| fields event_id, invalid_conversion, null_input_conversion
| limit 3

Explanation: As expected for invalid conversions in XQL, both attempts result in NULL values for the new fields, demonstrating to_boolean()'s strict input requirements.

Output:

EVENT_ID INVALID_CONVERSION NULL_INPUT_CONVERSION
101 NULL NULL
102 NULL NULL
103 NULL NULL

Example 6: Using supported operators for deriving a boolean string

Goal: Use to_boolean() within a filter stage to evaluate a derived string field as a boolean condition (for example, checking if an event ID is odd).

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter division_result = divide(event_id, 2)
| alter is_event_active_string = if(division_result != floor(division_result), "TRUE", "FALSE")
| filter to_boolean(is_event_active_string) = true
| fields event_id, is_event_active_string
| limit 3

Explanation: This query calculates the remainder of dividing event_id by 2. If the result is not a whole number (meaning the ID is odd), it assigns "TRUE" to the string field. to_boolean() converts this string to a boolean for the filter, returning only records with odd event IDs.

Output:

EVENT_ID IS_EVENT_ACTIVE_STRING
101 "TRUE"
103 "TRUE"
105 "TRUE"