Working with JSON

In online processes, it is common to send data to and retrieve data from a server. That data is often exchanged in JSON format. JSON is short for JavaScript Object Notation. It is a way to store information in a structured and easy-to-read format. It is often referred to as "XML without nodes" and it is designed for exchanging data.

Refer to the following online resources for more information on JSON and its syntax:

JSON support in Workflow tasks and scripts

PReS Workflow offers JSON support in and via the following tasks:

The XML/JSON Conversion task converts an XML job file to JSON or a JSON job file to XML.
The following OL Connect tasks accept JSON data as input: Create Email Content, Create Print Content, Create Web Content, Render Email Content, and the Create Preview PDF task.
When the OL Connect Execute Data Mapping task or the OL Connect Retrieve Items task is set to output Records in JSON, it outputs a JSON Record Data List (see Types of JSON in Workflow).
The OL Connect Send Get Data task can output its results to a JSON file.

In scripts written in any JSON-aware language (including JavaScript), JSON is obviously supported.

Note: Object methods (such as valueOf and toString) cannot be used in a JSON string in Workflow scripts. This is due to Microsoft’s implementation of the JScript library used in Workflow.

Certain methods in the Data Repository API accept or return JSON data.

Types of JSON in Workflow

Workflow tasks that support JSON accept or output one or two of the following types of JSON:

a regular JSON string, containing a JSON object or an array of JSON objects representing records. If a value in a record object is a string, it is considered to be a field value. If a value in a record object is a JSON object, it is considered to be a nested table with detail records. For examples, see JSON string examples.
a JSON Record Data List (see the REST API Cookbook). A JSON Record Data List is a proprietary JSON object type. It includes a schema entry with information about the types of all fields at the beginning of the record, and the data set with values after the schema. This structure allows for easy handling of REST API return values through scripting in Workflow or in the Designer; see JSON Record Data List example.

JSON string examples

The following JSON string samples show various techniques to incorporate data in a JSON string.

A simple JSON structure holding the first and last name of a person:

{
"first": "Peter",
"last": "Parker"
}

A JSON string with references to local variables and a Job Info variable (see About variables):

{
"first":"%{first}", 

"last":"%{last}",
"email":"%2"
}

A JSON string containing a local variable and various Data Repository selections (see Data Repository lookups):

{

"jobid":"%{jobid}",

"account":"lookup(OLCS_jobs, account, jobid, '%{jobid}')",

"datafile_name":"lookup(OLCS_jobs, datafile_name, jobid, '%{jobid}')",

"pages":"lookup(OLCS_jobs, pages, jobid, '%{jobid}')",

"documents":"lookup(OLCS_jobs, documents, jobid, '%{jobid}')",

"recordsetid":"lookup(OLCS_jobs, recordsetid, jobid, '%{jobid}')"

}

An example where the entire JSON string is provided in a Job Info variable:

%1

A JSON string constructed with information retrieved from an XML job data file (see XML data selections):

{
"first":"xmlget('/request[1]/values[1]/first[1]',Value,KeepCase,NoTrim)",

"last":"xmlget('/request[1]/values[1]/last[1]',Value,KeepCase,NoTrim)",

"email":"xmlget('/request[1]/values[1]/email[1]',Value,KeepCase,NoTrim)"
}

A JSON string that contains nested data:

{

"name":"Peter Parker",

"email":"parkerp@localhostcom",

"ExtraData":"foobar",

"detail": [{"id":"inv123","ExtraData":"hello"},{"id":"456","ExtraData":"world"}]

}

JSON Record Data List example

A JSON Record Data List describes a list of data fields (as name/value pairs), a data table schema and nested data records (if any) for one or more data records. Below is an example of such a JSON Record Data List.

{
    "data": {
        "schema": {
            "columns": {
                "ID": "STRING",
                "Gender": "STRING",
                "FirstName": "STRING",
                "LastName": "STRING"
            }
        },
        "fields": {
            "ID": "CU00048376",
            "Gender": "M.",
            "FirstName": "Benjamin",
            "LastName": "Verret"
        }
    }
}

For more examples, see the REST API Cookbook.

Values could be retrieved in JavaScript as follows:

var foo = record.fields.ID;
var bar = record.tables.detail[0].fields.ItemTotal;