The purpose of the jsonToDifferentTableFields transform is to take a JSON document stored in the "source_payload" field or one of the transform fields in an integration table, extract values from the JSON properties, and put these values into fields in another integration table.
The primary use case for extracting values from JSON properties and putting them in table fields is to enable SQL queries to use the collected data for analytics, machine learning, troubleshooting, queries, reporting, sending data to Thingworx, and supporting ETL jobs to send data to external systems, and so forth.
The "jsonToDifferentTableFields" transform method can also be used to do the following:
- Consolidate Data: FairCom Edge can consolidate data inserted into multiple tables into a single table (e.g., combining temperature data from various devices).
- Route Data to Multiple MQTT Topics: FairCom Edge can copy incoming data from one table to additional tables, delivering that data to multiple MQTT topics.
- Replicate Data for Intensive Queries: FairCom Edge can copy incoming data from one table to another, allowing data scientists and engineers to run intensive queries without impacting data collection speed.
Examples
The SQL examples in this section show how SQL can add value to collected data.
Return records where the temperature is greater than 60
SQL can filter records based on field values.
- Navigate to the Data tab.
- Select SQL Commands from the Data menu.
- Paste this code into the SQL Commands editor and click the Run button:
SELECT temperature_whole FROM air_quality_sensor WHERE temperature_whole > 60;
Combine the whole and fractional parts of a temperature to compute a temperature value
SQL can create calculated values from fields. This example returns records where the computed temperature is greater than 80.
- Navigate to the Data tab.
- Select SQL Commands from the Data menu.
- Paste this code into the SQL Commands editor and click the Run button:
SELECT (temperature_whole + temperature_fraction) AS temperature FROM air_quality_sensor WHERE (temperature_whole + temperature_fraction) > 80;
Create the integration table named air_quality_sensor.
If you want to run the previous SQL examples, paste this JSON into the API Console.
- Navigate to the Code tab.
- Select API Console from the Code menu.
- Paste this code into the API Console and click the Run button:
{
"api": "hub",
"authToken": "replaceWithAuthTokenFromServer",
"action": "createIntegrationTable",
"params": {
"databaseName": "faircom",
"tableName": "air_quality_sensor",
"fields": [
{
"name": "temperature_whole",
"type": "integer"
},
{
"name": "temperature_fraction",
"type": "integer"
}
],
"metadata": {"description": "Custom integration table for air quality"},
"retentionPeriod": 30,
"retentionUnit": "day"
}
}
Add a record to this table
Run these SQL insert statements to add records to this table.
- Navigate to the Data tab.
- Select SQL Commands from the Data menu.
- Paste this code into the SQL Commands editor and click the Run button:
INSERT INTO air_quality_sensor (temperature_whole, temperature_fraction) VALUES(70,3); INSERT INTO air_quality_sensor (temperature_whole, temperature_fraction) VALUES(80,9);
You may also run the JSON to external table transform tutorials to learn how to create a transform that automatically creates an integration table and inserts transformed records into it when the source integration table receives records.