Suppose there is a row in one of the table cells:

{"value":"{\"result\":\"SUCCESS\",\"orderId\":1424,\"employerId\":2342}","description":null}

Is there a way without parsing the whole construct to find the value there at some address? For example:

 declare my_str varchar2(200):='{"value":"{\"result\":\"SUCCESS\",\"orderId\":1424,\"employerId\":2342}","description":null}'; begin dbms_output.put_line(my_pkg.get_json_value(my_str, 'value;result')); end;

And at the exit get "SUCCESS".

I met only the code on the habr, which builds a temporary table for the entire row.

Version 11.0.6.1796.

  • Something with quotes and screening was too smart. SELECT json_value ('{"value": {"result": "SUCCESS", "orderId": 1424, "employerId": 2342} "," description ":" null "}', '$ .value.result' RETURNING VARCHAR2 (500)) FROM dual; - Okdel
  • The above option works with version 12.1.0.2. did not immediately notice your version. - Okdel
  • And what did not suit the decision on Habré? - 0xdb
  • The fact that too many actions to verify a single value. I did this, it seems to work (but the code formatting in the comments ._.) if v_order.execution_result is not null and v_order.execution_result <> 'null' then apex_json.parse (v_order.execution_result); apex_json.parse (apex_json.get_varchar2 ('value')); if (apex_json.get_varchar2 ('result')) <> 'SUCCESS' then v_fails_counter := v_fails_counter + 1; end if; if v_order.execution_result is not null and v_order.execution_result <> 'null' then apex_json.parse (v_order.execution_result); apex_json.parse (apex_json.get_varchar2 ('value')); if (apex_json.get_varchar2 ('result')) <> 'SUCCESS' then v_fails_counter := v_fails_counter + 1; end if; - Vitaly Jandulov

0