PLSQL JSON Parsing With Dataguides 2

The previous post demonstrated using a dataguide to parse scalar values. This post demonstrates using a dataguide to parse a simple JSON array.

SQL> -- put a simple numeric array into the demo table
SQL> INSERT INTO json_data
 2   VALUES(1,
 3          '{"KEY1":[0,1,2,3]}');
 1 row created.

Applying the parse function from the previous post reveals this result.

SQL> SELECT json_col,
 2          parser(json_col) pj
 3     FROM json_data;
 JSON_COL
 -------------------------------------------------
 PJ
 -------------------------------------------------
 {"KEY1":[0,1,2,3]}
 {"o:path":"$.KEY1","type":"array","o:length":16}

The important fields in the dataguide entry for the array are:

  • o:path is the path starting from the top of the JSON structure.
  • type is arrray

Next we’ll add code to the parser function to extract the dataguide elements.

-- {"o:path":"$.KEY1","type":"array","o:length":16}
DECLARE

  v_path CLOB;
  v_type VARCHAR2(30);

BEGIN

  -- extract the element path
  v_path := JSON_VALUE(v_element.to_string,'$."o:path"'
             ERROR ON ERROR);

  -- extract the element type
  v_type := JSON_VALUE(v_element.to_string,'$.type'
             ERROR ON ERROR);

  IF v_type = 'array' THEN
    DBMS_OUTPUT.PUT_LINE(v_type);
    DBMS_OUTPUT.PUT_LINE(v_path);
  END IF;

END;

And here is what that displays when the parser is run.

array
$.KEY1

Now we know the name of the array element so we just need to add parsing.  Let’s jump out to SQL*Plus to work through the parsing algorithm.

-- Lets try with a call to JSON_VALUE
-- Returns null -- WTH?
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1') );
END;
PL/SQL procedure successfully completed.

-- Lets add ERROR ON ERROR to see what went wrong
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1'
                        ERROR ON ERROR) );
END;
BEGIN
*
ERROR at line 1:
ORA-40456: JSON_VALUE evaluated to non-scalar value
ORA-06512: at line 2

-- so its not a scalar - boo hoo.
-- give me all the values with a * then...
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[*]'
                        ERROR ON ERROR) );
END;
BEGIN
*
ERROR at line 1:
ORA-40470: JSON_VALUE evaluated to multiple values
ORA-06512: at line 2

-- ok give me the values one at a time if you must...
-- ... but wait - how do i know when I'm done if there
-- is more or less than 4 values?
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[0]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[1]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[2]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[3]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[4]'
                        ERROR ON ERROR) );
END;
0
1
2
3
BEGIN
*
ERROR at line 1:
ORA-40462: JSON_VALUE evaluated to no value
ORA-06512: at line 14

-- I can just trap the exception
-- ORA-40462 equates to I AM DONE!
-- please forgive me for not defining an exception
-- just now I'll do that at the end...
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[0]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[1]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[2]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[3]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[4]'
                        ERROR ON ERROR) );
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -40462 THEN
      NULL;
    ELSE
      RAISE;
    END IF;
END;
0
1
2
3
PL/SQL procedure successfully completed.
 
-- That needs to be in a loop because the number of
-- entries is not known. Here goes...
DECLARE
  v_counter NUMBER := 0;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                     '$.KEY1[v_counter]'
                          ERROR ON ERROR) );
    v_counter := v_counter + 1;
   END LOOP;
END;
DECLARE
*
ERROR at line 1:
ORA-40457: JSON path expression contained an invalid
array index
('$.KEY1[v_counter]')
ORA-06512: at line 5

-- Thats actually good news - because it shows everything
-- is fine syntactically -- except for the darned pointer 
-- It turns out we can just concatenate it all together
-- like this:
DECLARE
  v_counter NUMBER := 0;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                           '$.KEY1[' || v_counter || ']'
                          ERROR ON ERROR) );
    v_counter := v_counter + 1;
  END LOOP;
END;
0
1
2
3
DECLARE
*
ERROR at line 1:
ORA-40462: JSON_VALUE evaluated to no value
ORA-06512: at line 5

-- Now handle the exception and we have some rock
-- solid code!
DECLARE
  v_counter NUMBER := 0;
  e_we_are_done EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_we_are_done,-40462);
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                             '$.KEY1[' || v_counter || ']'
                          ERROR ON ERROR) );
    v_counter := v_counter + 1;
  END LOOP;
EXCEPTION
  WHEN e_we_are_done THEN
    DBMS_OUTPUT.PUT_LINE('We are done!');
  WHEN OTHERS THEN
    RAISE;
END;
0
1
2
3
We are done!
PL/SQL procedure successfully completed.

In the next post I’ll show how this can be rolled into the parse function.

Thanks for reading!