JSON DB API tutorial

FairCom's JSON DB API can be used to query MQTT data captured from IoT devices

These tutorials show how to interact with the FairCom Edge database server using the FairCom API Explorer web utility to pull, store, view, and use data from MQTT messages.

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/ .

Create a session

 
  1. Start the FairCom Browser-Based tools.
  2. Select Data Explorer.
  3. Connect to the server as user Admin.
  4. In the Server navigation window, right-click faircom to select Connect () from the dropdown menu.
  5. Click the API Explorer tab ().
 
 

Configure an MQTT topic to store JSON records

This procedure configures FairCom Edge to capture JSON-based MQTT data from the topic "MyTopic" and store it in the payload field of records in a table named "MyTopicTable" in the "faircom" database.

  1. Replace the JSON in the API Request editor with the following JSON:
{
 "api": "mq",
 "action": "configureTopic",
   "params": {
     "topic": "MyTopic",
     "databaseName": "faircom",
     "tableName": "MyTopicTable"
    },
 "authToken": "replaceWithAuthTokenFromCreateSession"
}

Note

The data we publish will be sent to the "MyTopic" topic and stored in the "MyTopicTable" of the "faircom" database.

 
  1. Click Apply defaults to JSON request () to replace the "authToken" value with the valid one from your session.
  2. Click Send request () to issue the JSON-based request.
  3. 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.

 
 
 

Access MQTT management

  1. Start the FairCom Browser-Based tools.
  2. Select MQ Explorer.
  3. Select MQTT Management from the Control menu ().
 
 

Publish JSON data to an MQTT topic

  1. Click the Publish tab.
  2. Enter MyTopic in the Topic text box.
  3. In the Message text box enter:
{
 "Property1": "Stuff goes here",
 "Property2": 39
}
  1. Click +Publish ().
 
 

View captured MQTT data

  1. Access the FairCom database in the FairCom Data Explorer.
  2. Navigate to and select mytopictable in the Server navigation window through faircom>admin>Tables.
  3. Click the Table Records tab ().

Note

Table records are displayed one record per row.

 
  1. Click the source_payload field for one of the listed records.
  2. Observe the pop-up window with the contents of the selected record.
  3. Click Cancel () to close the window.
 
 

Create a transform to extract captured data into table fields

  1. Access the API Explorer.
  2. Select the Transform API from the Select API dropdown menu ().
  3. Select createTransform from the JSON Actions dropdown menu.
  4. Replace the JSON in the API Request editor with the following JSON:
{
 "api": "transform",
 "action": "createTransform",
 "params": {
 "transformName": "MyTopicTransform",
 "transformActions": [
 {
   "inputFields": [
   "source_payload"
    ],
 "transformStepMethod": "jsonToDifferentTableFields",
 "transformParams": {
   "targetDatabaseName": "ctreeSQL",
   "targetTableName": "MyTopicTransformed",
   "mapOfPropertiesToFields": [
    {
     "propertyPath": "Property1",
     "name": "property_1",
     "type": "varchar",
     "length": 300
    },
    {
     "propertyPath": "Property2",
     "name": "property_2",
     "type": "float"
    }
  ]
 }
 }
 ]
 },
 "authToken": "replaceWithAuthTokenFromCreateSession"
}
  1. Click Send request ().
  2. 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.

 
 
 

Configure an MQTT topic to implement a transform

  1. Select MQ API from the Select API dropdown menu.
  2. Select configureTopic from the JSON Actions dropdown menu.
  3. Replace the JSON in the API Request editor with the following JSON:
{
 "api": "mq",
 "action": "configureTopic",
   "params": {
     "topic": "MyTopic",
     "databaseName": "faircom",
     "tableName": "MyTopicTable",
     "transformName": "MyTopicTransform"
    },
 "authToken": "replaceWithAuthTokenFromCreateSession"
}
  1. Click Send request ().
  2. 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.

 
 
 

Publish JSON data to an MQTT topic to be transformed

  1. Select the Publish tab ().
  2. Enter MyTopic in the Topic textbox.
  3. In the Message textbox enter:
{
 "Property1": " More stuff here",
 "Property2": 39.39
}
  1. Click +Publish ().
 
 

View transformed data

  1. Open the Data Explorer.
  2. Select the ctreeSQL database in the Server navigation window.
  3. Select Connect () from the JSON Actions dropdown menu to connect to the ctreeSQL server.
  4. Navigate to and select mytopictransformed in the Server navigation window through ctreeSQL>admin>Tables.
  5. Click the Table Records tab ().
  6. Observe a new row that contains the published values of the second message in the property_1 and property_2 fields.
 
 

Query captured MQTT data using SQL

  1. Click the SQL Queries tab ().
  2. Enter the following SQL query in the textbox:JSON APIs
    select * from mytopictransformed where property_2 > 30;
  1. Click Send request ().
  2. Observe data in the Results window.
 
 

Query captured MQTT data using the JSON DB API

  1. Click the Api Explorer tab ().
  2. Select DB API from the Select API dropdown menu.
  3. Select getRecordsByIndex from the JSON Actions dropdown menu.
  4. Click the Property Display icon ().
  5. Click the Insert icon () next to the databaseName property to insert databaseName into the API Request.
  6. Update the API Request:
    1. Make the tableName "tableName":"mytopictransformed".
    2. Make the indexName "indexName":"admin_mytopictransformed_ts_index".
    3. Make the databaseName "databaseName:"ctreeSQL".
  7. Click Send request ().
  8. 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.

 
 
 

 

json api database tutorial

Was this article helpful?

Give feedback about this article

Related Articles