SQL expressions

​

Creating a SQL custom expression

• Dashboard
• API

To add a custom expression through the user interface:

• Define the name and code for the billable metric;
• Choose an aggregation type;
• Select Custom expressions as the aggregation option instead of Unique field;
• Enter your custom expression in the expanded side panel;
• Test the custom expression; and
• Save the billable metric.

Here are a few examples of custom expressions you can create for a billable metric:

Storage

Seats

  LAGO_URL="https://api.getlago.com"
  API_KEY="__YOUR_API_KEY__"

  curl --location --request POST "$LAGO_URL/api/v1/billable_metrics" \
    --header "Authorization: Bearer $API_KEY" \
    --header 'Content-Type: application/json' \
    --data-raw '{
      "billable_metric": {
        "name": "Storage",
        "code": "storage",
        "description": "Number of GB consumed",
        "aggregation_type": "sum_agg",
        "expression": "event.properties.gb * event.properties.replicas * (event.properties.ended_at - event.properties.started_at) / 3600",
        "field_name": "consumed_gb_hours",
        "recurring": false
      }
    }'

​

Supported expressions

• Concatenation: CONCAT(event.properties.user_id, '-', event.properties.app_id)
• Math operations: (event.properties.cpu_number * 25 * event.properties.duration_msec) + (event.properties.memory_mb * 0.000001 * event.properties.duration_msec)
• Rounding: ROUND(event.properties.duration_msec * 1000)
• Least/Greatest: LEAST(event.properties.memory_mb, 10.0)

• Event attributes
• Atoms
• Operators
• Functions
  • CONCAT
  • ROUND
  • FLOOR
  • CEIL
  • LEAST
  • GREATEST

​

Event attributes

• event.code (event code)
• event.timestamp (event timestamp)
• event.properties.[property_name] (event property)

​

Atoms

• 123 (integer)
• 123.45 (decimal)
• 'Hello, world!' (string)

​

Operators

• + (addition)
• - (subtraction)
• * (multiplication)
• / (division)
• unary minus (-12)

​

Functions

​

CONCAT

CONCAT(str1, str2[,strs,...])

• str1: The first string to concatenate
• str2: The second string to concatenate
• strs: (Optional) Additional strings to concatenate.

• CONCAT(event.properties.user_id, '-', event.properties.app_id)

​

ROUND

ROUND(value, precision)

• value: The number to round
• precision: (Optional) Number of decimal places. Defaults to 0.

• ROUND(14.2355) returns 14
• ROUND(14.2355, 0) returns 14
• ROUND(14.2355, 2) returns 14.24
• ROUND(14.2355, -1) returns 10

​

FLOOR

FLOOR(value, precision)

• value: The number to round
• precision: (Optional) Number of decimal places. Defaults to 0.

• FLOOR(16.2365) returns 16
• FLOOR(16.2365, 0) returns 16
• FLOOR(16.2365, 2) returns 16.23
• FLOOR(16.2365, -1) returns 10

​

CEIL

CEIL(value, precision)

• value: The number to round
• precision: (Optional) Number of decimal places. Defaults to 0.

• CEIL(14.2345) returns 15
• CEIL(14.2345, 0) returns 15
• CEIL(14.2345, 2) returns 14.24
• CEIL(14.2345, -1) returns 20

​

LEAST

LEAST(num1, num2[,nums,...])

• num1: The first number to check minimum value
• num2: The second number to check minimum value
• nums: (Optional) Additional numbers to check minimum value.

• LEAST(event.properties.disk1_usage_mb, event.properties.disk2_usage_mb, 10.0)

​

GREATEST

GREATEST(num1, num2[,nums,...])

• num1: The first number to check maximum value
• num2: The second number to check maximum value
• nums: (Optional) Additional numbers to check maximum value.

• GREATEST(event.properties.memory_mb, 10.0)

​

Testing your SQL custom expression

• You can dynamically reference any event field within your expression; and
• Use precise paths to reference fields accurately. For instance, to use the event timestamp, the path is event.timestamp. To reference a custom property sent with the event, the path is event.properties.your_field.