MQTT V3 Plug-in Reference

MQTT Persistence API Can Map Nested JSON Structures to Multiple Tables

Support has been implemented for persisting a JSON homogeneous array of simple values into multiple records in children tables.

The "mapOfPropertiesToFields" array supports the following list of new properties:

• childTableName - Similar to "fieldName", but the property will be persisted in a child table.
• childFieldType, childFieldWidth, childFieldScale - Similar to "fieldType", "fieldWidth" and "fieldScale" but it will define the type of the "value" field in the child table. For example:

{

"operation": "CreatePersistenceTopic",

""persistenceTopic": "myTopic",

"tableName": "ex1",

"mapOfPropertiesToFields":

[

{

"propertyPath": "myProp1",

"fieldName": "myProp1",

"fieldType": "VARCHAR"

},

{

"propertyPath": "myProp2",

"fieldName": "myProp2",

"fieldType": "DOUBLE"

},

{

"propertyPath": "myStringArray",

"childTableName": "ex1_mystringarray",

"childFieldType": "VARCHAR",

"childFieldWidth":64

},

{

"propertyPath": "myIntegerArray",

"childTableName": "ex1_myintegerarray",

"childFieldType": "INTEGER"

}

]

}

This can be used for persisting the following payload example:

{

"myProp1": "test1",

"myProp2": 13.2,

"myStringArray":

[

"string0",

"string1",

"string2"

],

"myIntegerArray": [10, 11, 12]

}

If a persistence table has children tables, a "primarykey" field of type BigInt is automatically added to the main persistence table. It will be linked to the the "foreignkey" field in the children tables. Apart from that, the children table will have only one field, named "value", containing the array's element information and a field named "position" with its position in the array.

For persistence tables with auto-purge, the children tables also have the "ts" field and they are also partitioned based on that field.

Multi-Column "fieldCount"

The elements of the "mapOfPropertiesToFields" array support a property named "fieldCount" (Default is 1). If this value is greater than 1, it will create multiple fields of the same data type with the following name: <fieldName>N (where N is 0, 1, ...). If the JSON property being persisted is an array, it will try to extract all the elements from the array and populate these fields. If the array has more elements than fields, the remaining fields are ignored. If the array has fewer elements than fields, the remaining fields are set to NULL.

Example of usage:

{

"operation": "CreatePersistenceTopic",

"persistenceTopic": "ford6",

"tableName": "ford6",

"mapOfPropertiesToFields":

[

{

"propertyPath": "myProp1",

"fieldName": "myProp1",

"fieldType": "VARCHAR",

"fieldWidth":64

},

{

"propertyPath": "myProp2",

"fieldName": "myProp2",

"fieldType": "DOUBLE"

},

{

"propertyPath": "myStringArray",

"fieldName": "c",

"fieldType": "VARCHAR",

"fieldWidth":64,

"fieldCount": 3

},

{

"propertyPath": "myIntegerArray",

"fieldName": "i",

"fieldType": "INTEGER",

"fieldCount": 3

}

]

}

Can be used for persisting the following payload example:

{

"myProp1": "test1",

"myProp2": 13.2,

"myStringArray":

[

"item0",

"item1",

"item2"

],

"myIntegerArray": [10, 11, 12]

}