TSTool / Command / ReadTableFromJSON
Overview
The ReadTableFromJSON
command reads a table from a JavaScript Object Notation (JSON) file.
An example of JSON returned from a web service is as follows.
This example illustrates water quality monitoring locations from Colorado’s Data Sharing Network
web services retrieved with the WebGet
command and URI:
http://awqmsws.goldsystems.com/api/MonitoringLocationsVer1?StateCode=CO.
[
{ "CountyName":"Adams",
"Huc8":"","Huc12":"",
"MonitoringLocationIdentifier":"NFE",
"Latitude":"39.812806",
"Longitude":"-104.954333",
"MonitoringLocationName":"Metro Wastewater Reclamation District North Final Effluent - CO-0026638",
"OrganizationIdentifier":"MWRD_WQX",
"ResultSummaries":[],
"StateCode":"CO",
"MonitoringLocationType":"Facility Municipal Sewage (POTW)",
"WaterbodyName":"","WatershedManagementUnit":""
},
{ "CountyName":"Adams",
"Huc8":"","Huc12":"",
"MonitoringLocationIdentifier":"SFE",
"Latitude":"39.812772",
"Longitude":"-104.95444",
"MonitoringLocationName":"Metro Wastewater Reclamation District South Final Effluent - CO-0026638",
"OrganizationIdentifier":"MWRD_WQX",
"ResultSummaries":[],
"StateCode":"CO",
"MonitoringLocationType":
"Facility Municipal Sewage (POTW)",
"WaterbodyName":"","WatershedManagementUnit":""
}
]
The following example shows water quality data retrieve using the following URI: http://awqmsws.goldsystems.com/api/ResultsVer1?MonitoringLocationIdentifiersCsv=UPSTREAM&OrganizationIdentifiersCsv=AURORA_WQX
[
{ "ActivityIdentifier":"UPSTREAM10/7/199817-OCT-980",
"ActivityType":"Sample-Routine",
"AssemblageSampled":"",
"BottomDepthHeight":"",
"BottomDepthHeightUnit":"",
"CountyName":"Adams",
"DepthAltitudeReferencePoint":"",
"DepthHeight":"",
"DepthHeightUnit":"",
"MediaName":"Water",
"MediaSubdivisionName":"",
"MonitoringLocationIdentifier":"UPSTREAM",
"OrganizationIdentifier":"AURORA_WQX",
"Projects":
[
{ "ProjectIdentifier":"SP CURE"}
],
"Results":
[
{ "AnalyticalMethodContext":"APHA",
"AnalyticalMethodIdentifier":"4500-NH3(D)",
"BiologicalIntent":"",
"CharacteristicName":"Ammonia",
"Comment":"",
"DataLoggerLine":"",
"DepthHeight":"",
"DepthHeightUnit":"",
"DetectionCondition":"Not Detected",
"MethodSpeciation":"as NH3",
"ParticleSizeBasis":"",
"QualifierCode":"",
"ResultUnit":"mg/l",
"ResultValue":"<0.05",
"SampleFraction":"Total",
"StatisticalBaseCode":"",
"Status":"Final",
"TaxonomicName":"",
"TemperatureBasis":"",
"TimeBasis":"",
"ValueType":"Actual",
"WeightBasis":""
},
{
"AnalyticalMethodContext":"APHA",
"AnalyticalMethodIdentifier":"9222D",
"BiologicalIntent":"",
"CharacteristicName":"Fecal Coliform",
"Comment":"",
"DataLoggerLine":"",
"DepthHeight":"",
"DepthHeightUnit":"",
"DetectionCondition":"",
"MethodSpeciation":"",
"ParticleSizeBasis":"",
"QualifierCode":"",
"ResultUnit":"#/100ml",
"ResultValue":"300",
"SampleFraction":"",
"StatisticalBaseCode":"",
"Status":"Final",
"TaxonomicName":"",
"TemperatureBasis":"",
"TimeBasis":"",
"ValueType":"Actual",
"WeightBasis”:””
],
"SampleCollectionEquipmentComment":"",
"SampleCollectionEquipmentName":"Miscellaneous (Other)",
"SampleCollectionMethodIdentifier":"GRAB",
StartDate":"1998-10-07",
"StateCode":"CO",
"StartTime":"",
"StartTimeZone":"",
"TopDepthHeight":"",
"TopDepthHeightUnit":""
}
]
JSON is a hierarchical representation that can have multiple nesting levels.
Arrays are indicated by [ ]
, objects are indicated by { }
and name:value
pairs define data for an object.
However, a table is a flat data structure,
which requires that some JSON data values are repeated in the output rows.
The command has the following functionality:
- All JSON names are converted to table columns. Currently processing of names is case-independent and redundant names will overwrite (“NAME1” is equivalent to “name1”).
- Types for table columns are determined by examining the JSON structure:
- Quoted values are interpreted to be strings.
- Booleans are handled directly.
- Numbers default to double precision output.
- Command parameters
DateTimeColumns
,DoubleColumns
,IntegerColumns
, andTextColumns
can be used to override the default data type mapping.
- Currently only one top-level embedded array can be handled (see
Projects
andResults
names in above examle), with the 2nd and following arrays being ignored. In the future, full recursion to flatten “deep” objects may be implemented. When the object’s array is encountered:- separate table rows will be added for each array item
- columns prior to the array will be filled with the first rows associated with the object to repeat content
- columns after the array will similarly be filled by repeating content In other words, the array determines the number of rows for the object and some column values are repeated to fill out the duplicate rows. This results in a flat table representation of JSON where some column content is repeated. Uniqueness for the row must then be determined by considering array values.
- The
ExcludeNames
parameter is provided to exclude JSON names from processing. Because only one top-level object array is currently supported, this parameter can be used to exclude arrays to ensure that the desired array to process is found. For example, useExcludeNames=Projects
to ensure that the Results array is processed into output as the only array allowed for top-level objects.
Command Editor
The following dialog is used to edit the command and illustrates the command syntax.
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. |
ExcludeNames |
List of JSON names to exclude from table, separated by commas. | Include all names. |
DateTimeColumns |
List of comma-separated column names for columns that should be treated as containing date/time values. | Automatically determine column types from data. |
DoubleColumns |
List of comma-separated column names for columns that should be treated as containing double-precision (floating point number) values. | Automatically determine column types from data. |
IntegerColumns |
List of comma-separated column names for columns that should be treated as containing integer values. | Automatically determine column types from data. |
TextColumns |
List of comma-separated column names for columns that should be treated as containing text values. | Automatically determine column types from data. |
Top |
Specify the number of data rows to read, useful when prototyping an analysis process. | Process all rows. |
Examples
See the automated tests.
Troubleshooting
See Also
WebGet
commandWriteTableToDelimitedFile
command