JSON to different table API reference

API reference for the JSON to different table transform method

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.

 

Request examples

Minimal

{
  "api": "hub",
  "apiVersion": "1.0",
  "action": "createIntegrationTable",
  "params": {
    "tableName": "test1",

    "fields": [
      {
        "name": "In1",
        "type": "varchar"
      }
    ],
    "transformSteps": [
      {
        "transformStepMethod": "jsonToDifferentTableFields",
        "targetTableName": "test_out2",
        "mapOfPropertiesToFields": [
            {
                "recordPath": "in1.out1",
                "fieldName": "out1"
            }
        ]
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

The following example is not the maximal example for creating an integration table; rather, it is the maximal example for creating a JSON to different table transform.

{
  "api": "hub",
  "apiVersion": "1.0",
  "action": "createIntegrationTable",
  "params": {
    "tableName": "test1",

    "transformSteps": [
      {
        "transformStepMethod": "jsonToDifferentTableFields",
        "targetTableName": "my_table",
        "mapOfPropertiesToFields": [
            {
                "recordPath": "source_payload.temperature",
                "fieldName": "temperature_a"
            },
            {
                "recordPath": "source_payload.temperature",
                "fieldName": "temperature_b"
            },
            {
                "recordPath": "source_payload.temperature",
                "fieldName": "temperature_c"
            }
        ]
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Request properties ("params")

Property Description Default Type Limits (inclusive)

tableName

The "tableName" property contains the unique, user-defined name of a table.

See table name in System specifications for the table naming requirements and limitations.

 

"params": {
  "tableName": "ctreeTable"
}
 Required - No default value string 1 to 64 bytes

transformSteps

 The "transformSteps" property specifies an array of transform objects. Required - No default value array of objects

0 or more objects containing 1 or more of the following properties:

"codeName"

"databaseName"

"ownerName"

"mapOfPropertiesToFields"

"targetDatabaseName"
"targetOwnerName"

"targetTableName"

"transformStepMethod"

"transformStepName"

"transformStepService"

transformSteps

.mapOfPropertiesToFields

The "mapOfPropertiesToFields" property takes fields in the table and maps them to a field containing JSON properties. Each object maps a field in a table to a JSON property in another field.

  • Required properties
    • "recordPath"
    • "fieldName"
  • Optional properties
    • "binaryFormat"
    • "numberFormat"
    • "variantFormat"
    • "dateFormat"
    • "timeFormat"
 Optional with default of [] array

"binaryFormat"

"dateFormat"

"fieldName"

"numberFormat"

"recordPath"

"timeFormat"

"variantFormat"

transformSteps

mapOfPropertiesToFields

.fieldName

 The "fieldName" property specifies the name of a field in a table. Required - No default value string 1 to 64 bytes

transformSteps

mapOfPropertiesToFields

.recordPath

The "recordPath" property specifies the location in a record where the server reads or writes a JSON value. It specifies a field name followed by an optional JSONPath. For more details, see "recordPath".

 

 Required - No default value string 0 to 256 bytes

transformSteps

.targetTableName

The "targetTableName" property specifies the name of the target table that has its transform steps replaced by this action.

Note The fully qualified name of a table includes the database, owner, and table names.

 Optional with default of "" string 1 to 64 bytes

transformSteps

.transformStepMethod

 The "transformStepMethod" property 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. For more details, see "transformStepMethod". Required - No default value string enum

"javascript"

"jsonToDifferentTableFields"

"jsonToTableFields"

"tableFieldsToJson"

 

Response examples

Success

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

 

json conversion table endpoint

Was this article helpful?

Give feedback about this article

Related Articles