Skip to content

TSTool / Command / ReadTableFromJSON

Overview

The ReadTableFromJSON command reads a table from a JavaScript Object Notation (JSON) file.

JSON is a hierarchical data representation that can have multiple nesting levels. Arrays are indicated by brackets [ ], objects are indicated by curly braces { } and "name": value pairs define data for an object. Objects are generically represented in software as a "map" or "dictionary". A simple example is as follows, where the outer braces enclose the entire JSON object and someArray is the data array of interest:

{
  "someArray": [
     {
       "stringProp": "a string",
       "numberProp": 1.0,
       "number2Prop": 1,
       "dateTimeProp": "2021-01-02T00:01:03-06:00"
     },
     {
       "stringProp": "another string",
       "numberProp": 2.0,
       "number2Prop": 2,
       "dateTimeProp": "2021-01-03T00:01:03-06:00"
     },
     ... etc. ...
  ]
}

This command has the following functionality:

  • An array name must be specified to indicate the level within the JSON to read:
    • If not specified, the topmost array is used.
    • All simple data objects in the array are read unless filtered.
    • Additional arrays and maps (dictionaries) are ignored.
  • Table column names match the object data names:
    • Objects do not need to have consistently-named properties, although this is typical.
    • The names within each object correspond to table column names in output.
    • Processing of names is case dependent and redundant names will overwrite.
  • Types for table columns are determined by examining the JSON structure:
    • Quoted values are interpreted to be strings.
    • Booleans are handled directly.
    • Floating point numbers with decimal default to double precision output.
    • Command parameters ArrayColumns, BooleanColumns, DateTimeColumns, DoubleColumns, IntegerColumns, and TextColumns can be used to override the default data type mapping.
    • Arrays containing primitive values (such as all strings or all numbers), can be read into the table as an array column.
    • The ExcludeNames parameter is provided to exclude JSON names within the data objects.

Command Editor

The following dialog is used to edit the command and illustrates the command syntax.

ReadTableFromJSON

ReadTableFromJSON Command Editor (see also the full-size image)

Command Syntax

The command syntax is as follows:

ReadTableFromJSON(Parameter="Value",...)

Command Parameters

Parameter                 Description Default                  
TableID
required		 Identifier to assign to the table that is read, which allows the table data to be used with other commands. Can be specified using processor ${Property}. None – must be specified.
InputFile
required		 The name of the file to read, as an absolute path or relative to the command file location. Can be specified using processor ${Property}. None – must be specified.
ArrayName JSON array name containing data objects to read. Can be specified using processor ${Property}. First array found will be read.
AppendArrays If False, the first matching array is processed. If True, all matching arrays are processed and appended. False
IncludeParents Planned feature. It may be necessary to include parent element values to ensure uniqueness. Parent data will be duplicated in child array table rows.
ExcludeNames List of JSON names in data objects to exclude from table, separated by commas. Include all names. Can be specified using processor ${Property}.
ArrayColumns List of comma-separated column names for columns that should be treated as containing primitive array data such as array of strings or numbers. Can be specified using processor ${Property}. Ignore array types.
DateTimeColumns List of comma-separated column names for columns that should be treated as containing date/time values. These are JSON strings that contain text that can be converted to date/time objects. Can be specified using processor ${Property}. Date/time columns are not automatically determined and must be indicated.
BooleanColumns List of comma-separated column names for columns that should be treated as containing boolean values. Can be specified using processor ${Property}. Automatically determine column type based on true and false values.
DoubleColumns List of comma-separated column names for columns that should be treated as containing double-precision (floating point number) values. Can be specified using processor ${Property}. Automatically determine columns type based on numbers with decimal point.
IntegerColumns List of comma-separated column names for columns that should be treated as containing integer values. Can be specified using processor ${Property}. Automatically determine column type based on numbers without decimal point.
TextColumns List of comma-separated column names for columns that should be treated as containing text values. Can be specified using processor ${Property}. Default column type if other type is not determined.
Top Specify the number of data rows to read, useful when prototyping an analysis process. Process all rows. Can be specified using processor ${Property}.

Examples

See the automated tests.

Top Level Array (No Array Name)

The following JSON uses a top-level array of objects. In this case the command wraps the data with { "toparray": } so that a data map can be generated, and the array is read because it is the first array that is found.

ReadTableFromJSON(TableID="Table1",InputFile="Data/toplevel-array.json",BooleanColumns="string_bool",DateTimeColumns="string_datetime",DoubleColumns="string_double",IntegerColumns="string_int")

[
  {
    "double": 1.0,
    "int": 1,
    "string": "s1",
    "bool": true,
    "datetime":  "2021-01-01T01:01:01:01",
    "string_double": "1.1",
    "string_int": "11",
    "string_bool": "true",
    "string_datetime":  "2021-11-11T01:11:11:11"
  },
  {
    "double": 2.0,
    "int": 2,
    "string": "s2",
    "bool": false,
    "datetime":  "2021-02-02T01:02:02:02",
    "string_double": "2.2",
    "string_int": "22",
    "string_bool": "false",
    "string_datetime":  "2021-02-22T01:22:22:22"
  },
  {
    "double": 3.0,
    "int": 3,
    "string": "s3",
    "bool": true,
    "datetime":  "2021-03-03T01:03:03:03",
    "string_double": "3.3",
    "string_int": "33",
    "string_bool": "true",
    "string_datetime":  "2021-03-30T01:33:33:33"
  }
]

Named Array

The following JSON uses a named array of objects. Because there is only one array, the array name can be specified, or can be omitted.

ReadTableFromJSON(TableID="Table1",InputFile="Data/named-array.json",BooleanColumns="string_bool",DateTimeColumns="string_datetime",DoubleColumns="string_double",IntegerColumns="string_int")

or

ReadTableFromJSON(TableID="Table1",InputFile="Data/named-array.json",ArrayName="data",BooleanColumns="string_bool",DateTimeColumns="string_datetime",DoubleColumns="string_double",IntegerColumns="string_int")

{
  "data" : [
    {
      "double": 1.0,
      "int": 1,
      "string": "s1",
      "bool": true,
      "datetime":  "2021-01-01T01:01:01:01",
      "string_double": "1.1",
      "string_int": "11",
      "string_bool": "true",
      "string_datetime":  "2021-11-11T01:11:11:11"
    },
    {
      "double": 2.0,
      "int": 2,
      "string": "s2",
      "bool": false,
      "datetime":  "2021-02-02T01:02:02:02",
      "string_double": "2.2",
      "string_int": "22",
      "string_bool": "false",
      "string_datetime":  "2021-02-22T01:22:22:22"
    },
    {
      "double": 3.0,
      "int": 3,
      "string": "s3",
      "bool": true,
      "datetime":  "2021-03-03T01:03:03:03",
      "string_double": "3.3",
      "string_int": "33",
      "string_bool": "true",
      "string_datetime":  "2021-03-30T01:33:33:33"
    }
  ]
}

Nested Arrays (AppendArrays=True)

The following JSON uses an outer array of objects that each contain a data array. The array name is specified and AppendArrays=True is specified to append the inner arrays. Currently the properties of parent objects are not included but this feature is planned for the future.

ReadTableFromJSON(TableID="Table1",InputFile="Data/named-array-nested.json",ArrayName="data",AppendArrays=True,BooleanColumns="string_bool",DateTimeColumns="string_datetime",DoubleColumns="string_double",IntegerColumns="string_int")

{
  "stations" : [
    {
      "name": "station1",
      "data" : [
        {
          "double": 1.0,
          "int": 1,
          "string": "s1",
          "bool": true,
          "datetime":  "2020-01-01T01:01:01:01",
          "string_double": "1.1",
          "string_int": "11",
          "string_bool": "true",
          "string_datetime":  "2020-11-11T01:11:11:11"
        },
        {
          "double": 2.0,
          "int": 2,
          "string": "s2",
          "bool": false,
          "datetime":  "2020-02-02T01:02:02:02",
          "string_double": "2.2",
          "string_int": "22",
          "string_bool": "false",
          "string_datetime":  "2020-02-22T01:22:22:22"
        },
        {
          "double": 3.0,
          "int": 3,
          "string": "s3",
          "bool": true,
          "datetime":  "2020-03-03T01:03:03:03",
          "string_double": "3.3",
          "string_int": "33",
          "string_bool": "true",
          "string_datetime":  "2020-03-30T01:33:33:33"
        }
      ]
    },
    {
      "name": "station1",
      "data" : [
        {
          "double": 100.0,
          "int": 100,
          "string": "s100",
          "bool": true,
          "datetime":  "2021-01-01T01:01:01:01",
          "string_double": "100.1",
          "string_int": "110",
          "string_bool": "true",
          "string_datetime":  "2021-11-11T01:11:11:11"
        },
        {
          "double": 200.0,
          "int": 200,
          "string": "s200",
          "bool": false,
          "datetime":  "2021-02-02T01:02:02:02",
          "string_double": "200.2",
          "string_int": "220",
          "string_bool": "false",
          "string_datetime":  "2021-02-22T01:22:22:22"
        },
        {
          "double": 300.0,
          "int": 300,
          "string": "s300",
          "bool": true,
          "datetime":  "2021-03-03T01:03:03:03",
          "string_double": "300.3",
          "string_int": "330",
          "string_bool": "true",
          "string_datetime":  "2021-03-30T01:33:33:33"
        }
      ]
    }
  ]
}

Troubleshooting

See Also