Parse JSON / XML and output tabular data

The "Parse text" block is used to parse the contents of a text field containing JSON or XML and output one or more records resulting from each cell.

It treats each individual cell in the input data field as if it were an entire text file, and then tries to interpret each ‘file’ (here a cell) as JSON/XML. The resulting data is appended for all cells, similar to how Omniscope's Batch Append block works for actual files.

Note, Parse text is designed for cases where you are extracting tabular data data (typically with multiple rows and columns) from each input cell, and will append the results together, along with the replicated input data. 

Tip: If instead you have a simple JSON document in your cell, and only wish to extract a single value from it, use the Field Organiser block with a formula and the LOOKUPJSONVALUE function, using JSONPath in the 2nd argument (see online evaluator). For example if your input field "Json" had cells like {"someAttribute": "abc"}, and you wanted to retrieve "abc", your formula would need to be:

LOOKUPJSONVALUE([Json], `$.someAttribute`).

A typical use case for this block is to parse the output of a Batch HTTP block. For example, consider a REST API endpoint returning structured data encoded as JSON object. We can use the Batch Http block to invoke the REST API endpoint multiple times, based on a set of parameters.

The Batch HTTP block will output a record for each REST API endpoint call, and the JSON document will be within one of the fields in the output record. We can use the Parse text block to parse and extract the tabular data from each document.

N.B. In order to leverage the automatic configuration of data structure, the block connected to the input of the parse text block must be fully executed.

In a wider context - this block's functionality will allow you to parse data from an XML file, or JSON retrieved from an API, or nested in some other source, and easily reformat and analyse it in your report.

Attached to this article is the IOZ demo file demonstrating how to extract crypto tokens price from an API.