Use the JSON DB API to query MQTT data captured from IoT devices tutorials

Start the FairCom Browser-Based tools.

Select Data Explorer.

Connect to the server as user Admin.

In the Server navigation window, right-click faircom to select Connect () from the dropdown menu.

Click the API Explorer tab ().

Select MQ API from the Select API dropdown menu ().

Select "configureTopic" from the JSON Actions dropdown menu.

Replace the JSON in the API Request editor with the following JSON:

{
    "authToken": "ReplaceWithValidToken",
    "api": "mq",
    "action": "configureTopic",
    "params": {
        "topic": "MyTopic",
        "databaseName": "faircom",
        "tableName": "MyTopicTable"
    }
}

Click Apply defaults to JSON request () to replace the "authToken" value with the valid one from your session.

Click Send request () to issue the JSON-based request.

Observe the response and ensure the action completed successfully.

Start the FairCom Browser-Based tools.

Select MQ Explorer.

Select MQTT Management from the Control menu ().

Click the Publish tab.

Enter MyTopic in the Topic text box.

In the Message text box enter:

{
  "Property1": "Stuff goes here",
  "Property2": 39
}

Click +Publish ().

Access the FairCom database in the FairCom Data Explorer.

Navigate to and select mytopictable in the Server navigation window through faircom>admin>Tables.

Click the Table Records tab ().

Click the source_payload field for one of the listed records.

Observe the pop-up window with the contents of the selected record.

Click Cancel () to close the window.

Access the API Explorer.

Select the Hub API from the Select API dropdown menu ().

Select createTransform from the JSON Actions dropdown menu.

Replace the JSON in the API Request editor with the following JSON:

{
    "authToken": "ReplaceWithValidToken",
    "api": "hub",
    "action": "createTransform",
    "params": {
        "transformName": "MyTopicTransform",
        "transformActions": [
            {
                "inputFields": [
                    "source_payload"
                ],
                "transformActionName": "jsonToDifferentTableFields",
                "transformParams": {
                    "targetDatabaseName": "ctreeSQL",
                    "targetTableName": "MyTopicTransformed",
                    "mapOfPropertiesToFields": [
                        {
                            "propertyPath": "Property1",
                            "name": "property_1",
                            "type": "VARCHAR",
                            "length": 300
                        },
                        {
                            "propertyPath": "Property2",
                            "name": "property_2",
                            "type": "DOUBLE"
                        }
                    ]
                }
            }
        ]
    }
}

Click Send request ().

Observe the response and ensure the action completed successfully.

Select MQ API from the Select API dropdown menu (see ???).

Select configureTopic from the JSON Actions dropdown menu.

Replace the JSON in the API Request editor with the following JSON:

{
    "authToken": "ReplaceWithValidToken",
    "api": "mq",
    "action": "configureTopic",
    "params": {
        "topic": "MyTopic",
        "databaseName": "faircom",
        "tableName": "MyTopicTable",
        "transformName": "MyTopicTransform"
    }
}

Click Send request ().

Observe the response and ensure the action completed successfully.

Select the Publish tab ().

Enter MyTopic in the Topic textbox.

In the Message textbox enter:

{
  "Property1": " More stuff here",
  "Property2": 39.39
}

Click +Publish ().

Open the Data Explorer.

Select the ctreeSQL database in the Server navigation window.

Select Connect () from the JSON Actions dropdown menu to connect to the ctreeSQL server.

Navigate to and select mytopictransformed in the Server navigation window through ctreeSQL>admin>Tables.

Click the Table Records tab ().

Observe a new row that contains the published values of the second message in the property_1 and property_2 fields.

Click the SQL Queries tab ().

Enter the following SQL query in the textbox:JSON APIs

select * from mytopictransformed where property_2 > 30;

Click Send request ().

Observe data in the Results window.

Click the Api Explorer tab ().

Select DB API from the Select API dropdown menu.

Select getRecordsByIndex from the JSON Actions dropdown menu.

Click the Property Display icon ().

Click the Insert icon () next to the databaseName property to insert databaseName into the API Request.

Update the API Request:

Click Send request ().

Observe the response and ensure the action completed successfully.