• 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. Navigate to the Code section from the sidebar menu.

Create the "MyTopicTable" integration table adding a transform to extract captured MQTT JSON data into table fields

This procedure creates a transform that will convert the JSON-based data from the "MyTopicTable" Integration Table into fields in the same table.

The Integration Table automatically creates the source_payload, ts_index and error fields. The jsonToTableFields transform extracts values from source_payload (which stores the MQTT JSON data) and stores them in the property_1 and property_2 fields defined in the request.

1. Start the FairCom browser-based tools.
2. Navigate to the Code section from the sidebar menu.
3. Select the Hub API from the Select API dropdown menu.
4. Select createIntegrationTable from the JSON Actions dropdown menu.
5. Replace the JSON in the API Request editor with the following JSON:

   {
     "api": "hub",
     "action": "createIntegrationTable",
     "params": {
       "tableName": "MyTopicTable",
       "fields": [
         {
           "propertyPath": "property_1",
           "name": "property_1",
           "type": "varchar",
           "length": 300
         },
         {
           "propertyPath": "property_2",
           "name": "property_2",
           "type": "float"
         }
       ],
       "transformSteps": [
         {
           "transformStepMethod": "jsonToTableFields",
           "mapOfPropertiesToFields": [
             {
               "fieldName": "property_1",
               "recordPath": "source_payload.property_1"
             },
             {
               "fieldName": "property_2",
               "recordPath": "source_payload.property_2"
             }
           ]
         }
       ]
     },
     "authToken": "replaceWithAuthTokenFromCreateSession"
   }

   6. Click Execute () to issue the JSON-based command.

         7. Observe the response and ensure the action is completed successfully.

"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 the 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"
       },
    "authToken": "replaceWithAuthTokenFromCreateSession"
   }

4.  Click Execute () to issue the JSON-based command.

     5. Observe the response and ensure the action is completed successfully.

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

Publish JSON data to an MQTT topic

Now that the OPC UA data is flowing into a table as JSON and being transformed into discrete fields in the same table, the data can be forwarded out over MQTT.

      4. Navigate to Publish:

      5. Enter "MyTopic" in the Topic text box.

  6. In the Message text box enter:

{"property_1":"Stuff goes here","property_2":39}

 7. Click Publish Message ().

     8. Go back to “Messages” and observe that your message was published successfully.

View transformed data

1. In the FairCom Edge, navigate to IoT > Tables. Select "MyTopicTable" from the Table Name section;
2. Click the Records tab.
3. Observe a new row that contains the published values of the second message in the Property1 and Property2 fields.

Query captured MQTT data using SQL 

1. In FairCom Edge, navigate to Data > SQL > Queries;
2. In the Enter SELECT query code box, execute the following SQL query against the data:

   select * from mytopictable where property_2 > 30;

3. Observe data in the Results window.

Query captured MQTT data using the JSON DB API 

1. Select DB API from the JSON API dropdown menu.
2. Select "getRecordsByIndex" from the JSON Actions dropdown menu.
3. Change the "tableName" property value from "athlete" to "MyTopicTable".
4. Change the "indexName" property value from "id_pk" to "ts_index".
5. Click Execute ().
6. Observe the records from the table returning as JSON in the API Response.

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