[FEATURE] missing JSON_VALUE function for Oracle

Grammar or Syntax Description

JOSN_VALUE clause is not supported yet

SQL Example

Simplified Query Example, focusing on the failing feature

SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER) AS value
FROM DUAL;

SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER ON ERROR) AS value
FROM DUAL;

Exception:

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
    at line 1, column 18.
Was expecting one of:
    "&"
    "::"
    "<<"
    ">>"
    "EXCEPT"
    "INTERSECT"
    "INTO"
    "MINUS"
    "UNION"
    "["
    "^"
    "|"
    <EOF>
    <ST_SEMICOLON>

Additional context

The used JSQLParser Version: 4.7_SNAPSHOT.
Oracle DB 12c
Links to the reference documentation: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_VALUE.html#GUID-C7F19D36-1E75-4CB2-AE67-ADFBAD23CBC2

The most recent documentation: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_VALUE.html#GUID-C7F19D36-1E75-4CB2-AE67-ADFBAD23CBC2

MySQL: https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#function_json-value
DB2: https://www.ibm.com/docs/en/db2/12.1.0?topic=functions-json-value
Postgres: https://www.postgresql.org/docs/current/functions-json.html#SQLJSON-QUERY-FUNCTIONS

@manticore-projects

@manticore-projects The reported JSON_VALUE/RETURNING gap appears resolved on master 6c726d88 following #2382. The first exact example now parses as a structured JsonFunction with type VALUE and returning type NUMBER:

SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER) AS value
FROM DUAL;

There is one qualification: the second example contains bare RETURNING NUMBER ON ERROR. Oracle's documentation requires an action: NULL ON ERROR, ERROR ON ERROR, or DEFAULT literal ON ERROR. Bare ON ERROR still fails, but is not a valid form under that grammar.

I separately tested all three valid forms (using DEFAULT 0), checked their AST behavior fields, and verified round-trips through both toString() and StatementDeParser. The original first example and these valid variants pass with default settings and unsupported-statement fallback disabled. Existing coverage includes JsonFunctionTest.testJsonValue, and the full Gradle check passed.

Could you consider closing this issue for the reported gap, unless there is another valid Oracle reproducer? This is not a claim that every Oracle JSON_VALUE option is supported.

Navigation Menu

Repository navigation

[FEATURE] missing JSON_VALUE function for Oracle #1825

Grammar or Syntax Description

SQL Example

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[FEATURE] missing JSON_VALUE function for Oracle #1825

Description

Grammar or Syntax Description

SQL Example

Additional context

Activity

manticore-projects commented on Jul 24, 2023

ANeumann82 commented on Oct 7, 2025

minleejae commented on Sep 7, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions