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. |
| JSON Path* | The path to the JSON node from which parsing starts. |
| Column names | Select the method to be used to name columns in the result dataset. |
| Don't delete original column | When checked, the column with parsed JSON(s) will be kept in the result dataset. |
| 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.
Examples
Source file: Public holidays (partial dataset)
The following JSON file was formatted with multiple columns. The data below was contained in the "Response-Body" column, which was the only column kept for parsing. In this format, the column names ("date", "localName", etc.) are embedded within the data. (Line breaks have been inserted for clarity.)
[{"date":"2019-01-01","localName":"New Year's Day","name":"New Year's Day","countryCode":"CA","fixed":true,"global":true,"counties":null,"launchYear":null,"type":"Public"},
{"date":"2019-02-18","localName":"Louis Riel Day","name":"Louis Riel Day","countryCode":"CA","fixed":false,"global":false,"counties":["CA-MB"],"launchYear":null,"type":"Public"},
{"date":"2019-02-18","localName":"Islander Day","name":"Islander Day","countryCode":"CA","fixed":false,"global":false,"counties":["CA-PE"],"launchYear":null,"type":"Public"},
{"date":"2019-02-18","localName":"Heritage Day","name":"Heritage Day","countryCode":"CA","fixed":false,"global":false,"counties":["CA-NS"],"launchYear":null,"type":"Public"}]
Action parameters:
Column is "Response-Body"
JSON Path is "<Root>"
Column names is set to "Property name"
Properties is set to "Parse selected properties"
Selected properties: "[*].countryCode", "[*].date", "[*].global", and "[*].name"
Result table:
| countryCode | date | global | name |
|---|---|---|---|
| CA | 2019-01-01 | TRUE | New Year's Day |
| CA | 2019-02-18 | FALSE | Louis Riel Day |
| CA | 2019-02-18 | FALSE | Islander Day |
| CA | 2019-02-18 | FALSE | Heritage Day |
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: 2022/10/02 20:16 by craigt