This tutorial outlines how to transform data from JSON to fields in a different table.

Create an integration table

The integration table created by the following example will be the target table where the transformed data will be sent. 

{
  "api":       "hub",
  "action":    "createIntegrationTable",
  "params": {
    "databaseName": "faircom",
    "tableName":    "target_integration_table",
    "fields": [
      {
        "name": "target_table_destination_field",
        "type": "VARCHAR",
        "length": 200
      }
    ]
  },
  "authToken": ""
}

Create an integration table with a transform

The integration table created by the following example will be the source table where data will be collected and transformed. 

{
  "api":    "hub",
  "action": "createIntegrationTable",
  "params": {
    "tableName": "jsonToDifferentTableSource",
    "logTransformOverwrites": false,
    "fields": [
      {
        "name": "source_varchar_field",
        "type": "VARCHAR",
        "length": 200
      }
    ],
    "transformSteps": [
      {
        "transformStepMethod": "jsonToDifferentTableFields",
        "targetOwnerName":     "admin",
        "targetDatabaseName":  "faircom",
        "targetTableName":     "target_integration_table",
        "mapOfPropertiesToFields": [
          {
            "recordPath": "source_varchar_field.sourceProperty",
            "fieldName":  "target_table_destination_field"
          }
        ]
      }
    ]
  },
  "authToken": ""
}

Insert Records 

The following example inserts records into the source table

{
  "action": "insertRecords",
  "params": {
    "tableName": "jsonToDifferentTableSource",
    "dataFormat": "objects",
    "sourceData": [
      {
        "source_varchar_field": "{ \"sourceProperty\": \"First value\" }"
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

View records

To verify that your transform has worked, you can view the records in each table. 

First, check the source table (jsonToDifferentTableSource) in the table records tab to make sure the JSON records were inserted.

Second, check the target table (target_integration_table) in the table records tab and make sure the destination field "target_table_destination_field" contains the value from the JSON that was sent.