JSON to table tutorial

1. Replace the JSON in the API Request editor with the following JSON:
   

   {
     "api": "hub",
     "action": "createTransform",
     "params": {
       "transformName": "BME280Transform2",
       "transformActions": [
         {
         "inputFields": [
         "source_payload"
         ],
         "transformStepMethod": "jsonToDifferentTableFields",
         "transformParams": {
           "targetDatabaseName": "ctreeSQL",
           "targetTableName": "bme2802",
           "mapOfPropertiesToFields": [
             {
               "propertyPath": "temperature_F",
               "name": "temperature",
               "type": "float"
              },
              {
                "propertyPath": "humidity",
                "name": "humidity",
                "type": "float"
               },
               {
                 "propertyPath": "pressure_hPa",
                 "name": "pressure",
                 "type": "float"
                },
                {
                  "propertyPath": "gas_resistance_Ohms",
                  "name": "quality",
                  "type": "float"
                 },
                 {
                   "propertyPath": "sensor",
                   "name": "sensor",
                   "type": "varchar",
                   "length": 300
                  }
                ]
              }
            }
          ]
        },
      "authToken": "replaceWithAuthTokenFromCreateSession"
     }

2. Click Apply defaults to JSON request () to replace the "authToken" with a valid token from your session.
3. Click Send request ().
4. Observe the response and ensure the action completed successfully.
   

   Note

   "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.

    

   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.

    

{
  "api": "mq",
  "action": "configureTopic",
  "params": {
    "topic": "test1",
    "transformName": "BME280Transform2"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Publish some messages with the following payload in the topic you just created to exercise the transform and check the records created in "bme2802" in "ctreeSQL":

{
  "temperature_F": 90,
  "humidity": 35,
  "pressure_hPa":100,
  "gas_resistance_Ohms": 200,
  "sensor": "Sensor1"
}

{
  "api": "db",
  "action": "alterTable",
  "params": {
    "databaseName": "ctreeSQL",
    "tableName": "bme2802",
    "addFields": [
      {
        "name": "new_field",
        "type": "varchar",
        "length": 300,
        "nullable": false
      }
    ],
    "alterFields": [
      {
        "name": "sensor",
        "newName": "description"
      },
      {
        "name": "quality",
        "type": "integer"
      }
    ],
    "deleteFields": [
      "pressure"
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

1. Replace the "params" property values with the following "params" values:
   

   {
       "api": "hub",
       "action": "createIntegrationTable",
       "params": {
           "databaseName": "faircom",
           "tableName": "test2",
           "fields": [
               {
                   "name": "In1",
                   "type": "JSON",
                   "length": 100
               }
           ],
           "transformName": "TestTransform2"
       },
       "requestId": "00000006",
       "authToken": "replaceWithAuthTokenFromCreateSession"
   }
   

2. Click Apply defaults to JSON request () to replace the "authToken" with a valid token from your session.
3. Click Send request ().
4. Observe the response and ensure the action completed successfully."errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.
   

   Note

   Helpjuice Success Callout Body

    

1. Replace the JSON in the API Request editor with the following JSON:
   

   {
       "api": "db",
       "action": "insertRecords",
       "params": {
           "tableName": "test2",
           "dataFormat": "arrays",
           "databaseName": "faircom",
           "fieldNames": [
               "In1"
           ],
           "sourceData": [
               [
                   {
                       "out1": "Text data 1.",
                       "out2": 1
                   }],
                   [{
                       "out1": "Text data 2.",
                       "out2": 2
                   }
               ]
           ]
       },
       "authToken": "replaceWithAuthTokenFromCreateSession"
   }

2. Click Apply defaults to JSON request () to replace the "authToken" with a valid token from your session.
3. Click Send request ().
4. Observe the response and ensure the action completed successfully.
   

   Note

   "errorCode" with a value of 0 indicates success. "errorCode" with a non-zero value indicates a failure. See Errors and contact FairCom for more information about an error.

    

1. In the API Explorer navigation window, navigate to faircom>admin>Tables and select test_out2 .
   

   Note

   If you do not see test_out2, refresh Tables.

    
2. Click the Table Records tab.
3. Observe the out1 and out2 data fields that were extracted from the JSON object created in Add records to the integration table

{
  "api": "hub",
  "action": "alterTransform",
  "params": {
    "transformName": "BME280Transform2",
    "transformActions": [
      {
        "inputFields": [
        "source_payload"
        ],
        "transformActionName": "jsonToDifferentTableFields",
        "transformParams": {
          "targetDatabaseName": "ctreeSQL",
          "targetTableName": "bme2802",
          "mapOfPropertiesToFields": [
            {
              "propertyPath": "temperature_F",
              "name": "temperature",
              "type": "float"
            },
            {
              "propertyPath": "humidity",
              "name": "humidity",
              "type": "float"
            },
            {
              "propertyPath": "gas_resistance_Ohms",
              "name": "quality",
              "type": "integer"
            },
            {
              "propertyPath": "sensor",
              "name": "description",
              "type": "varchar",
              "length": 300
            },
            {
              "propertyPath": "sensor",
              "name": "new_field",
              "type": "varchar",
              "length": 300
            }
          ]
        }
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Publish some messages with the following payload in the topic created in this step to check the new records based on the new schema in the external table:

{
  "temperature_F": 90,
  "humidity": 35,
  "pressure_hPa":100,
  "gas_resistance_Ohms": 200,
  "sensor": "Sensor1"
}