JSON

The JSON connector allows the retrieval and processing of JSON files, either from a disk, a Text, or from a defined URL.

Using JSON data

1. When creating a new data table, select JSON from the Connect to data dialog. The JSON Connection dialog displays.

   

2. Select the JSON file source:

1. • File

Then browse to the file source. By default , the Publish File to Server checkbox should be selected.

NOTE: If unchecked, the file will not be uploaded while publishing the workbook and no change in the file path will be done. Consequently, the data file should be copied in the <AppData>\data folder (i.e., c:\vizserverdata\data).

This is beneficial when the files are large. Also, it will be easier to update or overwrite data files on the server.

1. • Text

   Then enter the text block to be parsed.

2. 

3.  

   • Web URL

The dialog changes slightly to allow specification of the following:

 

• The absolute Path including the http where the JSON file is located

• The record that will be queried by the JSON path

• The HTTP Proxy setting that will allow the JSON connector to reach the endpoint

• Headers

• • Headers are separated by a comma

  • Each Header is entered as Name = Value, where Name and Value can be enclosed in double quotes to allow inclusion of any character except for double quotes

  • Name and Value can also be left unquoted, in which case they may not include comma or equals characters

NOTE:

In case of an invalid Header entry, the border of the input box changes to red. Modify the entry until the requirements are met or when the border changes to the default color.

 

Furthermore, if you enter a valid Header in key=value pair (e.g., panheader=testvalue), the rest of the invalid inputs will be ignored. Consequently, only the valid Header entries will be part of the request.

 

• The User ID and Password required to connect to the JSON files

Check the Show Characters box to display the entered password characters.

• Select the HTTP Method to map any of the following operations to HTTP requests

• GET -  retrieve information

• POST - create or update an entity

• PUT - replace an existing entity

• DELETE - remove a request

• Request Body (for HTTP POST)

• Set the required Content Type header (default is application/x-www-form-urlencoded)

The Context Type text box becomes editable only when there is a specified entry in the Request Body text box.

• Timeout in Seconds (to wait for server response)

Both the URL, Headers, User ID, Password, Request body, and Content Type can be parameterized, to allow dynamical data retrieval.

The standard settings controlling how the JSON file is parsed, is listed.

These include:

• Record JSON Path

• Columns and associated JSON Expressions

• Date/Time format expression to be parsed

 

NOTE:

To parse and format times with higher than millisecond precision, the format string needs to end with a period followed by sequence of lower case Fs. There can be no additional characters following them.

For example: yyyy-MM-dd HH:mm:ss.ffffff

• Whether the field is enabled to retrieval

The source table is returned with the title table and all fields listed.

3. The schema of the source file is then defined by clicking the Generate Columns… button.

This populates the list of columns, with the data type found from inspecting the first ‘n’ rows of the file.

4. Click Add Column… to add more columns and specify their properties.

5. The time zone of input parameters and output data is by default unchanged. Changing the time zone is supported through the Timezone list box, based on the assumption that data is stored in UTC time and outputs are presented in the selected time zone.

Likewise, the time zone can also be parameterized.

6. Click OK. The source data is returned in the Edit Data Table view, with the fields displayed in the Data Source Preview.