Oracle Live SQL - Script: JSON and plsql

Statement 1

Simple Example for a procedure that takes a JSON string, parses it as an object and adds a key/value pair.

create or replace procedure foo (j varchar2) is
  o json_object_t;
begin
  o := json_object_t(j);
  o.put('newKey', 'newValue');
  dbms_output.put_line(o.to_string);
end;

Statement 2

exec foo('{}')

Statement 3

exec foo('{a:1}')

Statement 4

This fails because [] cannot be parsed as an object.

exec foo('[]')

Statement 5

We can create a similar functions just for arrays (but that would also just be specific to one type).

create or replace procedure foo (j varchar2) is
  a json_array_t;
begin
  a := json_array_t(j);
  a.append('newValue');
  dbms_output.put_line(a.to_string);
end;

Statement 6

exec foo('[]')

Statement 7

A JSON value can be an object, an array or a scalar value. Think of a .get() operation on an object: it's value is one of the above three. We need a type to capture this - a super-type - which is called JSON_ELEMENT_T. JSON_ELEMENT_T is static, it cannot be directly instantiated, instead it offers a static method called parse().

create or replace procedure bar (j varchar2) is
  e json_element_t;
begin
  e := json_element_t.parse(j);
  dbms_output.put_line(e.to_string);
  if (e.is_object) then
    dbms_output.put_line('I am an object');
  elsif (e.is_array) then
    dbms_output.put_line('I am an array');
  end if;
end;

Statement 8

exec bar('{a:1}')

Statement 9

exec bar('[1,2,3]')

Statement 10

scalar value are not supported as top-level JSON values; therefore the parse method rejects them. But scalar values can be returned when extracting values from an object/array using the get() method.

exec bar('x')

Statement 11

using TREAT a JSON_ELEMENT_T instance can be down-casted to the corresponding sub-type.

create or replace procedure value_info (e json_element_t) is
 o json_object_t;
 a json_array_t;
 s json_scalar_t;
begin
  if (e.is_object) then
    o := TREAT (e as json_object_t);
  elsif (e.is_array) then
    a := TREAT (e as json_array_t);
  else 
    s := TREAT (e as json_scalar_t);
 end if;
end;

Statement 12

This procedure iterates over an array and prints the items with their position.

create or replace procedure array_info (a json_array_t) is
  e json_element_t;
begin
  for i in 0 .. (a.get_size - 1) loop
    e := a.get(i);
    dbms_output.put_line('Array position ' || i || ' has value ' || e.to_string);
  end loop;
end;

Statement 13

This procedure outputs some information about scalar values.

create or replace procedure scalar_info (s json_scalar_t) is
  e json_element_t;
begin
  if (s.is_number) then
     dbms_output.put_line('Scalar of type number with value ' || s.to_number);
  elsif (s.is_string) then
     dbms_output.put_line('Scalar of type string with value ' || s.to_string);
  elsif (s.is_boolean) then
     dbms_output.put_line('Scalar of type boolean with value ' || s.to_string);
  end if;
end;

Statement 14

This procedure accepts an object as input. It first retrieves all used key names in the object. It then iterates over the key names and prints them with the corresponding value.

create or replace procedure object_info (o json_object_t) is
  e json_element_t;
  keys json_key_list;
  key  varchar2(256);
begin
  keys := o.get_keys;
  for i in 1 .. keys.count loop
    key := keys(i);
    e := o.get(key);
    dbms_output.put_line('key ' || key || ' has value ' || e.to_string);
  end loop;
end;

Statement 15

Procedure to parse a string to a JSON_ELEMEN_T and then call the right sub-procedure.

create or replace procedure json_info (j varchar2) is
 e json_element_t;
begin
  e := JSON_ELEMENT_T.parse(j);
  if (e.is_object) then
    object_info(TREAT (e as json_object_t));
  elsif (e.is_array) then
    array_info(TREAT (e as json_array_t));
  else 
    scalar_info(TREAT (e as json_scalar_t));
 end if;
end;

Statement 16

exec json_info('[1,2,{a:1}, true]')

Statement 17

exec json_info('{a:1, b:false}')

Statement 18

Here we create an empty object and array and show how values can be added to it. We also show how the object and the array can be inserted into each other.

