JSONata for DuckDB 2.0: expressions that can be stopped

There are two ways a recursive expression can get stuck. It can keep adding calls until the stack runs out, or it can use tail recursion and run forever without growing the stack. Fixing one does not fix the other.

Both cases mattered in the JSONata extension. A transformation that looked like an ordinary scalar function could crash DuckDB or leave a query running indefinitely. The 2.0 update adds checks for evaluation time, depth, native stack space, and query interruption. It also updates jsonata-cpp to 0.3.0, replaces the regular-expression engine, and stops parsing the same expression again for every row.

The ordinary query stays short

The function still takes a JSONata expression and a JSON value:

SELECT jsonata(
    '$sum(items.price)',
    '{"items": [{"price": 12}, {"price": 8}, {"price": 5}]}'
) AS total;

A third argument supplies variable bindings. That is useful when the transformation stays fixed and the parameters change:

SELECT jsonata(
    'items[price > $minimum].name',
    '{"items": [{"name": "coffee", "price": 12},
                {"name": "tea", "price": 8}]}',
    '{"minimum": 10}'
) AS name;

The result is JSON. The extension keeps JSONata’s sequence behavior, including returning a single value when a selection has one result. If a downstream query needs a list regardless of the number of matches, express that shape in the JSONata expression.

Give each evaluation a deadline

Two settings control the evaluator:

SELECT current_setting('jsonata_timeout_ms') AS timeout_ms,
       current_setting('jsonata_max_depth') AS max_depth;

The timeout applies to one evaluation. Twenty thousand fast rows do not collectively spend a single ten-second allowance. A recursive expression that never returns, however, must stop within its own evaluation budget:

SET jsonata_timeout_ms = 100;

SELECT jsonata('($f := function($x){ $f($x) }; $f(1))', '{}');

This raises a timeout error after the evaluation spends its 100 ms budget.

Reset the setting after experimenting with it:

RESET jsonata_timeout_ms;

jsonata_max_depth limits evaluation nesting. Setting either limit to zero disables that limit, but disabling the depth limit does not disable the native stack guard. Threads have different stack sizes across platforms; on a small stack, the guard can stop an expression before it reaches the configured depth.

The extension checks deeply nested JSON data and bindings before parsing them, and checks expression structure before handing it to the recursive parser. The point is to report a query error while the process still has enough stack space to report one.

Validate syntax before evaluating rows

jsonata_validate is new:

SELECT expression, jsonata_validate(expression) AS valid
FROM (VALUES ('$sum(items.price)'), ('$sum(')) t(expression);

This is a syntax check, not a trial execution. A syntactically valid recursive function can still time out. A valid expression can also fail on a document with the wrong types or call a function that does not exist. Use the check to reject malformed expressions when accepting them, then keep the evaluation limits in place when running them.

Reuse parsed expressions without reusing their answers

Parsed expressions now live in a small cache owned by an execution thread and query. A constant expression can be reused across a column; a column of expressions can reuse its recently encountered entries. The objects are not shared between execution threads because the library keeps thread-local evaluation state.

Caching the parsed expression must not cache a random result. The extension now marks expressions using $random, $now, $millis, or $eval as volatile. Expressions supplied by a column are treated conservatively too. For example:

SELECT count(DISTINCT jsonata('$random()', '{}')) > 1 AS varies
FROM range(100);

A pure constant expression can still be folded. The distinction lets DuckDB avoid repeated work when the result is fixed without turning one random draw into an entire column.

Check existing regular expressions

The regex implementation now uses RE2. Long subjects and patterns prone to catastrophic backtracking no longer use std::regex’s recursive matcher. Ordinary matching, splitting, replacement groups, Unicode, and the supported flags are covered by the extension’s tests.

There is a compatibility cost: RE2 does not support lookaround or backreferences in patterns. An expression using those constructs now fails validation or evaluation with an invalid-regex error. Replacement strings can still use captured groups, such as $2 $1; that is different from a backreference inside the pattern.

The implementation update, limit tests, and function tests record these behaviors. The extension page has the complete function reference. On upgrade, check your regex syntax and any legitimate transformation that needs more than the default ten seconds for one document.