JSON_QUERY: Path expression based on variables?

Question

is it possible to define the path expression in JSON_QUERY dynamically based on variables?

DECLARE
    varpath varchar(180):= 'testvar1.testattribute';
BEGIN
    SELECT (JSON_QUERY(json_field FORMAT JSON , '$.' || varpath RETURNING VARCHAR2(4000) ASIS  WITHOUT ARRAY WRAPPER NULL ON ERROR)) FROM example1; 
END;

The concatenation of '$.' || varpath leads to the error:

PL/SQL: ORA-40454: path expression not a literal.

Is there any way to achieve this? In my use case, I need to fetch the path dynamically from other tables, that's why I built this short test script.

Thanks in advance!

Aaron · Accepted Answer · 2017-02-07 20:22:31Z

4

You can use EXECUTE IMMEDIATE to solve it.

DECLARE
    varpath varchar2(180):= 'testvar1.testattribute';
    v_query_str varchar2(4000);
    v_result varchar2(4000);
BEGIN
    v_query_str := 'SELECT JSON_QUERY(json_field FORMAT JSON , ''$.' || varpath || ''' RETURNING VARCHAR2(4000) ASIS  WITHOUT ARRAY WRAPPER NULL ON ERROR) FROM example1'; 
    EXECUTE IMMEDIATE v_query_str INTO v_result;
END;

answered Feb 7, 2017 at 20:22

Aaron

4346 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jackthehipster Over a year ago

Holy cow. Oracle really knows how to confuse and annoy programmers. But this does in fact work for me.

Collectives™ on Stack Overflow

JSON_QUERY: Path expression based on variables?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related