Skip to main content

JSON to different table API reference

API reference for the JSON to different table transform method

Request examples

Minimal request

{
    "api": "hub",
    "action": "createTransform",
    "params": {
        "transformName": "TestTransform2",
        "transformActions": [
            {
                "inputFields": [
                    "In1"
                ],
                      ],
                "transformStepMethod": "jsonToDifferentTableFields",
                "transformParams": {
                    "targetDatabaseName": "faircom",
                    "targetTableName": "test_out2",
                    "mapOfPropertiesToFields": [
                        {
                            "propertyPath": "out1",
                            "name": "out1",
                            "type": "VARCHAR",
                            "length": 200
                        }
                    ]
                }
            }
        ]
    },
    "authToken": "replaceWithAuthTokenFromCreateSession"
}

Mapping a single JSON property to multiple fields

{
  "api": "transform",
  "action": "createTransform",
  "params": {
    "transformName": "singlePropertyToMultipleFieldsTranform",
    "transformActions": [
      {
        "inputFields": [
          "source_payload"
        ],
        "outputFields": [
          "temperature_a",
          "temperature_b",
          "temperature_c"
        ],
        "transformStepMethod": "jsonToTableFields",
        "transformParams": {
          "targetTableName": "my_table",
          "mapOfPropertiesToFields": [
            {
              "propertyPath": "temperature",
              "name": "temperature_a",
              "type": "DOUBLE"
            },
            {
              "propertyPath": "temperature",
              "name": "temperature_b",
              "type": "DOUBLE"
            },
            {
              "propertyPath": "temperature",
              "name": "temperature_c",
              "type": "DOUBLE"
            }
          ]
        }
      }
    ]
  },
  "requestId": "00000002",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Maximal request

Tip

Creating the transform's target table before creating the transform enables you to create fields with more precise types than the default types created by the transform, such as NUMBER or INTEGER rather than DOUBLE.

{
  "api": "hub",
  "apiVersion": "1.0",
  "requestId": "00000006",
  "action": "createTransform",
  "params": {
    "transformName": "TestTransform2",
    "transformActions": [
      {
        "inputFields": [
          "In1"
        ],
        "outputFields": [
          "out1",
          "out2"
        ],
        "transformService": null,
        "transformStepMethod": "jsonToDifferentTableFields",
        "transformParams": {
          "targetDatabaseName": "faircom",
          "targetTableName": "test_out2",
          "newTargetTableFeatures": {
            "integrationTable": true,
            "retentionPolicy": "autoPurge",
            "retentionPeriod": 30,
            "retentionUnit": "day"
          },
          "mapOfPropertiesToFields": [
            {
              "propertyPath": "out1",
              "name": "out1",
              "type": "VARCHAR",
              "length": 200
            },
            {
              "propertyPath": "out2",
              "name": "out2",
              "type": "DOUBLE"
            }
          ]
        },
        "metadata": {
          "myTag": "my data"
        }
      }
    ]
  },
  "responseOptions": {
    "binaryFormat": "base64",
    "dataFormat": "arrays",
    "numberFormat": "number"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Note

The jsonToDifferentTableFields transform will automatically add the following fields to the target table if they do not already exist. When the transform inserts a new row into the target table, it auto-populates these fields:

  • change_id

  • create_ts

  • id

  • out1

  • out2

  • source_database

  • source_id

  • source_schema

  • source_table

If any existing field or index has the same name as one of these but with a mismatched schema definition, the transform will fail and prompt the user to fix this conflict.

Response example

{
    "result": {},
    "requestId": "00000007",
    "errorCode": 0,
    "errorMessage": "",
    "authToken": "replaceWithAuthTokenFromCreateSession"
}

API reference for the JSON to different table transform method

API referenceJSON hub APIJSON transformJSON to different tabletable transform

"params"

The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties. See System limits for a comprehensive look at property requirements and limitations.

Property summary

Table 1. "params" property summaries

Property

Description

Default

Type

Limits

transformActions

specifies "transformAction" objects

Required - No default value

array of objects

transformActions
.transformParams
.newTargetTableFeatures

(optional) specifies features of the table the transform creates if the specified "targetTableName" does not already exist in the database

{}

object

transformActions
.transformParams
.newTargetTableFeatures
.integrationTable

(optional) specifies whether or not the created table will be an integration table

true

Boolean

true
false
transformActions
.transformParams
.newTargetTableFeatures
.retentionPeriod

(optional) specifies how many units of data to retain

4

integer

1 to 100

transformActions
.transformParams
.newTargetTableFeatures
.retentionPolicy

(optional) controls how messages are persisted

"autoPurge"

string

"autoPurge"
"neverPurge"
transformActions
.transformParams
.newTargetTableFeatures
.retentionUnit

(optional) purges expired messages each time this unit cycles

"week"

string

"minute"
"hour"
"day"
"week"
"month"
"year"
"forever"
transformActions
.transformService

(optional) specifies the name of the transform service

Required when "transformStepMethod" is "javascript"; otherwise, it is optional with no default value

string

"javascript"
"siemensUdtPayloadToJson"
"v8TransformService"
transformActions
.transformStepMethod

specifies the name of the transform action

Required - No default value

string

"javascript"
"jsonToTableFields"
"tableFieldsToJson"
"jsonToDifferentTableFields"
"siemensUdtPayloadToJson"
transformActions
.transformStepName

(optional) allows the user to assign a name to each transform step

""

string

0 to 250 bytes

transformName

specifies the name of a transform process. The name cannot be one of the FairCom-provided transform names

Required - No default value

string

1 to 64 bytes



"transformActions"

The "transformActions" property is an optional array of "transformAction" objects. It defaults to an empty array.

"transformParams"

The "transformParams" property is a required object that can be an empty object or contain one or more properties needed by the "transformAction".

"mapOfPropertiesToFields"

The "mapOfPropertiesToFields" property is a required array of "mapOfPropertiesToFields" objects. It defaults to an empty array.

Example
"mapOfPropertiesToFields": [
  {
    "propertyPath": "out1",
    "name": "out1",
    "type": "VARCHAR",
    "length": 200
  }
]
"propertyPath"

The "propertyPath" property is a string that specifies the property that will be read in JSON.

"name"

The "name" property is a string that specifies the destination in the integration table for the data that was read from the propertyPath.

"type"

The "type" property specifies the type of the integration table being written to.

"length"

The "length" property is an integer that specifies the length of the "name" property.

"transformActions"."transformParams"."newTargetTableFeatures"

The "newTargetTableFeatures" property specifies features of the table the transform creates if the specified "targetTableName" does not already exist in the database. It only applies when the server creates a new table.

"transformActions"."transformParams"."newTargetTableFeatures"."integrationTable"

The "integrationTable" property specifies whether or not the created table will be an integration table. It defaults to true. Set to false when you want the action to create a non-integration table because you do not want it to run transforms, forward events to outputs, and contain the integration table fields.

"retentionPeriod"

The "retentionPeriod" property specifies how many units of data to retain. It must be an integer value from 1 to 100. It refers to the unit of time specified by the "retentionUnit" property — for example, if "retentionPeriod" is 14 and "retentionUnit" is "day", then message data is retained for 14 days. This property is optional.

  • Periodically, the system increments the age of each table partition.

    • "Minute" units are incremented at the zero second of each minute, whether the table partition was created 1 second before or 59 seconds before.

    • "Day" units are incremented at midnight GMT, not midnight of the local UTC time zone. A table partition becomes one day old at midnight GMT, whether it was created one second before or 23 hours and 59 seconds before.

    • "Week" units are incremented at midnight GMT on the last day of each week, even if the table partition was created one second before. The last day of the week is Saturday GMT.

    • "Month" units are incremented at midnight GMT on the last day of each month.

    • "Year" units are incremented at midnight GMT on Dec 31.

  • "retentionPeriod" implicitly calculates an upper bound on how many partitions are able to accumulate on your hard drive. However, partitions are not deleted just because this calculated number of partitions is reached. The system also does not restrict the deletion of all partitions.

  • If the FairCom database is shut down for a month, when the database is started up again, all partitions that are retained for less than one month are immediately deleted.

  • If someone purposely or accidentally sets the date to a future date, all partitions immediately become older, and auto-deletion ensues accordingly.

When partitions are auto-purged, some data are maintained "forever" in a global index. Auto-purge does not prevent these files from growing without bounds and filling up your hard drive.

If not specified, the default found in the services.json file is used. Initially, it is 4 (weeks).

Automatically purging data is important to ensure that retained data does not consume all storage and shut down the computer. The default value of 4 weeks allows FairCom's servers to store 1 TB of messages when 200 topics send one 2K message per second.

Note

  • If the value is not an integer from 1 to 100, FairCom's servers set it to the default value.

  • Smaller numbers improve SQL performance.

  • Each time the "retentionPeriod" cycles, FairCom's servers automatically and efficiently delete expired data.

  • The "retentionUnit" and "retentionPeriod" properties are used only when the "retentionPolicy" is set to "autoPurge".

  • FairCom's servers only use the "retentionPeriod" property when the "retentionPolicy" is "autoPurge".

  • The "retentionPeriod" can be changed to retain fewer or more messages. Changing it does not necessarily destroy existing data, but data may expire more quickly or be retained longer.

  • The "retentionPeriod" and "retentionUnit" properties control data granularity as well as the retention time.  In other words, "retentionPeriod" defines how many sets of data are stored, and "retentionUnit" defines how often data is purged.

    For example, if "rententionPeriod" is set to 14 , the server stores 14 sets of data. At the beginning of the 15th cycle, the server automatically purges the oldest set of data. If "retentionUnit" is set to day, then data will be purged daily. If set to "week", then data will be purged weekly.

  • The current calendar date affects purging.

    FairCom's servers automatically purge all retained data that has expired. This is noticeable when FairCom's servers come online after having been offline for a long time. When a server comes back online, it automatically purges all expired data.

    For example, if a FairCom server is offline for four weeks when it comes online, it will completely purge all retained data that has a retention time of less than 4 weeks.

"retentionPolicy"

The "retentionPolicy" property controls how messages are persisted. This property is optional.

If not specified, the default found in the services.json file is used. Initially, it is "autoPurge".

retentionPolicy values:

  • "autoPurge"

    This is the default. It is automatically applied when a new topic is created. It is preferred because it allows FairCom's servers to automatically remove messages that are older than the retention time. This helps ensure message data does not consume all storage space. It also minimizes storage costs and speeds up data access. The server partitions a table into multiple files so it can efficiently delete expired files.

  • "neverPurge"

    This stores messages on disk and never removes them. This is useful when you need the entire history of the message stream. If message velocity is high, this can consume all storage space and cause an outage. The server creates a non-partitioned table, which is slightly faster than a partitioned table because it stores all records in one file.

"retentionUnit"

Each time this unit cycles, FairCom purges expired messages. For example, if you want a week's worth of messages to be purged once a week, set "retentionUnit" to "week" . This property is optional.

If not specified, the default found in the services.json file is used. Initially, it is "week". 

  • This property is used in concert with "retentionPeriod" to determine retention time.

  • "retentionUnit" values:

    • "minute"

    • "hour"

    • "day"

    • "week"

    • "month"

    • "year"

    • "forever"

Note

  • For best performance, set the "retentionUnit" to a value that keeps "retentionPeriod" between 5 and 30. 

  • When you set "retentionUnit" property to "forever" the server will not purge messages. This setting is the same as setting "retentionPolicy" to "neverPurge".

  • The "retentionUnit" and "retentionPeriod" properties are used only when the "retentionPolicy" is set to "autoPurge".

"transformActions"."transformService"

The "transformService" property is an optional string that specifies the name of the transform service.

Note

If the "transformStepMethod" property is set to "javascript", the "transformService" property must be set to "v8TransformService".

"transformActions"."transformStepMethod"

The "transformStepMethod" property is a required string that specifies the type of transform, such as the "javascript" transform method that runs JavaScript to change the table's data or the "jsonToTableFields" transform method that extracts values from properties in a JSON field and stores them in other fields.

Note

The "transformStepMethod" property replaces the deprecated "transformActionName" property.

The value of the "transformStepMethod" affects the value of the "transformService" property. The following table defines the possible values of the "transformService" property when combined with the "transformStepMethod". Notice that some transform step methods are built into the server and do not require you to specify the "transformService".

"transformStepMethod" value

"transformService" value

Definition

"jsonToTableFields"

null or omitted

"tableFieldsToJson"

null or omitted

"jsonToDifferentTableFields"

null or omitted

"javascript"

"v8TransformService"

The "javascript" transform method runs JavaScript code each time a record is inserted into an Integration table. The code can read the value of any field and write values to any field except the built-in fields, such as source_payload. See this tutorial.

"siemensUdtPayloadToJson"

"siemensUdtPayloadToJson"

Note

If the "transformStepMethod" property is set to "javascript", the "transformService" property must be set to "v8TransformService".

"transformActions"."transformStepName"

The "transformStepName" property is an optional string that allows the user to assign a name to each transform step.