Supported JSON Structures
Panopticon supports JSON structures that represent one or several records where each record will become a row in a data table. It can be an array of JSON strings, or a set of named JSON string elements. Each record must be on the same record path, e.g., mydata.people. Each name in each record must follow hierarchically directly after the record path, e.g., mydata.people.name. Each record must be a JSON string of name-value pairs. Each name can have the following as value:
- A single value
- A JSON string of other name-value pairs
- A JSON array of values
Each name that has a single value will result in one column in the data table (Example 1). Each name that has several name-value pairs as value will result in several columns in the data table thus, one for each name at the lowest level (Examples 2 and 3). In case a name’s value has an array of multiple values (Example 4), then the Array Handling setting in the connector will control how the data table will be created, to accommodate the array of values:
- Add Rows (default) - one row will be created for each value in the array
- Add Columns - one column will be created for each value in the array
- Concatenate - one column will be created for all values in the array, concatenated with a delimiter character
In most cases, the Add Rows array handling is preferred. One scenario where Add Columns could be more useful is when each item in the data set has the same number of values in their arrays, representing a set of properties that all items have. One scenario where Concatenate could be more useful is when there are mixed data types in the values of the arrays, and perhaps also different value counts in the arrays.
Here are examples of supported JSON structures:
Example 1: An array of JSON strings with name-value pairs, each name with a single value:
{
"mydata": {
"people": [{
"Name": "Peter",
"Age": "20"
},
{
"Name": "Paul",
"Age": "21"
},
{
"Name": "Mary",
"Age": "22"
}
]
}
}
Example 2: An array of JSON strings with name-value pairs, some names having a single value and some names having a nested JSON string as value:
{
"mydata": {
"people": [{
"Name": "Peter",
"Age": "20",
"Address": {
"Street": "Park Avenue",
"Number": "1"
}
},
{
"Name": "Paul",
"Age": "21",
"Address": {
"Street": "Mainstreet",
"Number": "17"
}
},
{
"Name": "Mary",
"Age": "22",
"Address": {
"Street": "Cedar Road",
"Number": "5"
}
}
]
}
}
Example 3: Named elements instead of an array, where the element name will be parsed into a column called KeyColumn in the data table:
{
"Peter": {
"Age": "20",
"Address": {
"Street": "Park Avenue",
"Number": "1"
}
},
"Paul": {
"Age": "21",
"Address": {
"Street": "Mainstreet",
"Number": "17"
}
},
"Mary": {
"Age": "22",
"Address": {
"Street": "Cedar Road",
"Number": "5"
}
}
}
Example 4: An array of JSON strings with name-value pairs, where some names have as value an array of multiple values. A column will be created in the data table for each unique value in the arrays:
{
"mydata": {
"people": [{
"Name": "Peter",
"Age": "20",
"Address": ["Mainstreet", "Whoville"]
},
{
"Name": "Paul",
"Age": "21",
"Address": ["Backstreet", "Barnburg"]
},
{
"Name": "Mary",
"Age": "22",
"Address": ["Runroad", "Suburbia"]
}
]
}
}
(c) 2013-2024 Altair Engineering Inc. All Rights Reserved.