Oracle: _json filters and json() return quoted strings, silently breaking every string comparison

Describe the Bug

On Oracle, every _json filter and json() function call against a string-valued JSON path silently returns the wrong rows. No error is raised — the comparison just never matches.

The cause is one incorrect assumption in FnHelperOracle.json:

// JSON_VALUE only returns scalar values (returns NULL for objects/arrays)
// JSON_QUERY only returns objects/arrays (returns NULL for scalars)   <-- not true
// COALESCE handles both cases
COALESCE(JSON_QUERY(??.??, '$.path'), JSON_VALUE(??.??, '$.path'))

JSON_QUERY does not return NULL for scalars on Oracle 23ai. It returns the scalar with its JSON quotes intact, so the COALESCE never falls through to JSON_VALUE, and a string value is extracted as "red" rather than red.

Probed directly against gvenzl/oracle-free:23-slim-faststart, on a row holding {"s":"red","obj":{"theme":"dark"}}:

That single quoting artifact accounts for the whole failure pattern:

Broken

• _eq / _neq / _in / _nin on strings — compared against "red", never red
• _starts_with / _ends_with and their i / n variants — the anchored LIKE 'BrandX%' hits the leading "
• _between / _nbetween on strings — " (0x22) sorts before every letter
• any dot-path or array-index path resolving to a string scalar
• relational _json filters on string fields
• json() in fields and sort, which returns the quoted string to the client

Unaffected

• all numeric operators — they take the separate JSON_VALUE(... RETURNING NUMBER) branch
• _contains / _ncontains / _icontains — the unanchored LIKE '%Brand%' still matches inside the quotes
• _null / _nnull — null-ness is unchanged
• object- and array-valued paths — those genuinely need JSON_QUERY

The unanchored-LIKE case is worth calling out: _contains passing while _eq fails on the same field makes this look like an operator-specific bug rather than an extraction bug.

To Reproduce

Run the endpoints/query/json e2e suite against Oracle:

pnpm vitest --project oracle run tests/endpoints/query/json

Result on main:

66 failures out of 151. Every failure is a wrong row count or a quoted string, never an error.

Minimal standalone reproduction:

CREATE TABLE json_repro ("id" NUMBER, "meta" VARCHAR2(4000));
INSERT INTO json_repro VALUES (1, '{"color":"red","brand":"BrandX"}');

-- returns 0 rows
SELECT count(*) FROM json_repro
WHERE COALESCE(JSON_QUERY("meta",'$.color'), JSON_VALUE("meta",'$.color')) = 'red';

-- returns 1 row
SELECT count(*) FROM json_repro
WHERE JSON_VALUE("meta",'$.color') = 'red';

Suggested fix

Swap the COALESCE arguments. JSON_VALUE does return NULL for objects and arrays, so putting it first makes the fallback work in the direction the comment intended:

COALESCE(JSON_VALUE(??.??, '$.path'), JSON_QUERY(??.??, '$.path'))

Verified: with only this change, all four files pass — 151/151, up from 85/151. Behaviour for objects, arrays, missing keys and JSON null is unchanged.

Two follow-ups that go with it:

• The comment above the expression states the wrong assumption and should be corrected.
• oracle.test.ts asserts /COALESCE\(JSON_QUERY/ and will need updating.

Directus Version

main ([email protected], @directus/[email protected])

Hosting Strategy

Self-Hosted (Custom)

Database

Oracle Database 23ai Free (gvenzl/oracle-free:23-slim-faststart)