> Documentation index: https://docs.beetl.io/llms.txt

# JSON extraction

> Reading fields, nested values and arrays out of JSON text, and turning them into typed columns.

Source: https://docs.beetl.io/query/json-functions/
Last updated: 2026-10-03

Beetl includes the JSON functions from
[datafusion-functions-json](https://github.com/datafusion-contrib/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](https://docs.beetl.io/sources/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:

```sql
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:

```json
{"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

| Function                          | Returns                                                                                                 |
| --------------------------------- | ------------------------------------------------------------------------------------------------------- |
| `json_get_str(json, path...)`     | A JSON string as text. `NULL` for numbers, booleans, objects and arrays.                                |
| `json_get_int(json, path...)`     | A whole number as a 64-bit integer, or a string holding one such as `"42"`. `NULL` for decimals.        |
| `json_get_float(json, path...)`   | A number as a float, or a string holding one.                                                           |
| `json_get_bool(json, path...)`    | `true` or `false`, also from the strings `"true"` and `"false"`.                                        |
| `json_as_text(json, path...)`     | Any value as text: strings without quotes, numbers and booleans as written, objects and arrays as JSON. |
| `json_get_json(json, path...)`    | The raw JSON of the value, ready to pass to another JSON function.                                      |
| `json_get_array(json, path...)`   | An array as a list with one JSON text per element.                                                      |
| `json_object_keys(json, path...)` | The keys of an object as a list.                                                                        |
| `json_length(json, path...)`      | The number of elements in an array or keys in an object. `NULL` for other values.                       |
| `json_contains(json, path...)`    | `true` when the path exists, including when its value is `null`.                                        |
| `json_get(json, path...)`         | The value with a mixed type. See below.                                                                 |

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 `NULL`s against the raw JSON
before you trust a count or a sum.

> **Read negative numbers as text.**
> `json_get_int` and `json_get_float` return `NULL` for negative numbers such as `-12.5`. For any
> field that can be negative, such as an amount, a balance or a temperature, read it as text and
> cast it: `CAST(json_as_text(body, 'total') AS DECIMAL(12, 2))`. Use `TRY_CAST` if some values
> might not be numbers.

## Operators

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

```sql
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:

```sql
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](https://docs.beetl.io/pipelines/builder).

Stage `decoded`:

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

Stage `orders`:

```sql
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](https://docs.beetl.io/pipelines/deduplicate-records) later.

## Related

- [Webhook](https://docs.beetl.io/sources/webhook)

- [DataFusion SQL](https://docs.beetl.io/query/datafusion-sql)

- [Deduplicate records](https://docs.beetl.io/pipelines/deduplicate-records)

- [Quarantine bad rows](https://docs.beetl.io/pipelines/quarantine-bad-rows)