Skip to main content

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

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:

Complete the tutorial requirements before this procedure.

  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 (Connect) from the dropdown menu.

  5. Click the API Explorer tab (API Explorer Tab).

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.

Prerequisites:
  1. Complete the tutorial requirements.

  2. Create a session.

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

  2. Select "configureTopic" from the JSON Actions dropdown menu.

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

    Note

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

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

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

  6. 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 with any questions.

Complete the tutorial requirements before this procedure.

  1. Start the FairCom Browser-Based tools.

  2. Select MQ Explorer.

  3. Select MQTT Management from the Control menu (Control menu).

  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
    }
  4. Click +Publish (+Publish).

  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 (Table Records Tab).

    Note

    Table records are displayed one record per row.

  4. Click the source_payload field for one of the listed records.

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

  6. Click Cancel (Cancel) to close the window.

  1. Access the API Explorer.

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

  3. Select createTransform from the JSON Actions dropdown menu.

  4. 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"
                            }
                        ]
                    }
                }
            ]
        }
    }
    
  5. Click Send request (Runbutton.png).

  6. 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 with any questions.

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

  2. Select configureTopic from the JSON Actions dropdown menu.

  3. 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"
        }
    }
    
  4. Click Send request (Runbutton.png).

  5. 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 with any questions.

  1. Select the Publish tab (Publish Tab).

  2. Enter MyTopic in the Topic textbox.

  3. In the Message textbox enter:

    {
      "Property1": " More stuff here",
      "Property2": 39.39
    }
  4. Click +Publish (+Publish).

  1. Open the Data Explorer.

  2. Select the ctreeSQL database in the Server navigation window.

  3. Select Connect (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 (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.

  1. Click the SQL Queries tab (SQL Queries Tab).

  2. Enter the following SQL query in the textbox:JSON APIs

    select * from mytopictransformed where property_2 > 30;
  3. Click Send request (Runbutton.png).

  4. Observe data in the Results window.

  1. Click the Api Explorer tab (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 (Property Display Icon).

  5. Click the Insert icon (Insert) 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 (Runbutton.png).

  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 with any questions.