JSONTODATA

Overview

The JSONTODATA workflow application lets you parse JSON content into process data.

How it works

• The JSONTODATA application requires either the JSON_CONTENT or the JSON_CONTENT_FILE parameter; these correspond to the JSON to parse.

• The application supports the JSONPath query language (see https://github.com/json-path/JsonPath), which allows extraction of specific data from JSON content, similar to XPath expressions in XML.

• Application logs are available. These can be specified by setting the value of the JsonToDataLogLevel parameter in the web.config file to 0 to deactivate logs, 1 for error logs, 2 for information logs, or 3 for debug logs; the default value is 0.

Parameters

Parameter	Type	Direction	Description
JSON_CONTENT	TEXT	IN	Query to execute
JSON_CONTENT_FILE	FILE	IN	Query to execute, stored in a file

Parameter mapping

Using simple parameter names

The application supports custom OUT parameters to map simple JSON content.

📌 Example

{
    "person": {
        "address": {
            "street": "160 Guy Street",
            "zipcode": "J4G 1U4"
        },
        "age": 30,
        "name": "John"
    }
}

The following parameters allow you to map the JSON content to different process data:

Parameter	Type	Direction	Retrieve the value into a data	Result
person.address.street	Text	OUT	DATA_STREET	160 Guy Street
person.address.zipcode	Text	OUT	DATA_ZIPCODE	J4G 1U4
person.age	Text	OUT	DATA_AGE	30
person.name	Text	OUT	DATA_NAME	John

Using JSONPath query language

The application supports the JSONPath query language, similar to XPath expressions in XML. This language allows you to retrieve specific data from a JSON. For more details regarding the JSONPath syntax, see https://github.com/json-path/JsonPath.

📌 Example

{
    "person": {
      "name": "Elizabeth",
      "age": 85,
      "dob": "1937-09-23T00:00:00Z",
      "children": [
        {
          "name": "Charles",
          "age": 60,
          "children": [
            {
              "name": "Nathalie",
              "children": [
                {
                  "name": "George",
                  "age": 8
                },
                {
                  "name": "Charlotte",
                  "age": 10
                },
                {
                  "name": "Jefferson",
                  "age": 7
                }
              ]
            },
            {
              "name": "Harry"
            }
          ]
        },
        {
          "name": "Bob",
          "age": 57,
          "children": [
            {
              "name": "John"
            },
            {
              "name": "Mark"
            }
          ]
        }
      ]
    }
}

In the above example, we want to get the names of Charles's grandchildren who are older than seven years old, and we also want these names to be separated by a | (using the APP_JSONPATH_DELIMITER IN parameter). At the same time, we are also getting Elizabeth's age and date of birth. To get the information, the following parameters must be defined:

Parameter	Type	Direction	IN Value	OUT Value
APP_JSONPATH_DELIMITER	Text	IN	|
PARAM1__JSONPATH	Text	INOUT	person.children[?(@.name == 'Charles')].children[*].children[?(@.age > 7)].name	data DATA1_VALUE (value will be: George|Charlotte)
AGE__JSONPATH	Text	IN	$.person.age
AGE	Numeric	OUT		data AGE_VALUE (value will be: 85)
DOB__JSONPATH	Text	IN	$.person.dob
DOB	DateTime	OUT		data DOB_VALUE (value will be: 1937-09-23T00:00:00Z)

• In the PARAM1__JSONPATH parameter name, the PARAM1 name is not relevant, but it must be followed by the __JSONPATH suffix (two underscores are used in the suffix).

• To retrieve a value into a Text process data, you can use a single INOUT Text parameter (e.g. PARAM1__JSONPATH parameter as in the example above).

• To retrieve a value into a Numeric or DateTime process data, you must use separate parameters: an IN Text parameter for the JSONPath query and an OUT parameter that maps to the Numeric or DateTime process data. Both parameters must share the same name prefix (e.g. AGE__JSONPATH and AGE parameters as in the example above).

• The default value of APP_JSONPATH_DELIMITER is a comma (,) when this parameter is not defined.