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:
There are two Child Tables: ex1_mystringarray and ex1_myintegerarray
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.