Operations

Operations in a Jigx function allow you to manipulate data after a REST call completes. They are used to process the response and store it across one or more local SQLite tables. Operations run automatically after the REST function executes and can be defined for both success and error scenarios.

When and where to use operations

operations can be defined in the following places:

• Under the operations property in the main function.

• Within guard functions.

• Inside the error block for error-specific handling.

operations are particularly useful when:

• The REST response must be split and inserted into multiple tables.

• You need to control how data is stored in the local SQLite tables, e.g., merge, replace, or delete-insert.

• You want to run custom SQL after a function executes.

Multi-table operations

Specify multiple output tables and operations for both success and error scenarios.

• Use the operations property to specify an array of operations (executed in sequence) at the top level of the function or under an error handler.

• Each operation specifies an operation type, target table, and the records to use for the operation on the table.

• If the type is execute_sql you can specify an array of statements, similar to the action.execute-sql in a jig.

• If you've prepared data using outputTransform, you must reference the output in your operations or adjust your records to account for the transformed structure. In this case, operations will use ctx.output, the result of outputTransform becomes ctx.output.

• Each operation targets a table and specifies what records to insert or update. The records property can use JSONata expressions to transform the response data.

Operations

operations:
  - table: [email protected]
    type: operation.upsert-merge
    records: |
      =$each($.response.body, function($v, $k) {
        $merge([{ "id": $k}, $v])
      })
  - table: local-flags
    type: operation.delete-insert
    records: |
      =$each(response.body, function($v, $k) {
          {
              "id": $k,
              "name": $v.name,
              "flag": $v.emoji
          }
      })
  - table: local-counts
    type: operation.upsert-replace
    records: |
      ={
        'id': 'local-countries',
        'count': $count(@ctx.queries.existing-countries)
      }

Operation Property

Operations are not aware of conversions configured at the function level. Each operation must define its own conversions property to handle file format transformations. Function-level conversions do not cascade to individual operations.

Property	Description
conversions	Converting files runs per operation. This holds an array of properties that should be converted. The following properties control the conversion: property: The name of the property to convert. from: Format of the input data. It can be buffer, base64, data-uri, or local-uri. to: Format of the converted data. It can be base64, data-uri, buffer, or local-uri. convertHeicToJpg: When set to true, and the file being converted is HEIC, it is converted to JPG. Conversions can be set up as a static array of definitions or dynamically as an array returned by an expression. To set up dynamic conversions, use the expression: conversions: [email protected], applicable to both local and global actions. See File handling for details on using conversions in REST functions.
forRowsWithValues	Only applies to operation.delete-insert
forRowsInRange	Only applies to operation.delete-insert
parameters	Only applies to operation.execute_sql. The parameters used in the above statement.
primaryKey	Specify the remote data column that must be used as the primary key in the local data, such as Customer ID. This allows you to specify an expression ([email protected]) to define the primary key, including the ability to reference the whole record. This enables the building of dynamic keys, for example, by concatenating multiple fields from the record to construct an ID, timestamp, or GUID.
records	What records to use for the table operation, can manipulate data. Evaluates the expression against the function inputs and result to construct the records for the specified table. The constructed data will be stored in the table.
statements	Only applies to operation.execute_sql. List of statements to execute in sequence. Multiple statements can be configured to execute in sequence. statement - the SQL statement to execute against the solution database.
tables	Only applies to operation.execute_sql. The tables affected by the statements. Before executing the statements, a check ensures that the tables exist. After execution any datasources that use these entities will be notified that the database was changed.
timeStamp	Use a timestamp such as last modified from the remote data that must be used as the timestamp in the local table.
type	See the table operations types in the table below.
when	Specify the condition under which the function should execute (default) and when it should be skipped.

Table Operation Types

Applies to the operations that are performed on the local data from the remote data.

