How can we help?

About JSON and XML data sources

Many data sources return data in JSON (JavaScript Object Notation) and XML (eXtensible Markup Language) formats. This article describes the structure of JSON and XML data sources and how to access their data in Klipfolio. Watch the workshops below for more information.

Contents

In this article, you will find information and examples on the following topics associated with working with JSON and XML data sources.

About the structure of JSON and XML data

JSON and XML are hierarchical data formats.

Where tabular data is referenced by column and row, JSON and XML data is referenced by its path, which indicates its position in the hierarchy. This path, also called the XPath, describes the levels traversed to reach the elements. For example, in the following data source:

player/first_name is the XPath of the first_name element

player/team/wha is the XPath of the wha element

JSON data is made up of these elements:

objects: one or more field name and value pairs, indicated in Klipfolio by curly braces.

arrays: lists of elements of the same type, indicated in Klipfolio by square brackets.

Selecting a JSON or XML data source

The first image below shows how to choose a data source in the formula editor. The second image shows how the XPath is displayed in the formula once the data source is chosen.

The @ and ; outlined in purple are the data source reference and enclose the editable XPath, which is outlined in red.

Note: The ; is only displayed when the XPath is being edited.

Some data sources use '@' as the first character in their field name (shown in this example) or as an attribute. It is important not to get them confused with the data source reference.

Using the Selection Options in JSON or XML data

When you select data from a JSON or XML data source, the selected field’s path, or XPath, is displayed in the formula editor. You can type the path or you can click on the Selection Options link.

In the following image, the XPath of the first_name element is /player/first_name.

By default, the values of all peer elements are selected (equivalent to selecting a column of data in a table). If you need to change this selection, click Selection Options, and select the required option:

Select only this <field name> element. Only one specific <field_name> element is selected (equivalent to selecting a specific cell in Excel). The formula editor displays the specific path: @/player[1]/first_name[1].

Select all peer <field name> elements. All <field_name> elements at this level of the hierarchy are selected (equivalent to selecting a column of data in a table). The formula editor displays the path: @/player/first_name.

Select all <field_name> elements. All <field_name> elements at any level of the hierarchy are selected: @//first_name.

Select all <field_name> elements in this parent element. All <field_name> elements in this branch of the hierarchy are selected:@/player[1]/first_name.

XPath Expressions

An XPath expression is used to select elements based on specified criteria.

Some common functions used in XPath expressions:

name

last

position

contains

substring

count

Example 1: select elements by matching a specific value

To select the last names of players on team Winnipeg:

Select the last_name of a player: @/player/last_name This will return all player/last_name elements.

Type the expression, [team/name='Winnipeg'], after /player to select only the last names of players on team Winnipeg.

The resulting XPath: @/player[team/name='Winnipeg']/last_name

This says: for all player elements with descendant team/name equal to Winnipeg, select the last_name element.

Example 2: select elements based on a field name

This example uses the contains function and the name function to select all elements based on a field name.

Type: @/player/*[contains(name(),'name')]

This says: for all /player elements, select any values where the field name contains: "name"

JSON elements with field names that begin with non-alphabetic characters (as shown in the following data source) cannot be selected directly. Instead it is necessary to manually type the path in the formula editor.

Special Cases

Field names that begin with a non-alphabetic character cannot be selected directly; see the Example 4 in XPath Expressions.

Advanced Usage

Klipfolio functions like DATASOURCE and CONCAT can be used together for more flexible XPath editing.

Select elements matching a set of values

In the XPath Expressions section, the first example selected elements by matching a specific value, where /player[team/name='Winnipeg']/last_name selected the last_name for all /player elements with descendant team/name equal to Winnipeg.

To select last_name elements matching multiple values, you can use a User Input Control and create variable. In this example, we use the variable, city: