Oracle Live SQL - Script: JSON and Object Type mappers

Statement 1

CREATE OR REPLACE TYPE Address_T AS OBJECT (
  street   VARCHAR2(50),
  streetNo NUMBER,
  city     VARCHAR2(50),
  zip     VARCHAR2(5)
);

Statement 2

Unfortunately LiveSQL cannot output object type instances directly. We will be using a workaround later in this script.

select Address_T('Oracle Parkway', 500, 'Redwood Shores', '94065') from dual

Statement 3

Object types are supported as values in SQL/JSON generation functions JSON_OBJECT and JSON_ARRAY.

select JSON_OBJECT( 'myKey' : Address_T('Oracle Parkway', 500, 'Redwood Shores', '94065')) 
from dual

Statement 4

An object type can also be directly be inputted into JSON_OBJECT - in this case no new key needs to be defined.

select JSON_OBJECT(
   Address_T('Oracle Parkway', 500, 'Redwood Shores', '94065')
   ) from dual

Statement 5

SQL null values are mapped to JSON null values by default.

select JSON_OBJECT(
   Address_T('Oracle Parkway', 500, 'Redwood Shores', null)
   ) from dual

Statement 6

with ABSENT ON NULL one can omit the null values from the generated JSON data. This is a global handler - it cannot be specified an a per attribute basis.

select JSON_OBJECT(
   Address_T('Oracle Parkway', 500, 'Redwood Shores', null) ABSENT ON NULL
   ) from dual

Statement 7

NULL ON NULL is the other option (the default).

select JSON_OBJECT(
   Address_T('Oracle Parkway', 500, 'Redwood Shores', null) NULL ON NULL
   ) from dual

Statement 8

The 'returning clause' is also supported.

select JSON_OBJECT(
   Address_T('Oracle Parkway', 500, 'Redwood Shores', null) RETURNING CLOB
   ) from dual

Statement 9

Let's create another type that contains the existing Address_T type.

CREATE OR REPLACE TYPE Person_T AS OBJECT (
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  date_of_birth DATE,
  address       Address_T
);

Statement 10

When mapping an object type to JSON we recursively apply the mapping to all nested types. REFs are not supported since they could span a graph. Please note that a DATE value is mapped to an ISO-8601 formatted date string in JSON. Other SQL data types like TIMESTAMP are mapped similarly - see JSON generation.

select JSON_OBJECT(
   Person_T ('John', 'Doe', to_date('12-AUG-1989'), 
             Address_T('Oracle Parkway', 500, 'Redwood Shores', '94065'))) 
from dual

Statement 11

JSON_SERIALIZE with PRETTY option pretty-prints the output. It does not make a difference in LiveSQL, though.

select JSON_SERIALIZE(
    JSON_OBJECT(
   Person_T ('John', 'Doe', to_date('12-AUG-1989'), 
             Address_T('Oracle Parkway', 500, 'Redwood Shores', '94065'))) pretty)
from dual

Statement 12

Lets create a collection - a VARRAY.

CREATE Or REPLACE TYPE ThreeD_Point_T AS VARRAY(3) OF number;

Statement 13

Collections are mapped to JSON arrays.

select JSON_ARRAY(ThreeD_Point_T(15,-8,19)) from dual

Statement 14

Let's create another collection with TABLE OF ...

create or replace TYPE address_tab_t as TABLE OF Address_T;

Statement 15

It can also be mapped using JSON_ARRAY.

select JSON_ARRAY(
     address_tab_t(
          Address_T('Oracle Parkway', 500, 'Redwood Shores', '94065'),
          Address_T('Twin Dolphin Drive', 10, 'Redwood Shores', '94065')))
from dual

Statement 16

The inverse mapping (JSON to Object type) uses JSON_VALUE. Here we select the address fragment with the path expression '$.ADDRESS' and return it as an instance of Address_T.

SELECT JSON_VALUE('
{
	"FIRST_NAME": "John",
	"LAST_NAME": "Doe",
	"DATE_OF_BIRTH": "1989-08-12T00:00:00",
	"ADDRESS": {
		"STREET": "Oracle Parkway",
		"STREETNO": 500,
		"CITY": "Redwood Shores",
		"ZIP": "94065"
	}
}', '$.ADDRESS' returning Address_T) from dual

Statement 17

Because we cannot output the contents of an object in LiveSQL we use a table of that type and extract some field values.

create table address_tab (adr  address_T)

Statement 18

We insert the generated object type into the newly added table.

insert into address_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "John",
	"LAST_NAME": "Doe",
	"DATE_OF_BIRTH": "1989-08-12T00:00:00",
	"ADDRESS": {
		"STREET": "Oracle Parkway",
		"STREETNO": 500,
		"CITY": "Redwood Shores",
		"ZIP": "94065"
	}
}', '$.ADDRESS' returning Address_T) from dual

