How to use Oracle's JSON_VALUE function with a PreparedStatement

Question

I am trying to run a SQL query using Oracle's json_value() function using a PreparedStatement.

Assume the following table setup:

drop table foo cascade constraints purge;
create table foo
(
  id integer primary key, 
  payload clob, 
  constraint ensure_json check (payload IS JSON STRICT)
);

insert into foo values (1, '{"data": {"k1": 1, "k2": "foo"}}');

The following SQL query works fine:

select *
from foo
where json_value(payload, '$.data.k1') = '1'

and returns the expected row.

However, when I try to run this query using a PreparedStatement like in the the following piece of code:

String sql =
     "select *\n" +
     "from foo\n" +
     "where json_value(payload, ?) = ?";

PreparedStatement pstmt = conection.prepareStatement(sql);
pstmt.setString(1, "$.data.k1");
pstmt.setString(2, "1");
ResultSet rs = pstmt.executeQuery();

^{(I removed all error checking from the example to keep it simple)}

This results in:

java.sql.SQLException: ORA-40454: path expression not a literal

The culprit is passing the json path value (parameter index 1), the second parameter is no problem.

When I replace (only) the first parameter with a String constant json_value(payload, '$.data.k1') = ? the prepared statement works fine.

In a desperate attempt, I also tried including the single quotes in the parameter: pstmt.setString(1, "'$.data.k1'") but not surprisingly, Oracle wouldn't accept it either (same error message).

I also tried using json_value(payload, concat('$.', ?) ) and only passing "data.k1" as the parameter - same result.

So, the question is:

• How can I pass a JSON path expression to Oracle's json_value function using a PreparedStatement parameter?

Any ideas? Is this a bug in the driver or in Oracle? (I couldn't find anything on My Oracle Support)

Or is this simply a case of "not implemented"?

---

Environment:

I am using Oracle 18.0
I tried the 18.3 and 19.3 version of the ojdbc10.jar driver together with OpenJDK 11.

Answer 1

It isn't the driver - you get the same thing with dynamic SQL:

declare
  result foo%rowtype;
begin
  execute immediate 'select *
    from foo
    where json_value(payload, :1) = :2'
  into result using '$.data.k1', '1';
  dbms_output.put_line(result.payload);
end;
/

ORA-40454: path expression not a literal
ORA-06512: at line 4

And it isn't really a bug, it's documented (emphasis added):

JSON_basic_path_expression

Use this clause to specify a SQL/JSON path expression. The function uses the path expression to evaluate expr and find a scalar JSON value that matches, or satisfies, the path expression. The path expression must be a text literal. See Oracle Database JSON Developer's Guide for the full semantics of JSON_basic_path_expression.

So you would have to embed the path literal, rather than bind it, unfortunately:

declare
  result foo%rowtype;
begin
  execute immediate 'select *
    from foo
    where json_value(payload, ''' || '$.data.k1' || ''') = :1'
  into result using '1';
  dbms_output.put_line(result.payload);
end;
/

1 rows affected

dbms_output:
{"data": {"k1": 1, "k2": "foo"}}

or for your JDBC example (keeping the path as a separate string as you presumably want that to be a variable really):

String sql =
     "select *\n" +
     "from foo\n" +
     "where json_value(payload, '" + "$.data.k1" + "') = ?";

PreparedStatement pstmt = conection.prepareStatement(sql);
pstmt.setString(1, "1");
ResultSet rs = pstmt.executeQuery();

Which obviously isn't what you want to do*, but there doesn't seem to be an alternative. Other than turning your query into a function and passing the path variable in to that, but then the function would have to use dynamic SQL, so the effect is much the same - maybe easier to handle SQL injection concerns that way though.

* and I'm aware you know how to do this the embedded way, and know you want to use bind variables because that's the correct thing to do; I've spelled it out more than you need for other visitors *8-)