# 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](https://docs.jigx.com/examples/readme/actions/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. {% code title="Operations" %} ```yaml operations: - table: =@.entity 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) } ``` {% endcode %} ## Operation Property {% hint style="warning" %} 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. {% endhint %}

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: =@ctx.datasources.conversions`, 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 (`=@ctx.record.CustomerID`) 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	`=@ctx.record.{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
          }
        })
      )