DROP TABLE json_species

CREATE TABLE json_species
(
   id     NUMBER NOT NULL PRIMARY KEY,
   info   CLOB CONSTRAINT is_json CHECK (info IS JSON ) )

BEGIN
   INSERT INTO json_species
        VALUES (1, '{"name":"Spider"}');

   INSERT INTO json_species
        VALUES (2, '{"name":"Elephant", "trunk_length":"10"}');

   INSERT INTO json_species
        VALUES (3, '{"name":"Shark", "fin_count":"4"}');

   COMMIT;
END;

SELECT * FROM json_species

SELECT sp.info.name
  FROM json_species sp

DECLARE
   CURSOR species_cur
   IS
      SELECT id, sp.info
        FROM json_species sp;

   l_info              json_element_t;
   l_document_object   json_object_t;
   l_name              json_element_t;
BEGIN
   FOR rec IN species_cur
   LOOP
      l_info := json_element_t.parse (rec.info);
      l_document_object := TREAT (l_info AS json_object_t);
      l_name := l_document_object.get ('name');

      dbms_output.put_line (l_name.to_string);
   end loop;
END;

BEGIN 
   FOR rec IN (SELECT sp.info FROM json_species sp 
                ORDER BY sp.info.name) 
   LOOP 
      dbms_output.put_line ( 
         json_object_t (rec.info).get ('name').to_string); 
   END LOOP; 
END;

DECLARE
   CURSOR species_cur
   IS
      SELECT sp.id, sp.info
        FROM json_species sp
      FOR UPDATE;

   l_species              json_object_t;
   l_species_for_update   json_species.info%TYPE;
   l_current_name         VARCHAR2 (1000);
   l_new_name             VARCHAR2 (1000);
BEGIN
   FOR rec IN species_cur
   LOOP
      l_species := json_object_t (rec.info);

      l_current_name := l_species.get ('name').to_string;

      l_new_name := TRIM (BOTH '"' FROM UPPER (l_current_name));

      l_species.put ('name', l_new_name);

      l_species_for_update := l_species.stringify;

      UPDATE json_species
         SET info = l_species_for_update
       WHERE CURRENT OF species_cur;
   END LOOP;
END;

DECLARE
   CURSOR species_cur
   IS
      SELECT id, sp.info
        FROM json_species sp
      FOR UPDATE;

   l_species              json_object_t;
   l_species_for_update   json_species.info%TYPE;
BEGIN
   FOR rec IN species_cur
   LOOP
      l_species := json_object_t (rec.info);

      l_species.put (
         'name',
         TRIM (BOTH '"' FROM UPPER (l_species.get ('name').to_string)));

      l_species_for_update := l_species.stringify;

      UPDATE json_species
         SET info = l_species_for_update
       WHERE CURRENT OF species_cur;
   END LOOP;
END;

BEGIN
   INSERT INTO json_species
        VALUES (10, '{"name":"Ostrich", 
                      "favorite_eats":
                        ["Stirfry", "Yogurt", "Mosquitos"]}');
   COMMIT;
END;

DECLARE
   l_ostrich   json_object_t;
   l_eats      json_array_t;
BEGIN
   l_ostrich :=
      json_object_t (
         '{"name":"Ostrich", 
                      "favorite_eats":
                        ["Stirfry", "Yogurt", "Mosquitos"]}');
   l_eats := TREAT (l_ostrich.get ('favorite_eats') AS json_array_t);

   DBMS_OUTPUT.put_line ('# of eats = ' || l_eats.get_size);
   
   IF l_ostrich.get ('favorite_eats').is_array ()
   THEN
      DBMS_OUTPUT.put_line ('favorite eats is an array');
   END IF;

   IF NOT l_ostrich.get ('name').is_array ()
   THEN
      DBMS_OUTPUT.put_line ('name is NOT an array');
   END IF;
   
   l_eats.APPEND ('Truffles');
   DBMS_OUTPUT.put_line (l_eats.stringify());
   
   l_eats.APPEND_NULL;
   DBMS_OUTPUT.put_line (l_eats.stringify());
   
   /* Add Ice Cream before Yogurt.
      Remember: arrays start at 0 */
   l_eats.put (1, 'Ice Cream');
   DBMS_OUTPUT.put_line (l_eats.stringify());
   
   /* Add a null value before Ice Cream */
   l_eats.put_null (1);
   DBMS_OUTPUT.put_line (l_eats.stringify());
   
   /* Replace that null with Broccoli */
   l_eats.REMOVE (1);
   l_eats.put (1, 'Broccoli');
   DBMS_OUTPUT.put_line (l_eats.stringify());
END;

DECLARE
   l_fav   json_object_t;
   l_num   NUMBER;
BEGIN
   l_fav := json_object_t ('{"favorite_flavor":"chocolate"}');
   
   /* The default */
   l_fav.on_error (0);
   
   l_num := l_fav.get_number ('favorite_flavor');
   
   DBMS_OUTPUT.put_line ('Number = ' || l_num);
END;

DECLARE
   l_fav   json_object_t;
   l_num   NUMBER;
BEGIN
   l_fav := json_object_t ('{"favorite_flavor":"chocolate"}');
   
   /* Raise all errors */
   l_fav.on_error (1);
   
   l_num := l_fav.get_number ('favorite_flavor');
END;

DECLARE
   l_eats      json_array_t;
BEGIN
   l_eats := JSON_ARRAY_T ('["Stirfry", "Yogurt", "Mosquitos"]');
   
   /* Raise all errors */
   l_eats.on_error (4);
   
   l_eats.put (-1, 'Ice Cream');
   
   DBMS_OUTPUT.PUT_LINE (l_eats.get_size());
END;