Using JSON_OBJECT_T to Manipulate JSON Data in PL/SQL

View All Scripts Login to Run Script

Script Name Using JSON_OBJECT_T to Manipulate JSON Data in PL/SQL
Description JSON_OBJECT_T was added to Oracle Database 12c Release 2, as part of a suite of object types/APIs to allow native manipulation of JSON data in PL/SQL. JSON_OBJECT_T is for use with JSON objects. This code was provided by Darryl Hurley, as part of the ODTUG CodeTalk series.
Area PL/SQL General
Contributor Steven Feuerstein
Created Tuesday February 20, 2018

Statement 1

DECLARE 
   l_object   json_object_t; 
BEGIN 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   DBMS_OUTPUT.put_line (l_object.get_string ('Name')); 
END;

Elvis

Statement 2

DECLARE 
   l_object     json_object_t; 
   l_key_list   json_key_list; 
BEGIN 
   -- JSON_OBJECT can figure out what keys it has... 
   l_object := json_object_t.parse ( 
      '{"Name":"Elvis", 
        "Address":"Elsewhere",  
        "Numbers":[1,2,3,4,5], 
        "Stuff":[{"A":"B"},{"B":"C"}]}'); 
 
   l_key_list := l_object.get_keys; 
 
   FOR counter IN 1 .. l_key_list.COUNT 
   LOOP 
      DBMS_OUTPUT.put_line ( 
            l_key_list (counter) 
         || ' = ' 
         || l_object.get_string (l_key_list (counter))); 
   END LOOP; 
END;

Name = Elvis
Address = Elsewhere
Numbers = 
Stuff =

Statement 3

DECLARE 
   l_object   json_object_t; 
BEGIN 
   -- JSON_OBJECT_T stores JSON data of all types 
   -- must be key-value pairs though 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   l_object := json_object_t.parse ('{"Numbers":[1,2,3,4,5]}'); 
   l_object := json_object_t.parse ('{"Stuff":[{"A":"B"},{"B":"C"}]}'); 
END;

Statement processed.

Statement 4

DECLARE 
   l_object   json_object_t; 
BEGIN 
   -- The JSON must be valid though... 
   l_object := json_object_t.parse ('Not JSON At All'); 
END;

ORA-40441: JSON syntax error ORA-06512: at "SYS.JDOM_T", line 4
ORA-06512: at "SYS.JSON_OBJECT_T", line 86
ORA-06512: at line 5
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-40441

Statement 5

DECLARE 
   l_object   json_object_t; 
BEGIN 
   -- must be key-value pair; not just an alement 
   l_object := json_object_t.parse ('[0,1,2,3,4,5]'); 
END;

ORA-40587: invalid JSON type ORA-06512: at "SYS.JSON_OBJECT_T", line 90
ORA-06512: at line 5
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-40587

Statement 6

DECLARE 
   l_object   json_object_t; 
 
   PROCEDURE what_am_i (p_id NUMBER) 
   IS 
   BEGIN 
      DBMS_OUTPUT.put_line ('#' || p_id); 
      DBMS_OUTPUT.put_line ('Size ' || l_object.get_size); 
 
      IF l_object.is_object 
      THEN 
         DBMS_OUTPUT.put_line ('OBJECT'); 
      END IF; 
   END; 
BEGIN 
   -- It can interrogate itself... 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   what_am_i (1); 
   l_object := json_object_t.parse ('{"Numbers":[1,2,3,4,5]}'); 
   what_am_i (2); 
   l_object := json_object_t.parse ('{"Stuff":[{"A":"B"},{"B":"C"}]}'); 
   what_am_i (3); 
END;

#1
Size 1
OBJECT
#2
Size 1
OBJECT
#3
Size 1
OBJECT

Statement 7

DECLARE 
   l_object   json_object_t; 
 
   PROCEDURE output (p_key VARCHAR2) 
   IS 
   BEGIN 
      DBMS_OUTPUT.put_line (p_key); 
 
      -- if single value (scalar)... 
      IF l_object.get_type (p_key) = 'SCALAR' 
      THEN 
         IF l_object.is_string 
         THEN 
            DBMS_OUTPUT.put_line ('Elem = ' || l_object.to_string); 
         END IF; 
 
         DBMS_OUTPUT.put_line ('Obj  = ' || l_object.get_string (p_key)); 
      -- An array 
      ELSIF l_object.get_type (p_key) = 'ARRAY' 
      THEN 
         DBMS_OUTPUT.put_line ('Array!'); 
      ELSE                                                         -- not scalar 
         DBMS_OUTPUT.put_line ('Not A SCALAR or String'); 
      END IF;                                                         -- scalar? 
   END; 
