Re: Add SQL/JSON ON MISMATCH clause to JSON_VALUE

On Sun, Jan 25, 2026 at 3:15 AM Florents Tselai <florents(dot)tselai(at)gmail(dot)com>
wrote:

>
>
>>
>> I've had trouble making JSON_QUERY work too,
>> so I suspect that although my tests pass,
>> there may be something I'm missing in the executor side of things.
>>
>
> "For those curious, I found the issue: in ExecEvalJsonExprPath, I
> had misplaced the check for jsexpr->on_mismatch.
> I wasn't setting jsestate->escontext.details_wanted = true early enough.
> Without this, the soft error context wasn't capturing the specific SQL
> error code needed to identify the mismatch.
>
> Attaching a v2 that implements ON MISMATCH for JSON_QUERY and JSON_TABLE
> too.
>
> That said, the semantics of ON ERROR / MISMATCH / EMPTY are complex.
> I expect someone with access to and detailed knowledge of the standard
> might be able to poke holes in certain edge cases, particularly regarding
> precedence.
>

2 amendments to the commit message:

- This is isn't in the SQL/JSON standard

But aligns with the semantics of Oracle that already provides such a clause
[0]:

- A TYPE ERROR is defined as "A JSON scalar value has a data type that is
incompatible with the corresponding return SQL scalar data type."
- Precedence logic: ON MISMATCH applies only when neither of the clauses ON
EMPTY and ON ERROR applies.
- Fallback Behavior: if the user doesn't specify ON MISMATCH, the coercion
error is caught by ON ERROR.

[0]
https://docs.oracle.com/en/database/oracle/oracle-database/26/adjsn/mismatch-clause-sql-json-query-functions.html#GUID-21FE887D-5D7B-43BB-A1A8-B26B8924B290