JSON extraction

Beetl includes the JSON functions from datafusion-functions-json (version 0.54), in queries and in pipeline stages alike. They read JSON stored as text in any column, such as a decoded webhook payload or a CSV column that holds JSON. Every function takes the JSON text first and then a path.

Get JSON text from a webhook

A Webhook dataset stores each request body base64-encoded in payload. Decode it and cast the result to text before you call a JSON function, because decode returns binary and the JSON functions only accept text:

WITH decoded AS (
  SELECT ingested_at, CAST(decode(payload, 'base64') AS VARCHAR) AS body
  FROM shop_orders_events
)
SELECT
  ingested_at,
  json_get_str(body, 'order_id') AS order_id,
  json_get_str(body, 'customer', 'country') AS country,
  json_get_int(body, 'items', 0, 'quantity') AS first_item_quantity
FROM decoded

Paths

Pass each step of the path as its own argument. A text argument is an object key, and an integer is a position in an array, counted from 0. For a body such as:

{"order_id": "A-1001", "customer": {"country": "DE"}, "items": [{"sku": "K-7", "quantity": 2}]}

json_get_str(body, 'customer', 'country') returns DE, and json_get_int(body, 'items', 0, 'quantity') returns 2. With no path, a function reads the whole document, so json_length(body) counts the top-level keys.

JSONPath strings are not parsed. json_get_str(body, '$.customer.country') looks for a key literally named $.customer.country and returns NULL. Negative array positions also return NULL.

Functions

None of these raise an error on bad input. A missing key, a value of the wrong type, a JSON null or text that is not valid JSON all give NULL, so check unexpected NULLs against the raw JSON before you trust a count or a sum.

Operators

The PostgreSQL operators also work. -> does the same as json_get and ->> the same as json_as_text, so you can chain them:

SELECT body->'customer'->>'country' AS country FROM decoded

json ? 'key' is short for json_contains(json, 'key').

Mixed-type results

json_get and -> return a value whose type can differ from row to row, shown in results as {int=2} or {str=DE}. You cannot compare it with a plain value: body->'status' = 'paid' fails to plan. Use ->> or a typed function for filters and joins.

Casting json_get to INT, BIGINT, DOUBLE, DECIMAL, BOOLEAN or VARCHAR runs the matching typed function, so (body->'quantity')::int behaves like json_get_int, including the NULL for negative numbers. A cast to DECIMAL gives a float, and a cast to DATE or TIMESTAMP returns NULL. For dates, read the text and parse it: to_timestamp(json_get_str(body, 'updated_at')).

Arrays

json_length counts the elements and a position reads one. To get a row per element, combine json_get_array with unnest and read each element as its own JSON:

WITH items AS (
  SELECT
    json_get_str(body, 'order_id') AS order_id,
    unnest(json_get_array(body, 'items')) AS item
  FROM decoded
)
SELECT
  order_id,
  json_get_str(item, 'sku') AS sku,
  CAST(json_as_text(item, 'quantity') AS BIGINT) AS quantity
FROM items

Typed columns in a pipeline

Extracting JSON on every query repeats the same work and leaves column types to each query. A pipeline that decodes the payload once and writes a Silver dataset with real types is easier to query, chart and join. Give each step its own stage in the builder.

Stage decoded:

SELECT message_id, ingested_at, CAST(decode(payload, 'base64') AS VARCHAR) AS body
FROM shop_orders_events

Stage orders:

SELECT
  json_get_str(body, 'order_id') AS order_id,
  json_get_str(body, 'status') AS status,
  TRY_CAST(json_as_text(body, 'total') AS DECIMAL(12, 2)) AS total,
  json_get_int(body, 'item_count') AS item_count,
  json_get_bool(body, 'paid') AS paid,
  to_timestamp(json_get_str(body, 'updated_at')) AS updated_at,
  ingested_at,
  message_id
FROM decoded

Give every output column a single type. Write strings with json_get_str, counts that are never negative with json_get_int, signed numbers and money through json_as_text with a cast, and dates through to_timestamp. Keep json_get and -> out of the final stage. Keeping message_id and ingested_at lets you deduplicate later.

Related