extract_json_values

Uses a simplified JsonPath-like syntax to extract values from JSON objects. Supported syntax:

Dot notation: address.city, address.anotherLevel.key
Array index: phoneNumbers[0].type, phoneNumbers[1].number
Array slice (all elements): phoneNumbers[:], phoneNumbers[::]
Array slice (range): phoneNumbers[0:2], phoneNumbers[0:2:1]
Array slice (with step): phoneNumbers[::2]
Quoted keys (for special characters): address["other info"]
Root array access: $[:].firstName

Not supported:

Wildcard [*] — use [:] instead
Negative indices [-1]
Recursive descent ..

See darro#243 for tracking support of these operators.

Usage

The following examples show how the step can be used in a recipe.

Examples

Extract a simple nested value.

extract_json_values(ds.json_col, {
  "path": "address.city",
  "type": "text"
}) -> (ds.cities)

Extract a value from the first element of an array.

extract_json_values(ds.json_col, {
  "path": "phoneNumbers[0].type",
  "type": "category"
}) -> (ds.first_phone_type)

Extract values from all elements of an array using slice notation.

extract_json_values(ds.json_col, {
  "path": "phoneNumbers[:].type",
  "type": "list[category]"
}) -> (ds.all_phone_types)

Extract values from a range of array elements.

extract_json_values(ds.json_col, {
  "path": "items[0:3].name",
  "type": "list[category]"
}) -> (ds.first_three_names)

Extract a deeply nested value.

extract_json_values(ds.json_col, {
  "path": "address.anotherLevel.key",
  "type": "text"
}) -> (ds.deep_value)

Access a key with special characters using quoted bracket notation.

extract_json_values(ds.json_col, {
  "path": "address[\"other info\"]",
  "type": "text"
}) -> (ds.other_info)

Extract from root-level arrays using the $ symbol.

extract_json_values(ds.json_col, {
  "path": "$[:].firstName",
  "type": "list[category]"
}) -> (ds.first_names)

Extract numeric values from an array as a multivalued number column.

extract_json_values(ds.json_col, {
  "path": "scores",
  "type": "list[number]"
}) -> (ds.all_scores)

Extract dates in ISO 8601 format (e.g. “2024-01-15T10:30:00” or “2024-01-15”). These are parsed automatically.

extract_json_values(ds.json_col, {
  "path": "created_at",
  "type": "date"
}) -> (ds.created_at)

For dates in non-standard formats (e.g. “15/01/2024”, “01-15-2024”), extract as category first and then use cast with a format string, because extract_json_values does not support custom date format parsing.

extract_json_values(ds.json_col, {
  "path": "event_date",
  "type": "category"
}) -> (ds.event_date_raw)

cast(ds.event_date_raw, {
  "type": "date",
  "format": "%d/%m/%Y"
}) -> (ds.event_date)

General syntax for using the step in a recipe. Shows the inputs and outputs the step is expected to receive and will produce respectively. For futher details see sections below.

extract_json_values(text: text|category, {
    "param": value,
    ...
}) -> (value_extracted: column)

Inputs & Outputs

The following are the inputs expected by the step and the outputs it produces. These are generally columns (ds.first_name), datasets (ds or ds[["first_name", "last_name"]]) or models (referenced by name e.g. "churn-clf").

Inputs

column[text|category]

required

A text column with Json values to extract parts from.

Outputs

column

required

The column resulting from evaluating the JsonPath expression on the input column.

Configuration

The following parameters can be used to configure the behaviour of the step by including them in a json object as the last “input” to the step, i.e. step(..., {"param": "value", ...}) -> (output).

Parameters

string

required

JsonPath-like string used to extract values from the JSON column. Supports dot notation, array indices, slices and quoted keys. Does not support wildcard [*], negative indices or recursive descent (..).

Examples

address.city
phoneNumbers[:].type

string

required

Output column type. Select the desired type using a shortened yet fully specified name.Values must be one of the following:boolean category date number text url list[number] list[category] list[url]

Report

Analyse

Prepare

Usage

Inputs & Outputs

Configuration