BEGIN 
   -- Or the code can check... 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   output ('Name'); 
   l_object := json_object_t.parse ('{"Numbers":[1,2,3,4,5]}'); 
   output ('Numbers'); 
   l_object := json_object_t.parse ('{"Stuff":[{"A":"B"},{"B":"C"}]}'); 
   output ('Stuff'); 
END;

Name
Obj  = Elvis
Numbers
Array!
Stuff
Array!

Statement 8

DECLARE 
   l_object   json_object_t; 
 
   PROCEDURE process_it (p_key VARCHAR2) 
   IS 
      l_e   json_element_t; 
   BEGIN 
      -- elements can be extracted from JSON_OBJECT_T 
      l_e := l_object.get (p_key); 
      DBMS_OUTPUT.put_line (l_e.to_string); 
   END; 
BEGIN 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   process_it ('Name'); 
   l_object := json_object_t.parse ('{"Numbers":[1,2,3,4,5]}'); 
   process_it ('Numbers'); 
   l_object := json_object_t.parse ('{"Stuff":[{"A":"B"},{"B":"C"}]}'); 
   process_it ('Stuff'); 
END;

"Elvis"
[1,2,3,4,5]
[{"A":"B"},{"B":"C"}]

Statement 9

DECLARE 
   -- objects can be arrays 
   l_object   json_object_t; 
 
   PROCEDURE process_it (p_key VARCHAR2) 
   IS 
      l_e   json_element_t; 
      l_a   json_array_t; 
   BEGIN 
      l_e := l_object.get (p_key); 
 
      IF l_e.is_array 
      THEN 
         l_a := json_array_t.parse (l_e.to_string); 
         DBMS_OUTPUT.put_line ( 
            p_key || ' is an array of size ' || l_a.get_size); 
      ELSE 
         DBMS_OUTPUT.put_line (p_key || ' is not an array'); 
      END IF; 
   END; 
BEGIN 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   process_it ('Name'); 
   l_object := json_object_t.parse ('{"Numbers":[1,2,3,4,5]}'); 
   process_it ('Numbers'); 
   l_object := json_object_t.parse ('{"Stuff":[{"A":"B"},{"B":"C"}]}'); 
   process_it ('Stuff'); 
END;

Name is not an array
Numbers is an array of size 5
Stuff is an array of size 2

Statement 10

DECLARE 
   l_object   json_object_t; 
BEGIN 
   -- Elements can be added to an object... 
   -- ...conditionally... 
   -- ...overloaded... 
   -- ...and removed... 
   -- ...or renamed... 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   l_object.put ('Nickname', 'The King'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 
    
   -- PUT is an up-sert! 
    
   l_object.put ('Nickname', 'Not The King'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 
    
   l_object.put ('Age', 11); 
   DBMS_OUTPUT.put_line (l_object.to_string); 
 
   IF NOT l_object.has ('Age') 
   THEN 
      l_object.put ('Age', 22); 
   END IF; 
 
   l_object.remove ('Age'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 
    
   l_object.rename_key ('Nickname', 'RealityIs'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 
END;

{"Name":"Elvis","Nickname":"The King"}
{"Name":"Elvis","Nickname":"Not The King"}
{"Name":"Elvis","Nickname":"Not The King","Age":11}
{"Name":"Elvis","Nickname":"Not The King"}
{"Name":"Elvis","RealityIs":"Not The King"}

Statement 11

DECLARE 
   l_object   json_object_t; 
BEGIN 
   -- remove is forgiving 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   l_object.remove ('NotThere'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 
END;

{"Name":"Elvis"}

Statement 12

DECLARE 
   l_object   json_object_t; 
BEGIN 
   -- rename is not forgiving 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   l_object.rename_key ('NotThere', 'DudeItsNotThere'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 
END;

ORA-40571: JSON key name not found. ORA-06512: at "SYS.JDOM_T", line 158
ORA-06512: at "SYS.JSON_OBJECT_T", line 330
ORA-06512: at line 6
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-40571

Statement 13

DECLARE 
   l_object            json_object_t; 
   e_singing_off_key   EXCEPTION; 
   PRAGMA EXCEPTION_INIT (e_singing_off_key, -40571); 
BEGIN 
   -- plsql to the rescue! 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
 
   BEGIN 
      l_object.rename_key ('NotThere', 'DudeItsNotThere'); 
   EXCEPTION 
      WHEN e_singing_off_key 
      THEN 
         NULL; 
      WHEN OTHERS 
      THEN 
         RAISE; 
   END; 
 
   DBMS_OUTPUT.put_line (l_object.to_string); 
END;

{"Name":"Elvis"}

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database