declare 
  o json_object_t;
  a json_array_t;
  b boolean;
begin
  o := new json_object_t;
  a := new json_array_t;
  
  o.put('key', 'value1');
  o.put('key', 'value2');
  dbms_output.put_line(o.to_string);
  
  a.append(true);
  a.append('true');
  a.append(123);
  a.put(2, 456);
  dbms_output.put_line(a.to_string);
  
  a.append(o);
  dbms_output.put_line(a.to_string);
  
  o.put('nestedArr', a);
  dbms_output.put_line(o.to_string);
end;

Statement 19

The pl/sql type are able to handle data types beyond the normal JSON scalar types. For instance DATE and TIMESTAMP are supported as scalar values. When serializing such value then the ISO 8601 representation is chosen when converting the DATE/TIMESTAMP to a string. Similarly such string value can be read as a DATE/TIMESTAMP value using a get() method call.

declare
  o json_object_t;
  d date;
  e json_element_t;
begin
  o := new json_object_t;
  o.put('date', SYSDATE);
  dbms_output.put_line(o.to_string);
  d := o.get_date('date');
  dbms_output.put_line(d);
  
  o.put('date_as_string', '2019-07-09T15:18:41');
  e := o.get('date_as_string');
  dbms_output.put_line('ts:' || e.to_timestamp);
  
  o.put('date_as_string', '2019-07-09T15:18:41_XXXX');
  e := o.get('date_as_string');
  dbms_output.put_line('ts:' || e.to_timestamp);
end;

Statement 20

It is possible to change the default on error behavior to raise error instead of returning null.

declare
  o json_object_t;
  d date;
  e json_element_t;
begin
  o := new json_object_t;
  o.put('date_as_string', '2019-07-09T15:18:41_XXXX');
  e := o.get('date_as_string');
  e.on_error(1);
  dbms_output.put_line('ts:' || e.to_timestamp);
end;

Statement 21

When extracting a nested object or array (using get/get_object/get_array methods) then a 'reference' to the nested object is being returned - not a copy. If you modify the nested object/array then the containing object/array will see these changes.

declare
  customer json_object_t;
  address  json_object_t;
begin
  customer := json_object_t('{name:"Tom", address:{city:"Paris"}}');
  address  := customer.get_object('address');
 
  dbms_output.put_line(address.to_string);
  dbms_output.put_line(customer.to_string);
 
  address.put('city', 'Marseille');
  dbms_output.put_line(address.to_string);
  dbms_output.put_line(customer.to_string);
end;

Statement 22

When a copy is required then just call the clone() method.

declare
  customer json_object_t;
  address  json_object_t;
begin
  customer := json_object_t('{name:"Tom", address:{city:"Paris"}}');
  address  := customer.get_object('address').clone;
 
  dbms_output.put_line(address.to_string);
  dbms_output.put_line(customer.to_string);
 
  address.put('city', 'Marseille');
  dbms_output.put_line(address.to_string);
  dbms_output.put_line(customer.to_string);
end;

Statement 23

Reference semantics does not apply for 'put' calls. Here the value is copied into the containing object/array. Otherwise, one value could belong to more than one container.

declare
  customer json_object_t;
  address  json_object_t;
begin
  customer := json_object_t('{name:"Tom"}');
  address  := json_object_t('{city:"Paris"}');
 
  customer.put('address', address);
  dbms_output.put_line(customer.to_string);
 
  address.put('city', 'Marseille');
  dbms_output.put_line(address.to_string);
  dbms_output.put_line(customer.to_string);
end;

Statement 24

the plsql object types for JSON are transient types. One cannot create a table with them as column type. Nor is it possible to use them as types for bind variables to a programming languages. The serialization format of these types is JSON.

create table t (col json_object_t)

Statement 25

- no free() calls needed. The memory usage is internally tracked by the use of durations. If an object is passed to/from a pl/sql function/procedure we attach it to all relevant durations. if the last duration ends we free the object. - Larger JSONs can be returned as CLOBs: see the to_clob method. - documentation: https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/json-types.html#GUID-BDE10AAA-445B-47B5-8A39-D86C8EA99283

select 'comments' from dual;

JSON and plsql

Additional Information