Manipulate data in FQL

You can use Flows Query Language (FQL) to perform math functions, manipulate strings and arrays, and interact with the data in your responses. To help you get started with FQL, use the sample data provided and FQL examples below.

Working with strings

• Insert strings then group and sum results by description
• Return the length of a string
• Return part of a string
• Convert a string into a number
• Get the string before the first occurrence of a pattern
• Get the string after the first occurrence of a pattern
• Transform a string to all uppercase
• Transform a string to all lowercase
• Trim a string
• Pad a string
• Split a string into an array of components
• Join an array of strings into a single string
• Replace one string with another string
• Base64 encode a string
• Base64 decode a string
• Convert a string into JSON

Working with numbers

• Sum numerical values
• Get the absolute difference between two numbers
• Round up a number
• Round down a number
• Raise a number to a power
• Get the square root of a number
• Convert a number to hex or binary
• Generate a new random invoice number
• Convert a number into a string

Working with URLs

• Encode a URL component
• Decode a URL component
• Encode an entire URL
• Decode an entire URL

Working with date and time

• Get the current time in ISO 8601 format
• Get the current time in Unix milliseconds since the epoch
• Convert from a specific date format into Unix epoch time
• Convert from Unix epoch time into a specific date format
• Get the day/month/year from a date
• Get the time from a date
• Get the day of the week from a date
• Compare two dates
• Increase a date by one day
• Get the difference between two dates
• Time and Date formatting

Working with logic and arrays

• Append to an array
• If-then-else
• Partition an array
• Perform an action on each value in an array
• Filter for values
• Collapse an array to a single value
• Sort an array

Working with objects

• Perform an action on each value in an object

Example JSON

The examples below use the following JSON data:

1
{
2
    "customer_info": {
3
        "customer field": "Customer data",
4
        "unformatted_customer_field": " customer \n stuff ",
5
        "total_value": "281.01",
6
        "associated_usernames": ["user1", "myuser", "online_user"]
7
    },
8
    "payments": [
9
        {
10
            "invoice_number": "101301",
11
            "date": "2022-09-11T16:12:34.494Z",
12
            "description": "recurring subscription",
13
            "amount": 110.48
14
        },
15
        {
16
            "invoice_number": "101302",
17
            "date": "2022-09-29T14:45:13.148Z",
18
            "description": "one time purchase",
19
            "amount": 24.49
20
        },
21
        {
22
            "invoice_number": "101303",
23
            "date": "2022-10-11T16:12:34.683Z",
24
            "description": "recurring subscription",
25
            "amount": 110.48
26
        },
27
        {
28
            "invoice_number": "101304",
29
            "date": "2022-10-12T11:45:22.182Z",
30
            "description": "recurring subscription deluxe",
31
            "amount": 35.56
32
        }
33
    ]
34
}

Insert strings then group and sum results by description

The example below gets every description value in the payments array and appends the annual cost string. It then gets the amount value below each description field, multiplies it by 12, then appends it to its corresponding result. The results are grouped by description field.

1
[
2
    {"recurring subscription annual cost": 1325.76},
3
    {"one time purchase annual cost": 293.88},
4
    {"recurring subscription annual cost": 1325.76},
5
    {"recurring subscription deluxe annual cost": 426.72}
6
]

Return the length of a string

The $length() function returns the length of the specified string. The example below returns the length of the string in the first description key-value pair in the payments array.

Return part of a string

The $substring() function returns part of a specified string. In the example below, The 3 is optional and specifies the offset, and the 6 is the number of characters you are selecting. Negative numbers can also be used for the offset.

Get the string before the first occurrence of a pattern

In the example below, the $substringBefore() function returns the substring before the specified occurrence of subscription . If it doesn’t find subscription, it returns the entire string.

Get the string after the first occurrence of a pattern

The $substringAfter() function finds a pattern and returns the substring that appears after the found pattern. The example below returns the substring that follows recurring in the description key-value pair in the first object of the payments array.

Transform a string to all uppercase

The $uppercase() function makes all the characters in a string uppercase.

Transform a string to all lowercase

The $lowercase() function makes all the characters in a string lowercase.

Trim a string

The $trim() function does the following:

• Removes excess leading and trailing spaces
• Converts newline, carriage return, line feed, and tab characters into a single space character
• Reduces consecutive spaces into a single space character

Pad a string

The $pad() function adds spaces or characters to a string so that the total length of the string equals the second parameter. If the second parameter is a positive number, it pads the end of the string with the third parameter. If the second parameter is negative, it pads the front of the string with the third parameter. (Third parameter characters default to spaces if left blank.)

Split a string into an array of components

The $split() function returns the string split on the separator specified in the second parameter and optionally limited by the third parameter. You can also use a regex instead of a string.

Join an array of strings into a single string

The $join() function creates a single string from an array of strings. The example below gets the array from the associated_usernames key and returns the array’s values as a single string.

Replace one string with another string

In the example below, the $replace() function finds the instances of recurring in the first parameter string and replaces them with renewing, limited to the first instance found (optionally specified with the 1). You could also use a regex instead of recurring.

Base64 encode a string

The $base64encode() function converts a string to base64 encoding. The example below converts the string "some data here" into "c29tZSBkYXRhIGhlcmU=".

Base64 decode a string

The $base64decode function converts a base64-encoded string into a human-readable string. The example below converts "c29tZSBkYXRhIGhlcmU=" into "some data here".

Convert a string into JSON

The $jsonParse() function enables the string to be formatted into JSON so it can be queried with FQL. The example below assumes you have the string {"Feedback Type":"Bug Report"} stored as a variable named input.

1
{"Feedback Type":"Bug Report"}

Convert a string into a number

