Format Query Language

This is a reference guide to the Automation (InsightConnect) query language used in Filter Steps and Automated Decisions. The language is not supported in Artifacts or other steps.

Variables

Variables are wrapped in double brackets, e.g. {{Step.value}}

Data Types

Automation (InsightConnect)‘s data types are based on JSON data types:

• string: surround string constants with double quotes e.g., “hello world”
• number: floating point and integer
• boolean: boolean constants are true and false
• array - Collection of elements e.g. [ "item1", "item2" ]
• regular expression: surround regexps with slashes e.g., /hello.*/
• null: null is the null constant

Operators

• = Equals, e.g. {{Step.value}} = "abc123"
• != Not equals, e.g. {{Step.value}} != "abc123"
• > Greater than, e.g. {{Step.value}} > 1
• >= Greater than or equals, e.g. {{Step.value}} >= 100
• < Less than, e.g. {{Step.value}} < 1
• \<= Less than or equals, e.g.{{Step.value}} \<= 1
• =~ (or matches) Regex matches, e.g. {{Step.value}} =~ /.*hello/
• !~ Regex not matches, e.g. {{Step.value}} !~ /hi.*/
• contains Value contains, e.g.{{Step.value}} contains "hello"
• like Value matches case sensitive pattern, e.g. {{Step.value}} like "%a" (match text ending in “a”)
• ilike Value matches case insensitive pattern, e.g. {{Step.value}} ilike "a%" (match text beginning with “a” or “A”)
• starts_with Value starts with, e.g. {{Step.value}} starts_with "prefix"
• ends_with Value ends with, e.g. {{Step.value}} ends_with "suffix"

Logical Operators

• AND (or &&) Logical AND of two expressions. E.g {{Step.value1}} != 2 AND {{Step2.value2}} != "hello"
• OR (or ||) Logical OR of two expressions. E.g {{Step.value1}} != 2 OR {{Step2.value2}} != "hello"
• NOT (or not) Logical NOT on a single expression. E.g. NOT {{Step.value1}} > 1

Functions

• length(expression) Return the length of a collection or string. E.g. length({{Step1.array}}). An empty array is length 0, you can test for an empty array with length({{[Search For Sale].[sale_posting]}}) = 0
• is_defined(variable) Return true if the variable is defined, otherwise false. E.g. is_defined({{Step.value}})
• if_error(expression) Returns the value of expression, unless it causes an error. In that case, null is returned. E.g. if_error(1/0) returns null
• if_error(expression, error_expression) Returns the value of expression, unless it causes an error. In that case, the value of error_expression is returned. E.g. if_error(1/0, "fail") returns “fail”
• if_error(expression, error_expression, no_error_expression) If expression causes an error, error_expression is returned. Otherwise, no_error_expression is returned. E.g. if_error({{Step.value}}, "Not Found", "Found")