MQTT V3 Plug-in Reference

Mapping JSON Array of Homogeneous Objects to a Separate Table

Problem

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

Each object in a JSON array may contain any property of any type.

Typically each object in the array has an identical set of properties and types. This is called a homogeneous array.

An array of objects is the relational equivalent of a table nested inside a field. This is not supported in the relational model.

Solution

This solution is designed only for homogeneous arrays.

This solution stores each JSON array of objects in a separate table.

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

This solution handles deeply nested arrays.

• A property in an object may be an array of values.
• Thus, it is possible for child tables to have child tables. See Example 2 below.

Example 1: One Nested Table

Configuration Message:

{

"operation": "CreatePersistenceTopic",

"persistenceTopic": "myTopic",

"tableName": "ex1",

"mapOfPropertiesToFields":

[

{

"propertyPath": "prop1",

"fieldName": "prop1",

"fieldType": "VARCHAR"

},

{

"propertyPath": "nestedTable",

"childTableName": "ex1_nestedtable",

"mapOfPropertiesToFields":

[

{

"propertyPath": "childProp1",

"fieldName": "childprop1",

"fieldType": "VARCHAR"

},

{

"propertyPath": "childProp2",

"fieldName": "childprop2",

"fieldType": "INTEGER"

}

]

}

]

}

Data Message:

{

"prop1": "test1",

"nestedTable":

[

{"childProp1": "item0", "childProp2": 10},

{"childProp1": "item1", "childProp2": 11},

{"childProp1": "item2", "childProp2": 12}

]

}

Resulting tables and records

ex1

primarykey	ts	prop1
123	2019-12-17T10:58:13Z	"test1"

ex1_nestedtable

foreignkey	position	childprop1	childprop2
123	0	"item0"	10
123	1	"item1"	11
123	2	"item2"	12

Notes

ex1 table is the Primary Table for the JSON document.

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

There is one Child Table: ex1_mynestedtable

• There is one Child Table for each homogeneous array of objects.
• The childTableName property specifies the name of the child table.
• The presence of the mapOfPropertiesToFields property signals the creation of a child table to hold the data for the array of objects.
• The child table has these predefined fields:

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

  position - Contains the zero-based array item position

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

Each record in each child table has one field for each property mapped in mapOfPropertiesToFields.

In the example, the nested table has two fields: childprop1 and childprop2.

Example 2: Deeply Nested Tables

Configuration Message:

{

"operation": "CreatePersistenceTopic",

"persistenceTopic": "myTopic",

"tableName": "ex2",

"mapOfPropertiesToFields":

[

{

"propertyPath": "prop1",

"fieldName": "prop1",

"fieldType": "VARCHAR"

},

{

"propertyPath": "nestedTable",

"childTableName": "ex1_nestedtable",

"mapOfPropertiesToFields":

[

{

"propertyPath": "childProp1",

"fieldName": "childProp1",

"fieldType": "VARCHAR"

},

{

"propertyPath": "childProp2",

"fieldName": "childProp2",

"fieldType": "INTEGER"

},

{

"propertyPath": "deeplyNestedTable",

"childTableName": "ex1_deeplyNestedtable",

"mapOfPropertiesToFields":

[

{

"propertyPath": "grandchildProp1",

"fieldName": "grandchildprop1",

"fieldType": "DOUBLE"

},

{

"propertyPath": "isNested",

"fieldName": "isnested",

"fieldType": "BIT"

}

]

}

]

}

]

}

Data Message:

{

"prop1": "test1",

"nestedTable":

[

{

"childProp1": "item0",

"childProp2": 10,

"deeplyNestedTable":

[

{ "grandchildProp1": 0.1, "isNested": true },

{ "grandchildProp1": 0.2, "isNested": true },

]

},

{

"childProp1": "item1",

"childProp2": 11,

"deeplyNestedTable":

[

{ "grandchildProp1": 1.1, "isNested": true },

{ "grandchildProp1": 1.2, "isNested": true },

]

}

]

}

Resulting tables and records

ex2

primarykey	ts	prop1
123	2019-12-17T10:58:13Z	"test1"

ex2_nestedtable

foreignkey	position	childprop1	childprop2
123	0	"item0"	10
123	1	"item1"	11

ex2_deeplyNestedtable

foreignkey	position	grandchildprop1	isnested
1259	0	0.1	1
1259	1	0.2	1
1260	0	1.1	1
1260	1	1.2	1

Notes

The mapOfPropertiesToFields property can be nested recursively.

This creates a chain of child tables that have foreign keys referencing other child tables until the topmost child table references the parent table.

Each record in ex2_deeplyNestedtable has a foreignkey to a record in ex2_nestedtable.

Each record in ex2_nestedtable has a foreignkey to a record in ex2.