Use cases

Use cases for the JSON to fields transform method

Abstract

use cases for the JSON to fields transform method

The purpose of the jsonToTableFields 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 of that table.

The primary use case for doing this 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.

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.

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.

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, you can use this JSON action.

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