JSONata

JSONata vs. chains of json_extract

DuckDB ships a rich, vectorized JSON function library — json_extract, json_extract_string, the ->/->> operators, and the JSON type. For one-field-one-path access, those are the right tool and you should not reach for this extension at all. JSONata becomes the better fit once the expression starts to fan out — its single string collapses what native JSON would express as unnest plus a subquery.

• • Array predicate filters: Phone[type="mobile"].number selects matching elements and projects in one step. The native equivalent extracts the array, unnests it, filters with a WHERE, and projects in a correlated subquery.
• • Object reshaping: An object-construction literal builds a fresh JSON object — pulling, renaming, and computing fields together. Natively that's one json_extract_string per output field, which gets unwieldy past two or three fields.
• • Embedded aggregation and higher-order ops: $sum, $count, $max and $map, $filter, $reduce operate on arrays inside the document, keeping per-row roll-ups inline instead of leaving the JSON value to unnest and re-aggregate.
• • The break-even: Roughly: any array predicate, any reshape, or more than two output fields tilts toward JSONata. A single field at a fixed path stays simpler in native DuckDB JSON — don't add an extension for it.

How it works

The extension is a scalar UDF — no ATTACH, no secrets, no I/O. A call parses the JSONata expression into a program, evaluates it against the supplied JSON value, and returns a JSON result. The detail that matters most for performance isn't obvious from the signature.

• • Constant expressions are parsed once: When the expression argument is a SQL constant — by far the common case — the parser runs a single time and the compiled program is reused for every row. Per-row cost is then just the JSON walk plus operator application, not a parse-and-compile on each row, so a static expression over a million rows still parses exactly once.
• • JSON in, JSON out: Both the input value and the return type are JSON. Cast or unnest downstream depending on what the expression yields — result::VARCHAR, result->>'$', or unnest(result::JSON[]) for arrays — exactly like any other DuckDB JSON expression.
• • Two arities, parse cache intact: The 3-arg form takes a bindings JSON object whose keys become $variable names inside the expression. This is how you pass per-query values — thresholds, ids, dates — into an otherwise static expression without breaking the parse-once optimization, since the expression text stays constant.

Scope and caveats

The extension wraps the JSONata language faithfully, but it lives inside SQL — the surrounding context is DuckDB, not Node.js. A few things are worth knowing up front.

• • Single scalar function surface: Everything is reached through one function — there are no helper UDFs for individual JSONata operators. The expression string carries the whole computation. (It is not a JSON loader either; use DuckDB's read_json for that.)
• • Expressions are SQL strings: JSONata expressions live inside a SQL VARCHAR literal, so embedded double quotes need SQL escaping. For long or fiddly expressions, store them in a CTE or a SQL variable and reference by name — and iterate in the official JSONata exerciser, whose evaluator matches what the extension runs.
• • No DuckDB-UDF callbacks: JSONata's JavaScript-binding feature isn't wired up — you can't register a DuckDB function as a callable inside the expression. Stick to JSONata's built-in function library.
• • Per-row cost scales with document and expression size: Deeply-nested documents and complex expressions both cost, and they scale linearly with the data they touch. For very wide rows, materialize the JSONata projection once into a column instead of re-evaluating it in every downstream query.

Extract a nested field

SELECT jsonata('Account.Name',
               '{"Account":{"Name":"Firefly"}}');

Filter an array with a predicate, then project

SELECT jsonata('Phone[type="mobile"].number', payload)
FROM contacts;

Reshape JSON in one expression

SELECT jsonata('{
  "name":   FirstName & " " & Surname,
  "mobile": Phone[type="mobile"].number
}', payload) AS reshaped
FROM contacts;