MQTT V3 Plug-in Reference

Mapping JSON Array of Homogeneous Simple Values to Separate Columns in the Same Table

Problem

It is common for JSON documents to contain properties that are arrays.

• Each item in a JSON array may be any type of JSON value. This is called a heterogeneous array.
• Typically an array contains only one type of value, such as an array of strings. This is called a homogeneous array.

It is common for a property in a JSON document to be mapped to one field in a relational table

• An array typically contains more than one value.
• A field in a relational table may contain only one value
• Thus, a JSON array cannot be stored in a single field.

Solution

This solution is designed for homogeneous arrays of simple values.

• This solution does not support “arrays of arrays” and “arrays of objects”.

This solution stores each item in a JSON array in a separate column in the same table.

A JSON document may contain multiple properties that are arrays of homogeneous values.

Example 1:

Configuration Message

{

"operation": "CreatePersistenceTopic",

"persistenceTopic": "myTopic",

"tableName": "ex1",

"mapOfPropertiesToFields":

[

{

"propertyPath": "myProp1",

"fieldName": "myProp1",

"fieldType": "VARCHAR"

},

{

"propertyPath": "myProp2",

"fieldName": "myProp2",

"fieldType": "DOUBLE"

},

{

"propertyPath": "myStringArray",

"fieldName": "c",

"fieldType": "VARCHAR",

"fieldCount": 3

},

{

"propertyPath": "myIntegerArray",

"fieldName": "i",

"fieldType": "INTEGER",

"fieldCount": 3

}

]

}

Data Message:

{

"myProp1": "test1",

"myProp2": 13.2,

"myStringArray":

[

"item0",

"item1",

"item2"

],

"myIntegerArray": [10, 11, 12]

}

Resulting tables and records

ex1

ts	myprop1	myprop2	c0	c1	c2	i0	i1	i2
2019-12-17T10:58:13Z	"test1"	13.2	"item0"	"item1"	"item2"	10	11	12

Notes

Columns “c0”, “c1”, and “c2” come from myStringArray[] and columns “i0”, “i1”, and “i2” come from myIntegerArray[].

FairCom Edge automatically creates these columns based on the configuration message.

• When a fieldCount property exists and is set to a value greater than 1, FairCom Edge automatically generates multiple fields in the persisted table.
• It generates fields up to the number of fields in fieldCount.

  In the example, fieldCount is 3 and FairCom Edge generates three fields.

• FairCom Edge generates the name of each field by concatenating the fieldName with an array position number.

  The first item position in an array is 0 (zero not the letter “O”).

  The last item position in an array is the fieldCount minus 1.

  Thus, in the example, the fieldName “c” is concatenated with 0, 1, and 2 to create the field names “c0”, “c1”, and “c2”.

• FairCom Edge maps items in an array into fields that match the specified name and position.

  In the example, FairCom Edge places 10 in field “i0”, 11 in “i1”, and 12 in “i2”.