Problem
It is common for JSON documents to contain properties that are arrays.
It is common for a property in a JSON document to be mapped to one field in a relational table
Solution
This solution is designed for homogeneous arrays of simple values.
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.
In the example, fieldCount is 3 and FairCom Edge generates three fields.
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”.
In the example, FairCom Edge places 10 in field “i0”, 11 in “i1”, and 12 in “i2”.