Statement 19

we see that the object type instance got instantiated with values originating from the JSON data.

select a.adr.city,
       a.adr.zip,
       a.adr.street
from address_tab a

Statement 20

We do the same for Person objects: create a table to later select the generated values.

create table person_tab (per person_T)

Statement 21

A nested JSON object instantiates nested object types.

insert into person_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "John",
	"LAST_NAME": "Doe",
	"DATE_OF_BIRTH": "1989-08-12T00:00:00",
	"ADDRESS": {
		"STREET": "Oracle Parkway",
		"STREETNO": 500,
		"CITY": "Redwood Shores",
		"ZIP": "94065"
	}
}', '$' returning Person_T) from dual

Statement 22

select p.per.first_name,
       p.per.address.zip,
       p.per.address.city
from person_tab p

Statement 23

delete from person_tab

Statement 24

Please note that 'ZIP' is uppercase in the JSON data whereas the SQL name is "Zip". By default the mapping from JSON names to SQL names is doe using a type-insensitive comparison. You can chose to perform a case-sensitive mapping by specifying 'USING CASE-SENSITIVE MAPPING'.

insert into person_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "John",
	"LAST_NAME": "Doe",
	"DATE_OF_BIRTH": "1989-08-12T00:00:00",
	"ADDRESS": {
		"STREET": "Oracle Parkway",
		"STREETNO": 500,
		"CITY": "Redwood Shores",
		"ZIP": "94065"
	}
}', '$' returning Person_T USING CASE-SENSITIVE MAPPING) from dual

Statement 25

select p.per.first_name,
       p.per.address.zip,
       p.per.address.city
from person_tab p

Statement 26

delete from person_tab

Statement 27

missing values and extra values are being ignored by default. The nature of JSON is often schema-flexible: null values are being ommited and schema evolution often leads to newer JSON data having more fields. Enforcing strict matching by default may break JSON-based applications.

insert into person_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "John",
	"age": 23,
        "playsLottery":"nope"
}', '$' returning Person_T) from dual

Statement 28

We see that most values are null.

select p.per.first_name,
       p.per.address.zip,
       p.per.address.city
from person_tab p

Statement 29

With the optional 'ERROR ON MISMATCH' clause we can enforce strict mapping. The default is 'IGNORE ON MISMATCH'.

insert into person_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "John",
	"age": 23
}', '$' returning Person_T ERROR ON MISMATCH) from dual

Statement 30

delete from person_tab

Statement 31

It is possible to restrict the errors being raises to allow missing values or extra values.

insert into person_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "John",
}', '$' returning Person_T ERROR ON MISMATCH (EXTRA DATA)) from dual

Statement 32

select p.per.first_name,
       p.per.address.zip,
       p.per.address.city
from person_tab p

Statement 33

delete from person_tab

Statement 34

This query succeeds because only a missing field would cause an error. The extra field with the key name "an_extra_field" is being ignored.

insert into person_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "Jane",
	"an_extra_field" : true,
	"LAST_NAME": "Doe",
	"DATE_OF_BIRTH": "1991-03-11",
	"ADDRESS": {
		"STREET": "Oracle Parkway",
		"STREETNO": 500,
		"CITY": "Redwood Shores",
		"ZIP": "94065"
	}
}', '$' returning Person_T ERROR ON MISMATCH (MISSING DATA)) from dual

Statement 35

select p.per.first_name,
       p.per.address.zip,
       p.per.address.city
from person_tab p

Statement 36

With EXTRA DATA as an error the extra field is now causing an error.

insert into person_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "Jane",
	"an_extra_field" : true,
	"LAST_NAME": "Doe",
	"DATE_OF_BIRTH": "1991-03-11",
	"ADDRESS": {
		"STREET": "Oracle Parkway",
		"STREETNO": 500,
		"CITY": "Redwood Shores",
		"ZIP": "94065"
	}
}', '$' returning Person_T ERROR ON MISMATCH (EXTRA DATA)) from dual

Statement 37

delete from  person_tab

Statement 38

The value "500" is auto-converted to a number.

insert into person_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "Jane",
	"LAST_NAME": "Doe",
	"DATE_OF_BIRTH": "1991-03-11",
	"ADDRESS": {
		"STREET": "Oracle Parkway",
		"STREETNO": "500",
		"CITY": "Redwood Shores",
		"ZIP": "94065"
	}
}', '$' returning Person_T ERROR ON MISMATCH) from dual

Statement 39

