forRowsWithMatchingids

By default, the JSON payload returned from the REST call replaces all existing data in the SQLite database. However, the forRowsWithMatchingIds property controls how data from REST API responses is synchronized with the local SQLite database.

This approach is useful for incrementally syncing data without affecting unrelated records, making it ideal for scenarios where partial updates or record-level inserts are required.

Understanding data sync behavior

The behavior of data synchronization depends on whether forRowsWithMatchingIds and explicit operations are specified. When using explicit operations, you lose the automatic upsert-merge behavior provided with the forRowsWithMatchingIds and must define all desired operations yourself. The id field in your outputTransform is critical for matching records. There are three scenarios:

Scenario	Combinations	Results
Scenario 1 (Default)	No forRowsWithMatchingIds + no operations	Wipe and sync
Scenario 2 (Automatic)	forRowsWithMatchingIds: true + no operations	Automatic upsert-merge
Scenario 3 (Manual)	forRowsWithMatchingIds + explicit operations	You control the behavior

Scenario 1: No forRowsWithMatchingIds, no operations (Default behavior)

Use case: When you want to completely refresh the table contents.

When neither forRowsWithMatchingIds nor explicit operations are specified, Jigx performs a wipe and sync:

• Deletes all existing records in the table
• Inserts all new records from the API response

This is the standard default operation that completely replaces the table contents.

provider: DATA_PROVIDER_REST
method: GET
url: https://api.example.com/data
useLocalCall: true
outputTransform: |
  {
    "data": $.items
  }
# No forRowsWithMatchingIds specified
# No operations specified
# Result: Complete table replacement (wipe and sync)

Scenario 2: forRowsWithMatchingIds specified, no operations

Use case: Use for simple incremental syncing without deletions.

When forRowsWithMatchingIds: true is specified without any explicit operations, Jigx automatically performs an upsert-merge operation:

• Updates existing records where the id matches
• Inserts new records where no matching id exists
• Preserves existing records that are not in the API response (no deletion)

The outputTransform must include a field named id, which Jigx uses to match against the id column in the target table.

provider: DATA_PROVIDER_REST
method: GET
url: https://{api.example.com}/data
useLocalCall: true
# Automatically performs upsert-merge when no operations are specified
forRowsWithMatchingIds: true

outputTransform: |
  {
    "earthquakes": features[].{
      "id": id,
      "place": properties.place,
      "mag": properties.mag,
      "time": properties.time,
      "coordinates": geometry.coordinates,
      "depth": geometry.coordinates[2]
    }
  }
# No operations specified - automatic upsert-merge behavior applies.

Scenario 3: forRowsWithMatchingIds with explicit operations

Use case: When you need custom logic, such as handling server-side deletions or complex synchronization.

IMPORTANT: When you specify explicit operations, forRowsWithMatchingIds loses its automatic upsert-merge behavior. You must explicitly define the operations you want to perform.

This scenario is useful when you need custom synchronization logic, such as:

• Deleting records that no longer exist on the server
• Combining upsert with cleanup operations

provider: DATA_PROVIDER_REST
method: GET
useLocalCall: true
url: https://{api.example.com}/ExpenseReceipt
records: =$.data
# forRowsWithMatchingIds is used with explicit operations.
forRowsWithMatchingIds: true

outputTransform: >-
  ={
    "top": @ctx.parameters."$top",
    "skip": @ctx.parameters."$skip",
    "data": @ctx.response.body ? $map(@ctx.response.body, function($item){
      $merge([$item, { "Remote": true }])
    }) : []
  }

operations:
  # First operation: 
  # Delete local records that no longer exist on the server.
  - type: operation.execute-sql
    statements:
      - statement: |
          ="DELETE FROM [expense-receipts] WHERE [id] NOT IN (" & 
          $join($map(@ctx.response.body.id, function($v) { "'" & $v & "'" }), ", ") & 
          ")"
    tables:
      - expense-receipts
  
  # Second operation: 
  # Explicitly define upsert-merge for matching records.
  - type: operation.upsert-merge
    table: expense-receipts
    records: |
      =@ctx.response.body ? $map(@ctx.response.body, function($item){
        $merge([$item, { "Remote": true }])
      }) : []

Considerations

• Always ensure your outputTransform includes an id field when using forRowsWithMatchingIds.
• When using explicit operations (Scenario 3), carefully plan your operation sequence.
• Test your synchronization logic thoroughly, especially when handling deletions.