DriveWorks Pro 22

SppConvertJsonToTable

SppConvertJsonToTable

Converts JSON (array or object) into a DriveWorks table.

Note: 3-dimensional array elements are returned as a string.

The function returns a table.

If the JSON is not valid an error string is returned.

Syntax

SppConvertJsonToTable([JSON])

Where:

JSON is the JSON as a string to convert.

Blank values

Blank Values at Parent (Root) Level

When passing JSON Objects or Arrays, blank values must be defined as null.

For example:

{"title":"Harry Potter","price":null}

However, JSON Literals will convert blank values in the table.

For example:

[3,,5]

Blank Values at Child Level

Since DriveWorks tables are only two-dimensional, any third dimension (children) of an array is converted to a string within its parent.

As such, blank values are converted to undefined for children.

For example:

{"title":"Harry Potter","price":[20,35,,16]}

Will be converted to:

Invalid Syntax

This function validates the JSON being passed into it in accordance with ECMA 404 - The JSON Data Interchange Standard.

Invalid JSON will return the following error result:

#SPPCONVERTJSONTOTABLE! Invalid JSON.

Example 1a - Using JSON Object

Note: A single object is converted to a one row table.

Table headers are taken from property names.

Example Data

The following JSON Object data is stored in the constant DWConstantJSONObject.

Example Rule

Example Result

Example 1b - Using JSON Object with Children

Note: A single object is converted to a one row table.

Table headers are taken from property names.

Since DriveWorks tables are only two-dimensional, any third dimension (children) of an array is converted to a string within its parent.

Example Data

The following JSON Object data is stored in the constant DWConstantJSONObject.

Example Rule

Example Result

Example 2a - Using JSON Array

Note: A single row represents an object in the array, and the row order reflects the object’s position in the array.

Table headers are taken from the property names.

Example Data

The following JSON Array data is stored in the constant DWConstantJSONArray.

Example Rule

Example Result

Example 2b - Using JSON Array with Children

Note: A single row represents an object in the array, and the row order reflects the object’s position in the array.

Table headers are taken from the property names.

Since DriveWorks tables are only two-dimensional, any third dimension (children) of an array is converted to a string within its parent.

Example Data

The following JSON Array data is stored in the constant DWConstantJSONArray.

Example Rule

Example Result

Example 3a - Using JSON Array Literal

Note: Array literals are converted to a single column table with a blank header.

Example Data

The following JSON Array Literal data is stored in the constant DWConstantJSONLiteral.

Example Rule

Example Result

See Also

The following functions may be of use when working with JSON.

XPathJsonGet

SppJSONLoad

Useful Resources

Introduction to JSON - JSON.org

Online JSON validator - JSONLint - The JSON Validator

ECMA Interntaional - ECMA 404 - The JSON Data Interchange Standard.