CREATE OR REPLACE PACKAGE to_json
   AUTHID DEFINER
IS
   SUBTYPE index_t IS VARCHAR2 (50);

   TYPE assoc_array_t IS TABLE OF VARCHAR2 (100)
      INDEX BY index_t;

   FUNCTION to_object (key_in IN VARCHAR2, value_in IN VARCHAR2)
      RETURN json_object_t;

   FUNCTION to_object (key_in IN VARCHAR2, value_in IN NUMBER)
      RETURN json_object_t;

   FUNCTION to_object (key_in IN VARCHAR2, value_in IN BOOLEAN)
      RETURN json_object_t;

   FUNCTION to_object (key_in IN VARCHAR2, value_in IN DATE)
      RETURN json_object_t;

   FUNCTION to_array (assoc_array_in IN assoc_array_t)
      RETURN json_array_t;
END;

CREATE OR REPLACE PACKAGE BODY to_json
IS
   FUNCTION to_object (key_in IN VARCHAR2, value_in IN VARCHAR2)
      RETURN json_object_t
   IS
   BEGIN
      RETURN json_object_t ('{"' || key_in || '":"' || value_in || '"}');
   END;

   FUNCTION to_object (key_in IN VARCHAR2, value_in IN NUMBER)
      RETURN json_object_t
   IS
   BEGIN
      /* No double quotes around a JSON numeric literal */
      RETURN json_object_t (
                '{"' || key_in || '":' || TO_CHAR (value_in) || '}');
   END;

   FUNCTION to_object (key_in IN VARCHAR2, value_in IN BOOLEAN)
      RETURN json_object_t
   IS
   BEGIN
      /* No double quotes around a JSON boolean literal */
      RETURN json_object_t (
                   '{"'
                || key_in
                || '":'
                || CASE WHEN value_in THEN 'true' ELSE 'false' END
                || '}');
   END;

   FUNCTION to_object (key_in IN VARCHAR2, value_in IN DATE)
      RETURN json_object_t
   IS
   BEGIN
      /* Not finished */
      RETURN json_object_t (
                '{"' || key_in || '":"' || TO_CHAR (value_in) || '"}');
   END;

   FUNCTION to_array (assoc_array_in IN assoc_array_t)
      RETURN json_array_t
   IS
      l_index        index_t := assoc_array_in.FIRST;
      l_json_array   json_array_t := json_array_t ();
   BEGIN
      WHILE l_index IS NOT NULL
      LOOP
         DBMS_OUTPUT.put_line (
            'Appending ' || l_index || ':' || assoc_array_in (l_index));
            
         l_json_array.append (to_object (l_index, assoc_array_in (l_index)));
         
         DBMS_OUTPUT.put_line ('Watch it grow! ' || l_json_array.get_size ());
         
         l_index := assoc_array_in.NEXT (l_index);
      END LOOP;

      RETURN l_json_array;
   END;
END;

DECLARE
   l_object   json_object_t;
BEGIN
   l_object := to_json.to_object ('a_number', 1);
   DBMS_OUTPUT.put_line (l_object.stringify ());
   
   l_object := to_json.to_object ('a_boolean', TRUE);
   DBMS_OUTPUT.put_line (l_object.stringify ());
   
   l_object := to_json.to_object ('a_string', 'abc');
   DBMS_OUTPUT.put_line (l_object.stringify ());
END;

DECLARE
   l_array to_json.assoc_array_t := 
      to_json.assoc_array_t (
         'yes' => 'you', 'can'=>'in', 'oracledatabase'=>'18c', 
         'fullstop'=>NULL, 'and then'=>'some');
BEGIN
   /* Notice they are shown in alphabetical order. That's due to the
      automatic ordering by character set order when values are put
      into a string-indexed collection */
   DBMS_OUTPUT.put_line (to_json.to_array (l_array).to_string ());
END;

DECLARE
   l_array to_json.assoc_array_t := 
      to_json.assoc_array_t (
         '1yes' => 'you', '2can'=>'in', '3oracledatabase'=>'18c');
BEGIN
   DBMS_OUTPUT.put_line (to_json.to_array (l_array).to_string ());
END;