Skip to main content

JSON to table tutorial

Tutorial to transform JSON to fields in a different table

Requirements:

  • Ensure the FairCom server is installed and running.

  • Confirm server access by running the FairCom API Explorer. The typical URL is https://localhost:8443/ .

Tutorial to transform JSON to fields in a different table

tutorialJSON to tabletransform methodJSON transformJSON to different table

Create a "jsonToDifferentTableFields" transform

  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": "DOUBLE"
           },
           {
             "propertyPath": "humidity",
             "name": "humidity",
             "type": "DOUBLE"
            },
            {
              "propertyPath": "pressure_hPa",
              "name": "pressure",
              "type": "DOUBLE"
             },
             {
               "propertyPath": "gas_resistance_Ohms",
               "name": "quality",
               "type": "DOUBLE"
              },
              {
                "propertyPath": "sensor",
                "name": "sensor",
                "type": "VARCHAR",
                "length": 300
               }
             ]
           }
         }
       ]
     },
   "authToken": "replaceWithAuthTokenFromCreateSession"
  }

  2. Click Apply defaults to JSON request (Apply.PNG) to replace the "authToken" with a valid token from your session.

  3. Click Send request (Runbutton.png).

  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.

Configure a topic with a created transform

{
  "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"
}

Modify the schema of an external table created for the "jsonToDifferentTableFields" transform

{
  "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"
}

Create the integration table

  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 (Apply.PNG) to replace the "authToken" with a valid token from your session.

  3. Click Send request (Runbutton.png).

  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.

Add records to the integration table

  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 (Apply.PNG) to replace the "authToken" with a valid token from your session.

  3. Click Send request (Runbutton.png).

  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.

View the results in the table records

  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

Modify the existing "jsonToDifferentTableFields" transform to be compatible with a new schema

{
  "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": "DOUBLE"
            },
            {
              "propertyPath": "humidity",
              "name": "humidity",
              "type": "DOUBLE"
            },
            {
              "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"
}