JSONPath filter
The JSONPath filter Extracts data from a cell that contains a JSON expression, using a JSONPath to select the data.
The filter will ignore cells that do not contain valid JSON expressions. leaving their original values in place. If a cell contains a valid JSON expression but no matching values, then it will end up empty. If a cell contains multiple matching values, then they will end up as a JSON list.
You can use the tag pattern and row queries options below to be selective about where you apply the filter. An invalid JSONPath expression will cause an error.
JSONPath expression: a JSONPath expression identifying the data to extract, e.g. foo.bar[3]
Tag patterns: a list of Tag patterns for the columns to consider. If omitted, the filter will apply to all columns.
Apply only to rows matching this query: a row query matching the rows to consider. If omitted, the filter will apply to all rows.
Flatten: if there are multiple results for the path, instead of printing a JSON list, use a flat text list separated with " | ".
Extracting in the #status column using the JSONPath metadata.status
| #org | #sector | #adm1 | #status |
|---|---|---|---|
| UNICEF | WASH | Coast | {"metadata": {"status": "current", "funded": 1}} |
| UNICEF | Health | Coast | {"metadata": {"status": "current", "funded": 0}} |
| UNICEF | Health | Coast | {"metadata": {"status": "completed", "funded": 1}} |
| TdH | WASH | {"metadata": {"status": "completed", "funded": 1}} |
| #org | #sector | #adm1 | #status |
|---|---|---|---|
| UNICEF | WASH | Coast | current |
| UNICEF | Health | Coast | current |
| UNICEF | Health | Coast | completed |
| TdH | WASH | completed |
When importing HXL from a JSON dataset, it is common to have a value that's actually an entire substructure. Use this filter in combination with the Add column filter to duplicate the column first (using the Add column filter's special {{#hashtag}} notation), then apply different JSON paths to different columns.