JSON to Table
Verb: jsonToTable
Creates a data table from text in JSON format.
"JavaScript Object Notation (JSON) is an efficient data encoding format that enables fast exchange of small amounts of data between client browsers and web services." [MICROSOFT, 2019]
Syntax
jsonToTable [--handleError(Boolean)] --json(String) --jsonPath(String) (Boolean)=success (DataTable)=value (Numeric)=rows (Numeric)=columns
Inputs
| Script | Designer | Required | AcceptedTypes | Description |
|---|---|---|---|---|
| --handleError | Handle Error | Optional | Boolean | When enabled, allows script execution to continue even if an error occurs while executing the JSON to Table command. |
| --json | JSON | Required | Text | Text in JSON format, or file containing it, from which to extract the data table.
To insert a file with the text in JSON format, import this file as an asset and export it for use, using the Export Asset command. |
| --jsonPath | JSONPath Expression | Required | Text | Expression responsible for accessing the object, attributes, or attribute values in the JSON format text. There are the following expressions:
|
Outputs
| Script | Designer | AcceptedTypes | Description |
|---|---|---|---|
| success | Success | Boolean | Returns "True" if the table was successfully fetched, or "False" otherwise. |
| value | Data Table | Data Table | Returns the data table obtained from the text in JSON format. |
| rows | Rows | Number | Returns the number of rows in the table obtained. |
| columns | Columns | Number | Returns the number of columns in the obtained table. |
Example
the JSON to Table command gets all the attributes of a text in JSON format, using the expression "$" and assigns them to a data table, also returning the number of rows and columns it has and if they were successfully obtained.
defVar --name extractionSuccess --type Boolean
defVar --name extractedTable --type DataTable
defVar --name tableRows --type Numeric
defVar --name tableColumns --type Numeric
jsonToTable --json "{ \"Company\": [\"Microsoft Corporation\",\"Alphabet Inc.\", \"IBM Corporation\"],\r\n \"City\": [\"Redmond\", \"Mountain View\", \"Armonk\"] }\r\n" --jsonPath "$" tableColumns=columns tableRows=rows extractionSuccess=success extractedTable=value
logMessage --message "Success: ${extractionSuccess}\r\nRows: ${tableRows}\r\nColumns: ${tableColumns}\r\nTable: ${extractedTable}" --type "Info"
//This example returns the following output:
// Success: True
// Rows: 1
// Columns: 2
// Table: [
// "Microsoft Corporation",
// "Alphabet Inc.",
// "IBM Corporation"
// ], [
// "Redmond",
// "Mountain View",
// "Armonk"
// ]