MQTT V3 Plug-in Reference

Mapping JSON Array of Homogeneous Simple Values to a Separate 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.

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 only for homogeneous arrays of simple values.

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

This solution stores each JSON array in a separate 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",

"childTableName": "ex1_mystringarray",

"childFieldType": "VARCHAR"

},

{

"propertyPath": "myIntegerArray",

"childTableName": "ex1_myintegerarray",

"childFieldType": "INTEGER"

}

]

}

Data Message:

{

"myProp1": "test1",

"myProp2": 13.2,

"myStringArray":

[

"string0",

"string1",

"string2"

],

"myIntegerArray": [10, 11, 12]

}

Resulting 3 Tables and Records:

ex1

primarykey	ts	myProp1	myProp2
123	2019-12-17T10:58:13Z	"test1"	13.2

ex1_mystringarray

foreignkey	position	value
123	0	"string0"
123	1	"string1"
123	2	"string2"

ex1_myintegerarray

foreignkey	position	value
123	0	10
123	1	11
123	2	12

Example Notes

ex1 table is the Primary Table for the JSON document:

• It contains the non-array properties from the JSON document, which are myProp1 and myProp2.
• It also has automatically generated primarykey and ts fields. The ts field contains the timestamp.

There are two Child Tables: ex1_mystringarray and ex1_myintegerarray

• There is one Child Table for each homogeneous array.
• When mapping JSON properties to table fields in the persistence topic configuration document, the presence of the childTableName or the childFieldType property signals the creation of a child table.
• The childTableName property specifies the name of the child table.
• Each record in each child table has the following three fields:

  foreignkey - Its value is the primarykey of the associated record in the primary table.

  position - Contains the zero-based array item position.

  value - Contains the value at the specified position in the JSON array.

The type of column matches the homogeneous type in the JSON array. It is specified by the fieldType property in the CreatePersistenceTopic message.

The primary key of each child table is the combination of foreignkey and position fields.