transformations:parsejson
Table of Contents
PARSE JSON
Category: Transform / Web
Description
Parse text values that are JSON objects into a tabular dataset.
Action settings
| Setting | Description |
|---|---|
| Column | The column containing the source JSON to be parsed. |
| JSONPath* | The path to the JSON node from which parsing starts. Learn more about the path syntax. |
| Column names | Select the method to be used to name columns in the result dataset. Options: Property name, Full JSON path |
| Properties | Select which properties to parse. One property corresponds to one column in the result dataset. Options: Parse all properties or Parse selected properties (and select which properties to parse). |
* Setting can be specified using a parameter.
Advanced options
| Setting | Description |
|---|---|
| Don't delete original column | When checked, the column with parsed JSON(s) will be kept in the result dataset. |
| Parsing mode | Select how nested JSON objects and arrays are turned into rows and columns. Options: Dense, Sparse (see "Parsing modes" below). |
Parsing modes
| Mode | Description |
|---|---|
| Dense | Default option. Properties of nested objects are placed in the same row as their parent's properties, and a new row is created only for each array item. |
| Sparse | Each nested object gets its own row, and an empty column is added for each array, so the result has more rows and more empty cells. |
Examples
Example #1
The data below was contained in the "Response-Body" column (kept for parsing). The column names ("date", "localName", etc.) are embedded within the data. (Line breaks have been inserted for clarity.)
Before (source table)
[{"id":"01","name":"John","Age":"23","Kids":true},
{"id":"02","name":"Louis","Age":"33","Kids":false},
{"id":"03","name":"Mark","Age":"44","Kids":false},
{"id":"04","name":"Tom","Age":"22","Kids":false}]
After (result table)
| id | name | Age | Kids |
|---|---|---|---|
| 01 | John | 23 | true |
| 02 | Louis | 33 | false |
| 03 | Mark | 44 | false |
| 04 | Tom | 22 | false |
Action parameters
Column: Response-Body
JSON Path: <Root>
Column names: Property name
Properties: Parse selected properties
Selected properties: [*].id, [*].name, [*].Age, and [*].Kids
Community examples
- Example "Getting public holidays from an API with Web Request" (Module: Main; Group: Tab 1; Table: Public holidays; Action position: 3)
- Example: use of Web Request and Iterate Web Request with the Community forum API (Project; Module: Main; Group: Tab 1;
Table: User details; Action position: 6) - How to load JSON file (Project; Module: Main; Group: Tab 1; Table: Imported table 1; Action position: 3)
- How to pull data from web APIs with pagination (Project; Module: Main; Group: Group 1; Table: Query API with pagination;
Action position: 4) - Example: obtaining stock price history from web API (Project; Module: Main; Group: Group 1; Table: Stock history; Action position: 3)
See also
transformations/parsejson.txt · Last modified: by roberto