You can convert a string into a number with the $number() function. The example below converts the string "281.01" in the customer_info object into the number 281.01.

Sum numerical values

The $sum() function gets values from every instance of a key-value pair in an object or array, adds the values together, and returns the result. The example below gets every amount field’s value in the payments array and returns their sum.

Get the absolute difference between two numbers

The $abs() function returns the absolute difference between two numbers. Absolute difference is the distance between two values on a number line. Absolute difference is always positive.

Round up a number

The $ceil() function rounds a number up to the next whole number.

Round down a number

The $floor() function rounds a number down to the previous whole number.

Raise a number to a power

The $power() function raises the first number to the power of the second number. The example below raises 2 (the base) to the power of 3 (the exponent).

Get the square root of a number

The $sqrt() function returns the square root of a number.

Convert a number to hex or binary

The formatBase() function converts a number to hex or binary. The example below converts 3000 to hex. Using base 2 instead of 16 would convert 3000 to binary.

Generate a new random invoice number

The $round($random()) function generates a random whole number. The example below generates a random whole number between 1 and 1000 and returns the number appended to the string “Invoice number ”.

Convert a number into a string

You can convert a number into a string with the $string() function. The example below gets the number value from the amount key in the first object in the payments array and converts it into the string "110.48".

Encode a URL component

The $encodeUrlComponent() function replaces certain characters in a URL component with their UTF-8 encoded versions. The example below replaces ? with %3F, and = with %3D.

Decode a URL component

The $decodeUrlComponent() function replaces UTF-8 encoded characters in a URL component with their original versions. The example below replaces %3F with ?, and $3D with =.

Encode an entire URL

The $encodeUrl() function replaces certain characters in a URL with UTF-8 encoded characters. The example below replaces こんにちは with %E3%81%93%E3%82%93%E3%81%AB%E3%81%A1%E3%81%AF

Decode an entire URL

The $decodeUrl() function replaces UTF-8 encoded characters in a URL with their original versions. The example below replaces %E3%81%93%E3%82%93%E3%81%AB%E3%81%A1%E3%81%AF with こんにちは.

Get the current time in ISO 8601 format

The $now() function returns the current time in ISO 8601 format.

Get the current time in Unix milliseconds since the epoch

The millis() function returns the current time in Unix milliseconds since the epoch.

Convert from a specific date format into Unix epoch time

The $toMillis() function converts a given date format into Unix epoch time. See the formatting section below for details on date formatting.

Convert from Unix epoch time into a specific date format

The $fromMillis() function converts Unix epoch time into a different date format. See the Time and date formatting section below for details on date formatting.

Get the day/month/year from a date

The $year(), $month(), and $day() functions return their respective components from a year-month-day date format.

Get the time from a date

The $hours(), $minutes(), $seconds(), and $milliseconds() functions return their respective values from a given date. The example below uses the $now() function for the date.

Get the day of the week from a date

The $dayOfTheWeek() function accepts a date and returns a number corresponding to a day of the week.

Compare two dates

The $hasSameDate() function accepts two or more dates with parameters specifying values in the dates. The function compares the values specified by the parameters and returns true if they’re the same, or false if they’re not.

Increase a date by one day

The $datePlus() function accepts a date (formatted as YYYY-MM-DD or milliseconds since epoch), the number of units you want to add, and the date component you want to advance (years, months, days, hours, minutes, seconds, or milliseconds). The function returns the increased date as milliseconds since epoch.

Get the difference between two dates

The $diffDate() function accepts two dates (formatted as YYYY-MM-DD or milliseconds since epoch) and a component of each date (years, months, days, hours, minutes, seconds, or milliseconds), then returns the difference between the two dates’ component.

Time and date formatting

For more information about time and date formatting with FQL, see the W3C Date and Time Formats page.

Append to an array

The $append() function can combine two arrays, an array and a single value, or two strings into an array.

If-then-else

The $boolean value is a true/false test. The second value is the output for true and the final value is the output for false. The example below tests if the total_value field’s value is greater than 250. Since the value is greater than 250, the function returns “high-value customer”.

Partition an array

The $partition() function breaks an array up into smaller arrays and returns the smaller arrays as a list.

1
[
2
 [{"invoice_number": "101301","date": "2022-09-11T16:12:34.494Z","description": "recurring subscription","amount": 110.48},{"invoice_number": "101302","date": "2022-09-29T14:45:13.148Z","description": "one time purchase","amount": 24.49}],
3
 [{"invoice_number": "101303","date": "2022-10-11T16:12:34.683Z","description": "recurring subscription","amount": 110.48},{"invoice_number": "101304","date": "2022-10-12T11:45:22.182Z","description": "recurring subscription deluxe","amount": 35.56}]
4
]

Perform an action on each value in an array

In this example, the $map() function gets the numerical values from the amount fields in the payments array and converts them to strings with the $string() function.

Filter for values

In this example, the $filter() function returns values greater than 40 from amount fields in the payments array.

In this example, the filter() function returns items from the list where the description property matches the payments variable.

1
[
2
  {
3
    "invoice_number": "101301",
4
    "date": "2022-09-11T16:12:34.494Z",
5
    "description": "recurring subscription",
6
    "amount": 110.48
7
  },
8
  {
9
    "invoice_number": "101303",
10
    "date": "2022-10-11T16:12:34.683Z",
11
    "description": "recurring subscription",
12
    "amount": 110.48
13
  }
14
]

Collapse an array to a single value

The $reduce() function applies a function to every element in the array. In this example, it adds all the elements of the array together.

Sort an array

The $sort() function sorts an array by the function specified where $j is the current item and $i is the next item.

Perform an action on each value in an object

In this example, the $each() function converts every string value in an object to uppercase.