select p.per.first_name,
       p.per.address.zip,
       p.per.address.streetNo,
       p.per.address.city
from person_tab p

Statement 40

In this example the value "500x" cannot be converted to a number and an error is raised. Without the ON MISMATCH clause this would be ignored!

insert into person_tab
SELECT JSON_VALUE('
{
	"FIRST_NAME": "Jane",
	"LAST_NAME": "Doe",
	"DATE_OF_BIRTH": "1991-03-11",
	"ADDRESS": {
		"STREET": "Oracle Parkway",
		"STREETNO": "500x",
		"CITY": "Redwood Shores",
		"ZIP": "94065"
	}
}', '$' returning Person_T ERROR ON MISMATCH) from dual

Statement 41

The mapping of spatial objects (SDO_GEOMETRY to/from GeoJSON) is also supported.

create table spatial_objects (obj SDO_GEOMETRY)

Statement 42

This statement takes a GeoJSON and converts it to an equivalent SDO_GEOMETRY object.

insert into spatial_objects
SELECT JSON_VALUE('
{
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [125.6, 10.1]
  },
  "properties": {
    "name": "Dinagat Islands"
  }
}' , '$.geometry' returning SDO_GEOMETRY) from dual

Statement 43

The SDO_GEOMETRY object uses different names for the properties and also a structure. We do perform a special mapping in this case.

select so.obj.SDO_GTYPE,
       so.obj.SDO_SRID,
       so.obj.SDO_POINT.X, so.obj.SDO_POINT.Y, so.obj.SDO_POINT.Z
from spatial_objects so

Statement 44

The inverse route uses the generic mapping - this may be a bug ?!

select JSON_OBJECT(
     SDO_GEOMETRY(2003,NULL,NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,4), 
                  SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11)
     )
) from  dual

Statement 45

But sdo_util package allows to perform the mapping (in both directions).

select sdo_util.to_json(
  SDO_GEOMETRY(2003,NULL,NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,4), 
                  SDO_ORDINATE_ARRAY(7,6, 11,8, 7,9)))
from dual

Statement 46

One use case could be to use object type to manipulate JSON data. Other uses cases include but are not limited to export object states, have strong type check otherwise schema-less JSON data, ... In this example we create a function that takes a person object and anonymizes the address by overwriting the street and streetNo values.

create or replace function anonymize(p1 IN person_t) return person_t is
  p2 person_t;
begin
  p2 := p1;
  p2.address.street := 'unknown';
  p2.address.streetno := 0;
  return p2;
end;

Statement 47

We use the object bases function to modify JSON data. First an object is being created with JSON_VALUE, then we call the function and turn the result back into JSON by using JSON_OBJECT. The statement would be much smaller if the JSON data was not inline.

select JSON_OBJECT(
         anonymize(
            JSON_VALUE('
       {
	"FIRST_NAME": "Jane",
	"an_extra_field" : true,
	"LAST_NAME": "Doe",
	"DATE_OF_BIRTH": "1991-03-11",
	"ADDRESS": {
		"STREET": "Oracle Parkway",
		"STREETNO": 500,
		"CITY": "Redwood Shores",
		"ZIP": "94065"
	}
      }', '$' returning Person_T  )
)) from dual

Statement 48

The Pl/Sql build-in function JSON_OBJECT does not support the mapping yet (open issue). But we can use EXECUTE IMMEDIATE to force the SQL code path.

create or replace function map_to_json (p1 IN person_t) return varchar2 is
 v varchar2(100);
begin
 v := JSON_OBJECT (p1);
 return v;
end;

Statement 49

create or replace function map_to_json (p1 IN person_t) return varchar2 is
 v varchar2(100);
begin
   execute immediate 'SELECT JSON_OBJECT (:1) from dual' into v using p1;
   return v;
end;

Statement 50

select map_to_json((Person_T ('John', 'Doe', to_date('12-AUG-1989'),null)))
from dual

Statement 51

support for record types and associative arrays also need to be supported.

DECLARE   
  TYPE  record_t IS RECORD (first VARCHAR2(20), last VARCHAR2(25) ); 
  name1 record_t;
  v     varchar2(100);
BEGIN
  name1 := record_t('Beda', 'Hammerschmidt');
  execute immediate 'SELECT JSON_OBJECT (:1) from dual' into v using name1;
END;

Statement 52

DECLARE
 TYPE map_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(2);
 map  map_t;
 v    varchar2(100);
BEGIN

 map('ES') := 'Spain';
 map('US') := 'United States of America';
 map('FR') := 'France';

 execute immediate 'SELECT JSON_OBJECT (:1) from dual' into v using map;

END;

JSON and Object Type mappers

Additional Information