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.
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.
There is one Child Table: ex1_mynestedtable
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.