Custom Operators

Fracttal ETL extends the standard JSON Logic operators with its own operators available in the transform block of any ETL configuration.

Renaming and casting

`rename`

Maps source fields to destination fields, renaming them and optionally casting their type.

Signature: ["rename", [logic, "new_name", "type?"], ...]

Each element of the array is a 2- or 3-element tuple: the logic to obtain the value, the output field name, and the optional cast type.

{
  "rename": [
    [{"var": "firstName"}, "nombre", "string"],
    [{"var": "age"}, "edad", "integer"],
    [{"var": "price"}, "precio", "number"],
    [{"var": "active"}, "activo", "boolean"],
    [{"var": "tags"}, "etiquetas", "array"]
  ]
}

See Data Types for the complete cast type reference.

Text

`trim`

Removes characters from the edges of a string.

Signature: ["trim", logic, "characters", "mode"]

Parameter	Type	Default	Description
`logic`	JSON Logic	—	Expression to obtain the string
`characters`	string	—	Characters to remove
`mode`	string	`"all"`	`"all"`, `"left"`, or `"right"`

{"trim": [{"var": "name"}, " ", "all"]}

{"trim": [{"var": "code"}, "0", "left"]}

`split`

Splits a string by a separator and returns the element at the given index.

Signature: ["split", logic, "separator", index]

{"split": [{"var": "full_name"}, " ", 0]}

{"split": [{"var": "email"}, "@", 1]}

`join`

Joins the elements of an array into a string using a separator.

Signature: ["join", logic, "separator"]

{"join": [{"var": "tags"}, ", "]}

`substring`

Extracts a portion of a string by start and end index.

Signature: ["substring", logic, start, end]

Indices are 0-based. If end is 0, it extracts to the end of the string.

{"substring": [{"var": "code"}, 0, 4]}

{"substring": [{"var": "description"}, 10, 0]}

`case`

Converts a string to uppercase or lowercase.

Signature: ["case", logic, "upper"|"lower"]

{"case": [{"var": "country"}, "upper"]}

`replace`

Replaces all occurrences of a substring in the string.

Signature: ["replace", logic, "original", "replacement"]

{"replace": [{"var": "phone"}, "-", ""]}

{"replace": [{"var": "text"}, " ", "_"]}

`switch`

Maps a value to another using comma-separated value,result pairs. The last unpaired element is the default value.

Signature: ["switch", logic, "v1,r1,v2,r2,...,default"]

{"switch": [{"var": "status"}, "A,Activo,I,Inactivo,P,Pendiente,Desconocido"]}

With a single pair and no default:

{"switch": [{"var": "type"}, "premium,true,"]}

`replace_if_condition`

Replaces the value only if it matches a comparison value; otherwise returns the original value.

Signature: ["replace_if_condition", logic, "compare_value", "replacement_value"]

{"replace_if_condition": [{"var": "status"}, "None", "Pendiente"]}

{"replace_if_condition": [{"var": "category"}, "null", "Sin categoría"]}

`string_to_array`

Splits a string into an array using the given separator.

Signature: ["string_to_array", logic, "separator"]

{"string_to_array": [{"var": "tags"}, ","]}

`startsWith`

Returns true if the string begins with the given prefix.

Signature: ["startsWith", logic, "prefix"]

{"startsWith": [{"var": "code"}, "FTL-"]}

`endsWith`

Returns true if the string ends with the given suffix.

Signature: ["endsWith", logic, "suffix"]

{"endsWith": [{"var": "filename"}, ".pdf"]}

Numbers

`round`

Rounds a number to the specified number of decimal places. Without decimals, rounds to the nearest integer.

Signature: ["round", logic, decimals?]

{"round": [{"var": "price"}, 2]}

{"round": [{"var": "total"}, 0]}

`format_number`

Formats a number using Python format templates ({:,}, {:.2f}, etc.).

Signature: ["format_number", logic, "integer_format", "decimal_format?"]

{"format_number": [{"var": "amount"}, "{:,}"]}

With decimals:

{"format_number": [{"var": "amount"}, "{:,}", "{:.2f}"]}

Dates

`format_date`

Converts dates between formats, applies timezones, or generates the current date/time.

