Skip to main content

xSuite Interface Windows Prism 5.x – Online Help

Index Data Reader "Json"

The "Json" index data reader is a generic index data reader for JSON files with a largely arbitrary structure.

Property

Description

ProcessReadIndex[].ItemProperty[]

Subparameters defining a property in the JSON object that contains subobjects, each representing a row of tabular data:

  1. ItemProp*: related property in JSONPath syntax

  2. FieldName: table name of the target fields in the field catalog (optional)

If there are multiple JSON properties whose values are to be assigned to different target tables, the .ItemProperty[] property can be defined multiple times with different FieldName parameters. If the parameter is omitted, the entry will apply globally to all tables.

Specify the path to the source property in the field mappings under ProcessReadIndex[].Field[].InputName. For tabular data, define this path relative to the child objects determined via the ItemProp parameter.

ProcessReadIndex[].ConvertUtcDate

Conversion type of dates and times to local time

Designations of date and time are transferred in JSON format as string values. The syntax generally used is Coordinated Universal Time (UTC). This configuration property determines whether such values are converted to local time, and if so, in which way:

  • Never: never convert to local time

  • Always: always convert to local time

  • HasTime: only convert to local time if the time portion is not empty; i.e., not 00:00:00 (default value)

    Notice

    HasTime is based on the assumption that pure dates without a time component are usually fixed values (e.g., a document date). Conversion to a different time zone could inadvertently shift these dates by one day.

    If, on the other hand, there is a time component, it is assumed that it is relevant and should be converted (such as the processing time).

For defining the path to a JSON property to be read in the .InputName property, a variant of the JSONPath syntax is used. This variant is based on the XPath syntax used in the context of XML files (see Index Data Reader "Xml").

In the following, we will illustrate basic aspects of this JSONPath syntax with some examples, focusing only on the JSONPath decimal notation, where property names are separated by decimals points.

Notice

For a detailed description of the JSONPath syntax, see the originator of this syntax at http://goessner.net/articles/JsonPath. Test suites are available online, for example, under the search term "JSONPath Tester."

To read the value of a specific property, specify the key name of that property. For nested objects, specify the path from the root object to the property being searched for. The root object has the placeholder $ and the current element has the name @ (primarily for use in a filter). Array elements can either be addressed via their index number or selected via a filter expression ?(...). The counting of index numbers starts at 0. If you specify *, all array elements will be selected (e.g., to read multiple values for tabular target fields – if not already explicitly separated into subobjects by line via the .ItemProperty[] property above).

In the following examples, the header data field is to be read with the "InvoiceNo" and the item data field with the "ItemNo."

Notice

In practice, for "ItemNo" it is preferable to define $.Document.Items as an .ItemProperty[] configuration property and then to address the source field using ItemNo.

Notice

The use of the root object $ is optional. In the following examples, the root object is always included for the sake of completeness.

JSON fragment

JSONPath expression

{
   "Document": {
      "Fields": {
         "InvoiceNo": "123456"
      }
   }
}

$.Document.Fields.InvoiceNo

{
   "Document": {
      "Fields": [{
            "Name": "InvoiceNo",
            "Value": "123456"
         }
      ]
   }
}

first array element:

$.Document.Fields[0].Value

specific array element using name filters:

$.Document.Fields[?(@.Name=='InvoiceNo')].Value

{
   "Document": {
      "Items": [{
            "ItemNo": 1
         },{
            "ItemNo": 2
         }
      ]
   }
}

all array elements:

$.Document.Items[*].ItemNo

The JSON format supports typed values. The program adopts the typed values, generally making it unnecessary to define a data type in ProcessReadIndex[].Field[].InputFormat for parsing the input values. An exception might be the date type, for which the JSON format uses string values. Common syntaxes (e.g., the ISO format) are nevertheless implicitly recognized by the JSON parser. For other syntaxes, configure the .InputFormat property explicitly for date values.

In this section: