User Tools

Site Tools


transformations:parsejson

PARSE JSON

Category: Transform / Advanced


Description

Parse text values that are JSON objects into a tabular dataset.


Action settings

SettingDescription
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


See also

transformations/parsejson.txt · Last modified: 2021/07/18 22:46 by craigt