Type	Property	Description
DELETE_INSERT (used to sync data)	operation.delete_insert	Deletes old records that match a specified range and rows with matching values and inserts new records. Deletion runs at the beginning of the function. The table is only cleared on non-continuation operations on the first call. Uses this to overwrite the local data with the remote data.
UPSERT_REPLACE	operation.upsert_replace	Appends new records and replaces matching existing records. (Continuous update). The data of any existing record is replaced with the data from the matched new record. Records are matched by id (primary key). Use this type to append records to the local data and replace any existing records with the same id, leaving unmatched records unchanged and undeleted.
UPSERT_MERGE (used on CRUD methods and output transform)	operation.upsert_merge	Appends new records and merges matching existing records. The data of any existing record is merged with the data from the matched new record. Only top-level properties are copied, overwriting any existing top-level properties. Records are matched by id (primary key). Use this to append records to the local data and merge any existing records with the same id, leaving unmatched records unchanged and undeleted.
EXECUTE_SQL	operation.execute_sql	Executes a custom SQL operation specified with the SQL statement and parameters. This is the same functionality as the execute-sql action, which runs when a button is tapped; here, it runs when the function finishes or on continuation.

Expressions

records	[email protected].{primaryKey}	Defines the primary key, including the ability to reference the full record.

Example code

The example code below defines a series of database operations that process and store folder data from the ClickUp REST API.

operations:
# Only when there's NO X-opsPrimaryKey parameter (bulk processing mode) then,
# Deletes all existing records from folders-split-queries table
# Inserts new records by transforming each folder from $.folders array.
# Maps folder data to a flattened structure with space information included.
  - type: operation.delete-insert
    table: folders-split-queries
    when: =$not($boolean(@ctx.parameters.X-opsPrimaryKey))
    records: |
      =$.folders.{
        "id": id,
        "name": name,
        "orderIndex": orderindex,
        "overrideStatuses": override_statuses,
        "hidden": hidden,
        "archived": archived,
        "task_count": task_count,
        "spaceId": space.id,
        "spaceName": space.name
      }
   # Store Specific Folder (Primary Key Mode)
   # Only when X-opsPrimaryKey parameter exists (single record mode), then
   # Uses either the provided primary key OR the output name as the primary key.
   # Targets a specific record for update rather than bulk processing.
   # Same data transformation as above but for targeted updates.  
  - primaryKey: >
      =$boolean(@ctx.parameters.X-opsPrimaryKey) ?
      @ctx.parameters.X-opsPrimaryKey:@ctx.output.name
    type: operation.delete-insert
    table: folders-split-queries-primary
    when: =$boolean(@ctx.parameters.X-opsPrimaryKey)
    records: |
      =$.folders.{
        "id": id,
        "name": name,
        "orderIndex": orderindex,
        "overrideStatuses": override_statuses,
        "hidden": hidden,
        "archived": archived,
        "task_count": task_count,
        "spaceId": space.id,
        "spaceName": space.name
      }
  # Iterate through each folder,
  # for each list within a folder, creates a record linking the list to its 
  # parent folder. Processes lists that don't belong to any folder.
  # Sets folderId and folderName to null
  # Uses $append() to merge folder-based lists and folderless lists into a single array.
  - type: operation.delete-insert
    table: lists-split-queries
    # Convert list thumbnails from buffer to local-uri for efficient storage
    # Operations are not aware of conversions configured at the function level. 
    # Each operation must define its own conversions property to handle file 
    # format transformations. 
    # Function-level conversions do not cascade to individual operations.
    conversions:
      - property: listThumbnail
        from: buffer
        to: local-uri
        convertHeicToJpg: true
    records: |
      =$append(
        $reduce(folders, [], function($acc, $folder) {
          $append($acc,
            $map($folder.lists, function($list) {
              {
                "listId": $list.id,
                "listName": $list.name,
                "listThumbnail": $list.thumbnail,
                "folderId": $folder.id,
                "folderName": $folder.name
              }
            })
          )
        }),
        $map(@ctx.queries.folderless-lists, function($list) {
          {
            "listId": $list.listId,
            "listName": $list.listName,
            "listThumbnail": $list.thumbnail,
            "folderId": null,
            "folderName": null
          }
        })
      )