CODEWITHSUNDEEP
jsonoracle-database
derpyderp
derpyderp

Reputation: 343

Extracting entire JSON element sub elements

I have this bit of code here...

set serveroutput on;
DECLARE
    v_response varchar(3000) := '{"content":{"stuff":{"cat":"meow","dog":"woof"}},"http_code":401,"response_code":"-1"}';
    v_content varchar(3000);
BEGIN
    select json_value(v_response, '$.content') into v_content from dual;
    dbms_output.put_line('v_content: ' || v_content);
END;

I would expect the variable v_content to contain something along the lines of '{"stuff":{"cat":"meow","dog":"woof"}'. However it is returning nothing.

Upvotes: 0

Views: 173

Answers (1)

Evelyn Ma
Evelyn Ma

Reputation: 494

JSON_VALUE finds a specified scalar JSON value in JSON data and returns it as a SQL value. 

select json_value('{"content":{"stuff":{"cat":"meow","dog":"woof"}},"http_code":401,"response_code":"-1"}', '$.content.stuff.cat') from dual

returned meow

Try this:

DECLARE 
  je JSON_ELEMENT_T;
  jo JSON_OBJECT_T;
  content JSON_OBJECT_T;
  v_response varchar(3000) := '{"content":{"stuff":{"cat":"meow","dog":"woof"}},"http_code":401,"response_code":"-1"}';
BEGIN
  je := JSON_ELEMENT_T.parse(v_response);
  IF (je.is_Object) THEN
      jo := treat(je AS JSON_OBJECT_T);
      content := jo.get_Object('content');
  END IF;
  DBMS_OUTPUT.put_line(content.to_string);
END;

Upvotes: 1

Related Questions