JSON Data Structures in PL/SQL @ DanBrother的部落格

JSON_ELEMENT_T: the supertype for the JSON_OBJECT_T, JSON_ARRAY_T, JSON_SCALAR_T and JSON_KEY_LIST object types.
Each of them extends JSON_ELEMENT_T as a subtype.
An instance of type JSON_ELEMENT_T, which is an in-memory representation of the JSON data, can be constructed only by parsing JSON text.
An instance of JSON_ELEMENT_T can be casted to a subtype instance using PL/SQL function - treat.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
my_je JSON_ELEMENT_T;
my_jo JSON_OBJECT_T;
BEGIN
my_je := JSON_ELEMENT_T.parse('{"name":"Toy Car",
                                        "price":20
                                        }');
IF (my_je.is_Object) THEN
     -- cast JSON element into JSON object
     my_jo := treat(my_je AS JSON_OBJECT_T);
     -- add the object field and set the value of it
    my_jo.put('color', 'blue');
END IF;
  -- serialize the JSON element to be printed out (or stored in a table)
-- (method to_string() returns a string (VARCHAR2) representation of
-- the JSON object-type instance
DBMS_OUTPUT.put_line(my_je.to_string);
END;
/
{"name":"Toy Car","price":20,"color":"blue"}

select json_array(

Employee_id,first_name,last_name,job_id

) as json_array

from employees

where employee_id in (101,102);

>>
JSON_ARRAY
--------------------------------
[101,"Neena","Kochhar","AD_VP"]
[102,"Lex","De Haan","AD_VP"]

Select json_object(
'Employee ID' is employee_id,
'First Name' is first_name,
'Last Name' is last_name,
'Job ID' is job_id
) as json_object from employees
where employee_id in (101,102);

>>
JSON_OBJECT
--------------------------------------------------------------------------------
{"Employee ID":101,"First Name":"Neena","Last Name":"Kochhar","Job ID":"AD_VP"}
{"Employee ID":102,"First Name":"Lex","Last Name":"De Haan","Job ID":"AD_VP"}

select json_object(
'First Name' is a.first_name,
'Job ID' is a.job_id,
'Job Histroy' is (select
     json_arrayagg(
        json_object(
           'Start Date' is to_char(b.start_date,'yyyy-mm-dd'),
           'End Date' is to_char(b.end_date,'yyyy-mm-dd')
         )
    )
   from job_history b
   where a.employee_id = b.employee_id
)
) as employee_with_job_history
from employees a
where a.employee_id in (101,102);

col department_name format a20
select department_name,department_id
from departments
where department_id between 10 and 30;
>>
DEPARTMENT_NAME      DEPARTMENT_ID
-------------------- -------------
Administration                  10
Marketing                       20
Purchasing                      30

SELECT JSON_OBJECTAGG(KEY department_name VALUE department_id) "Department Numbers"
FROM departments
WHERE department_id between 10 and 30;
>>
Department Numbers
----------------------------------------------------
{"Administration":10,"Marketing":20,"Purchasing":30}
SELECT JSON_OBJECTAGG(KEY department_name VALUE department_id) "Department Numbers"
FROM departments
WHERE department_id between 10 and 30
group by department_id;
>>
Department Numbers
----------------------
{"Administration":10}
{"Marketing":20}
{"Purchasing":30}