Cross Table Sum (CrossTableAggregateGenerator)

The Cross Table Sum generator sets the value of the column to the sum of the values of another column aggregated across rows that have a foreign key value that matches the primary key in the current record.

For example, in a users table, a total_transactions value is obtained from the transactions table by combining all of the transaction_amount values from rows that have a user_id value that matches the primary key value for the current users record.

Link object structure

The Cross Table Sum generator does not support linking or consistency. You cannot configure differential privacy.

The metadata object is populated from the CrossTableAggregateMetadata object.

The generator-specific configuration includes:

• The schema and table that contain the column to sum against.

• The foreign key column to compare against the primary key for the current table.

• The column that contains the values to sum.

• The primary key column in the current table.

{
  "schema": "string",
  "table": "string",
  "column": "string",
  "metadata": {
    "generatorId": "CrossTableAggregateGenerator",
    "foreignSchema": "string",
    "foreignTable": "string",
    "foreignKeyColumns": [ "string" ],
    "sumColumn": "string",
    "primaryKeyColumns": [ "string" ],
    "encryptionProcessor": "x-on", //To use configured Structural data encryption
    "customValueProcessor": "string" //If custom value processor applied 
  }
}

Example replacement

In the following example replacement for the Cross Table Sum generator, the value of total_transactions in the users table is set to the sum of the values of the amount column in the transactions table for rows where user_id has the same value as the id column in the current users table row.

{
  "name": "total-transactions",
  "schema": "public",
  "table": "users",
  "links": [
    {
      "schema": "public",
      "table": "users",
      "column": "total_transactions",
      "metadata": {
        "generatorId": "CrossTableAggregateGenerator",
        "foreignSchema": "public",
        "foreignTable": "transactions",
        "foreignKeyColumns": [ "user_id" ],
        "sumColumn": "amount",
        "primaryKeyColumns": [ "id" ]
      }
    }
  ]
}