Signature: ["format_date", logic_or_"now", "input_format", "output_format", "timezone?"]

Special output format	Description
`"timestamp"`	Unix timestamp as a string
`"microsoft_json_date"`	`/Date(milliseconds)/` format

Format conversion:

{"format_date": [{"var": "created_at"}, "%Y-%m-%d", "%d/%m/%Y"]}

With timezone:

{"format_date": [{"var": "date"}, "%Y-%m-%dT%H:%M:%S", "%d/%m/%Y %H:%M", "America/Santiago"]}

Current date and time:

{"format_date": ["now", "", "%Y-%m-%dT%H:%M:%S", "Europe/Madrid"]}

To timestamp:

{"format_date": [{"var": "date"}, "%Y-%m-%d", "timestamp"]}

`add_timedelta`

Adds a time interval to a date in ISO 8601 format (%Y-%m-%dT%H:%M:%S.%f%z).

Signature: ["add_timedelta", "iso_date", number, "type"]

`type`	Description
`"hour"` (default)	Hours
`"day"`	Days (equivalent to 24 hours)
`"second"`	Seconds

{"add_timedelta": ["2024-01-15T00:00:00.000+0000", 24, "hour"]}

{"add_timedelta": ["2024-01-15T00:00:00.000+0000", 7, "day"]}

`substract_timedelta`

Subtracts a time interval from a date in ISO 8601 format.

Signature: ["substract_timedelta", "iso_date", number, "type"]

{"substract_timedelta": ["2024-01-15T00:00:00.000+0000", 1, "day"]}

`substract_datetime`

Calculates the difference between two dates and returns it in seconds.

Signature: ["substract_datetime", logic, "reference_date_or_now", "format"]

Using "now" as reference (current date in UTC):

{"substract_datetime": [{"var": "start_date"}, "now", "%Y-%m-%dT%H:%M:%S%z"]}

Between two specific dates:

{"substract_datetime": [{"var": "end_date"}, "2024-01-01T00:00:00+0000", "%Y-%m-%dT%H:%M:%S%z"]}

Booleans

`set_boolean`

Converts a value to true/false/null by comparing it against reference values.

Signature: ["set_boolean", logic, "true_value", "false_value?"]

Case	Behavior
Without `false_value`	`true` if it matches `true_value`, `false` in any other case
With `false_value`	`true`, `false`, or `null` if it matches neither

{"set_boolean": [{"var": "status"}, "active"]}

With explicit false value:

{"set_boolean": [{"var": "flag"}, "SI", "NO"]}

Arrays

`in`

Returns true if a value is contained in the result of the logic expression.

Signature: ["in", "value", logic]

{"in": ["admin", {"var": "roles"}]}

`flatmap`

Flattens an array of objects by injecting parent context fields into each element.

Signature: ["flatmap", logic_array, context_fields?]

context_fields is a list of field specifications from the parent object to inject into each item:

{
  "flatmap": [
    {"var": "items"},
    [
      {"field": {"var": "order_id"}, "as": "order_id"},
      {"field": {"var": "customer_name"}, "as": "customer"}
    ]
  ]
}

Without context fields (just flattens the array):

{"flatmap": [{"var": "lines"}]}

Full example

Order transformation: renaming, date formatting, difference calculation, and type casting.

{
  "merge": [
    {
      "rename": [
        [{"var": "orderId"}, "id_pedido", "string"],
        [{"var": "totalAmount"}, "total", "number"],
        [{"var": "customerName"}, "cliente", "string"]
      ]
    },
    {
      "fecha_creacion": {
        "format_date": [{"var": "createdAt"}, "%Y-%m-%dT%H:%M:%S", "%d/%m/%Y", "America/Bogota"]
      }
    },
    {
      "estado": {
        "switch": [{"var": "status"}, "pending,Pendiente,confirmed,Confirmado,cancelled,Cancelado,Desconocido"]
      }
    },
    {
      "nombre_cliente": {
        "trim": [{"var": "customerName"}, " ", "all"]
      }
    }
  ]
}

Custom Operators

Renaming and casting

rename

Text

trim

split

join

substring

case

replace

switch

replace_if_condition

string_to_array

startsWith

endsWith