JSON to fields tutorial

Tutorial to transform JSON to fields in the same table

This tutorial creates a transform that enables a data engineer to run SQL analytical queries on collected data for machine learning, business intelligence, and analytical reports.

A transform modifies and enriches data collected by a FairCom server. The FairCom server automatically runs a transform when a connector, MQTT, JSON DB, or SQL inserts a record into an integration table assigned to the transform.

This tutorial's transform extracts a "t1" property from JSON data in the source_payload field and loads it into a table's temperature field. The following example shows a record after the transform has run.

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/ .
Familiarize yourself with how to run JSON actions in API Explorer.

Tutorial to transform JSON to fields in the same table

tutorialsJSONJSON to fieldstransformtransform method

Create a transform

This step creates a transform that extracts a t1 property from JSON data in the source_payload field and loads it into a table's temperature field. You can assign this transform to any integration table if the table has a temperature field with a double data type.

{
  "api": "hub",
  "action": "createTransform",
  "params": {
    "transformName": "jsonToTableFields source_payload t1 to temperature",
    "transformActions": [
      {
        "inputFields": [
          "source_payload"
        ],
        "outputFields": [
          "temperature"
        ],
        "transformActionName": "jsonToTableFields",
        "transformParams": {
          "mapOfPropertiesToFields": [
            {
              "propertyPath": "t1",
              "name": "temperature",
              "type": "DOUBLE"
            }
          ]
        }
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Create the integration table

This step creates an integration table that is compatible with the transform. It is named "temperature1" and has a temperature field with a double data type.

The "transformName" property assigns the previously created transform to the table.

{
  "api": "hub",
  "action": "createIntegrationTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "temperature1",
    "fields": [
      {
        "name": "temperature",
        "type": "double"
      }
    ],
    "transformName": "jsonToTableFields source_payload t1 to temperature"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Add records to the integration table

This step inserts a record into the "temperature1" table.

The FairCom server automatically runs the transform when a connector, MQTT, JSON DB, or SQL inserts a record into the "temperature1" table.

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "tableName": "temperature1",
    "databaseName": "faircom",
    "sourceData": [
      {
        "source_payload": 
        {
          "t1": 20.1
        }
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

View the results in the table records

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

Note

If you do not see temperature1, click the refresh Tables button.

Click on the inserted record.
Select the extracted record in the record viewer.
Click on the source_payload field's edit button to show its value.
Observe that the value is {"t1":20.1},