📋Steps in this guide1/10

JSON Collections in Oracle Database 23ai/26ai

Oracle database 23ai/26ai introduced JSON collections, which are specifically for use with the Oracle Database API for MongoDB and Simple Oracle Document Access (SODA).

oracle 23configurationintermediate

by OracleDba

25 views

Setup

We create a test user and connect to it. We create and populate some tables to work with.

Code/Command (click line numbers to comment):

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant db_developer_role to testuser1;

-- SELECT_CATALOG_ROLE isn't necessary.
grant select_catalog_role to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

drop table if exists emp purge;
drop table if exists dept purge;

create table dept (
deptno number(2) constraint pk_dept primary key,
dname varchar2(14),
loc varchar2(13)
) ;

create table emp (
empno number(4) constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) constraint fk_deptno references dept
);

create index emp_dept_fk_i on emp(deptno);

insert into dept values
  (10,'ACCOUNTING','NEW YORK'),
  (20,'RESEARCH','DALLAS'),
  (30,'SALES','CHICAGO'),
  (40,'OPERATIONS','BOSTON');

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
  (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20),
  (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30),
  (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30),
  (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20),
  (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30),
  (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30),
  (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10),
  (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20),
  (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10),
  (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30),
  (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20),
  (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30),
  (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20),
  (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

Creating JSON Collection Tables

A JSON collection table has a single column called "DATA" of type "JSON". In the background this has created a number of objects, including a synonym matching the case of the table name as specified during creation. The synonym creation seems to follow these rules when JSON collection tables are created from SQL*Plus and using the package. It may be a little different when using the Oracle Database API for MongoDB. - If the table name is specified in upper case (quoted or unquoted), no synonym is created. - If the table name is specified in lower case or mixed case without quotes, the table is created with an upper case name, and the synonym matches the case used during the table creation. - If the table name is specified in lower case or mixed case with quotes, the table is created with the quoted name, and the synonym provides the upper case version of the table name. Always having an upper case reference to the object means we can access it from SQL without needing to worry about the case of the name. We can optionally inject an "etag" in the document metadata. In a JSON-relational duality view the "etag" is a hash of the document contents. In a JSON collection view the "etag" is just a UUID that is changed each time the record is updated. The "etag" can be tested to check if the data has changed since it was queried, giving a mechanism for optimistic locking.

Code/Command (click line numbers to comment):

drop table if exists fruit_jct;

create json collection table fruit_jct;

desc fruit_jct
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DATA                                               JSON

SQL>

column object_name format a30

select object_type, object_name
from   user_objects
where  lower(object_name) = 'fruit_jct'
order by 1, 2;

OBJECT_TYPE             OBJECT_NAME
----------------------- ------------------------------
SYNONYM                 fruit_jct
TABLE                   FRUIT_JCT

SQL>

drop table if exists fruit_etag_jct;

create json collection table fruit_etag_jct with etag;

Working with JSON Collection Tables

When we insert JSON data into a JSON collection table, if the JSON document doesn't include an element called "_id", one is injected into the JSON. Tables with "etag" support also inject the "etag" as metadata. The hash of the document contents. We can manually provide a "_id" value using the JSON_ID function, which returns a value containing an OID or a UUID as required. We insert two more documents, but this time manually provide the "_id" value in OID and UUID format. We can see the three rows in the table. One with the longer UIID. Updates of the data should target specific rows using the "_id" element. The JSON data can't include the "_id" element or we get an error, even if the value is unchanged. This seems to be because the value is a , and we are presenting it as a string, so they don't match. It feels like the serialization should take care of this, but it doesn't. We could make the change using instead, which is actually easier. We can delete the data based on the "_id" value. After the delete we rollback the changes to leave the data intact.

Code/Command (click line numbers to comment):

insert into fruit_jct (data) values (json('{"fruit":"apple","quantity":10}'));
commit;

select json_serialize(data pretty) as data from fruit_jct;

DATA
--------------------------------------------------------------------------------
{
  "_id" : "66fed30100000177ce395096",
  "fruit" : "apple",
  "quantity" : 10
}

SQL>

insert into fruit_etag_jct (data) values (json('{"fruit":"apple","quantity":10}'));
commit;

select json_serialize(data pretty) as data from fruit_etag_jct;

DATA
--------------------------------------------------------------------------------
{
  "_id" : "66fed32000000177ce39509a",
  "_metadata" :
  {
    "etag" : "2396CF39509B77CEE065000000000001"
  },
  "fruit" : "apple",
  "quantity" : 10
}

SQL>

select json_id('OID');

JSON_ID('OID')
--------------------------------------------------------------------------------
66FD1FC3000001127BA87762

SQL>


select json_id('UUID');

JSON_ID('UUID')
--------------------------------------------------------------------------------
6F6EB69F5BE24FF2BF794093019866B8

SQL>

declare
  l_raw raw(32767);
begin
  select json_id('OID') into l_raw;
  insert into fruit_jct (data) values (json('{"_id":"' || l_raw || '","fruit":"apple","quantity":10}'));

  select json_id('UUID') into l_raw;
  insert into fruit_jct (data) values (json('{"_id":"' || l_raw || '","fruit":"apple","quantity":10}'));
  commit;
end;
/

select json_serialize(data pretty) as data from fruit_jct;

DATA
--------------------------------------------------------------------------------
{
  "_id" : "66fed30100000177ce395096",
  "fruit" : "apple",
  "quantity" : 10
}

{
  "_id" : "66FED35300000177CE39509C",
  "fruit" : "apple",
  "quantity" : 10
}

{
  "_id" : "BD926F7A03A44FB2BFBFFA2FD2B18518",
  "fruit" : "apple",
  "quantity" : 10
}

SQL>

update fruit_jct f
set    f.data = json('
{
  "fruit" : "apple",
  "quantity" : 15
}')
where  f.data."_id".string() = '66fed30100000177ce395096';


select json_serialize(data pretty) as data
from   fruit_jct f
where  f.data."_id".string() = '66fed30100000177ce395096';

DATA
--------------------------------------------------------------------------------
{
  "_id" : "66fed30100000177ce395096",
  "fruit" : "apple",
  "quantity" : 15
}

SQL>

update fruit_jct f
set    f.data = json('
{
  "_id" : "66fed30100000177ce395096",
  "fruit" : "apple",
  "quantity" : 15
}')
where  f.data."_id".string() = '66fed30100000177ce395096';
          *
ERROR at line 1:
ORA-54059: cannot update an immutable column ("TESTUSER1"."FRUIT_JC"."RESID")
to a different value
Help: https://docs.oracle.com/error-help/db/ora-54059/

SQL>

update fruit_jct f
set    f.data = json_transform(f.data, set '$.quantity' = 15)
where  f.data."_id".string() = '66fed30100000177ce395096';

delete from fruit_jct f
where f.data."_id".string() = '66fed30100000177ce395096';

1 row deleted.

SQL>

rollback;

Managing State (Optimistic Locking)

In all the previous operations we've ignored state, assuming the data is not changing. In reality it's possible the data has changed between our service calls. Using the option on the collection table allows us to use optimistic locking. We insert some data into the collection table. Let's assume we want to make a change to this document. We query the JSON document. Notice the "etag" value. Before we have a chance to submit our change, someone else alters the data. We can see the "etag" has changed. In a JSON-relational duality view, if we attempt to update the data using the original "etag" value, it would fail, but with a JSON collection table the "etag" presented is ignored. We rollback the change. Instead we have to manually include the "etag" in the clause and check for zero rows updated. In this example we update the document as we did before, but with the additional check of the "etag". We can see no rows were updated, so we know the data was altered between us querying the data and updating it.

Code/Command (click line numbers to comment):

drop table if exists fruit_etag_jct;

create json collection table fruit_etag_jct with etag;

insert into fruit_etag_jct (data) values (json('{"fruit":"pineapple","quantity":10}'));
commit;

select json_serialize(f.data pretty) as data
from   fruit_etag_jct f
where  f.data."fruit" = 'pineapple';

DATA
--------------------------------------------------------------------------------
{
  "_id" : "66fed4bb00000177ce3950b2",
  "_metadata" :
  {
    "etag" : "2396CF3950B377CEE065000000000001"
  },
  "fruit" : "pineapple",
  "quantity" : 10
}

SQL>

update fruit_etag_jct f
set    f.data = json_transform(f.data, set '$.quantity' = 15)
where  f.data."fruit" = 'pineapple';
commit;

select json_serialize(f.data pretty) as data
from   fruit_etag_jct f
where  f.data."fruit" = 'pineapple';

DATA
--------------------------------------------------------------------------------
{
  "_id" : "66fed4bb00000177ce3950b2",
  "_metadata" :
  {
    "etag" : "2396CF3950B577CEE065000000000001"
  },
  "fruit" : "pineapple",
  "quantity" : 15
}

SQL>

update fruit_etag_jct f
set    f.data = json('{
  "_metadata" :
  {
    "etag" : "2396CF3950B377CEE065000000000001"
  },
  "fruit" : "pineapple",
  "quantity" : 20
}')
where  f.data."_id".string() = '66fed4bb00000177ce3950b2';


select json_serialize(f.data pretty) as data
from   fruit_etag_jct f
where  f.data."fruit" = 'pineapple';

DATA
--------------------------------------------------------------------------------
{
  "_id" : "66fed4bb00000177ce3950b2",
  "_metadata" :
  {
    "etag" : "2396CF3950B777CEE065000000000001"
  },
  "fruit" : "pineapple",
  "quantity" : 20
}

SQL>

rollback;

update fruit_etag_jct f
set    f.data = json('{
  "_metadata" :
  {
    "etag" : "2396CF3950B777CEE065000000000001"
  },
  "fruit" : "pineapple",
  "quantity" : 20
}')
where  f.data."_id".string() = '66fed4bb00000177ce3950b2'
and    f.data."_metadata"."etag" = '2396CF3950B777CEE065000000000001';

0 rows updated.

SQL>

Creating JSON Collection Views

A JSON collection view is similar to a JSON-relational duality view, but it is read-only. We create it using the SQL/JSON syntax as shown below. We need to alias the resulting JSON column. The name is not restricted, but it makes sense to name it "DATA" to match the column name for JSON-relational duality views. There is currently no restriction on the number or types of columns, but the documentation says it should have a single JSON column.

Code/Command (click line numbers to comment):

create or replace json collection view departments_jcv as
select json {'_id' : d.deptno,
             'departmentName'   : d.dname,
             'location'         : d.loc,
             'employees' :
               [ select json {'employeeNumber' : e.empno,
                              'employeeName'   : e.ename,
                              'job'            : e.job,
                              'salary'         : e.sal}
                 from   emp e
                 where  d.deptno = e.deptno ]} as data
from dept d;

desc departments_jcv
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATA                                               JSON

SQL>

Working with JSON Collection Views

JSON collection views are read-only, so the only thing we can do is query them. Changes to the underlying tables will be reflected in the output of the views. In the following example we delete two of the empyoyees from department 10 and query the view. We can see the JSON output reflects the data change. We rollback the change.

Code/Command (click line numbers to comment):

select json_serialize(d.data pretty) as data
from   departments_jcv d
where  d.data."_id" = 10;

DATA
--------------------------------------------------------------------------------
{
  "_id" : 10,
  "departmentName" : "ACCOUNTING",
  "location" : "NEW YORK",
  "employees" :
  [
    {
      "employeeNumber" : 7782,
      "employeeName" : "CLARK",
      "job" : "MANAGER",
      "salary" : 2450
    },
    {
      "employeeNumber" : 7839,
      "employeeName" : "KING",
      "job" : "PRESIDENT",
      "salary" : 5000
    },
    {
      "employeeNumber" : 7934,
      "employeeName" : "MILLER",
      "job" : "CLERK",
      "salary" : 1300
    }
  ]
}

SQL>

delete from emp where empno in (7782, 7839);

select json_serialize(d.data pretty) as data
from   departments_jcv d
where  d.data."_id" = 10;

DATA
--------------------------------------------------------------------------------
{
  "_id" : 10,
  "departmentName" : "ACCOUNTING",
  "location" : "NEW YORK",
  "employees" :
  [
    {
      "employeeNumber" : 7934,
      "employeeName" : "MILLER",
      "job" : "CLERK",
      "salary" : 1300
    }
  ]
}

SQL>

rollback;

JSON-Relational Duality Views

This subject is covered in its own article here. - JSON-Relational Duality Views in Oracle Database 23ai/26ai For the sake of completeness, we will create a JSON-relational duality view here.

Code/Command (click line numbers to comment):

create or replace json relational duality view departments_dv as
select json {'_id' : d.deptno,
             'departmentName'   : d.dname,
             'location'         : d.loc,
             'employees' :
               [ select json {'employeeNumber' : e.empno,
                              'employeeName'   : e.ename,
                              'job'            : e.job,
                              'salary'         : e.sal}
                 from   emp e with insert update delete
                 where  d.deptno = e.deptno ]}
from dept d with insert update delete;

Dictionary Views

The views display information about JSON collection tables. The views display information about JSON collection views. The views display information about JSON collection tables, JSON collection views and JSON-relational duality views. In 23.5 JSON collection views were not included in the output. In 23.6 this was fixed.

Code/Command (click line numbers to comment):

column owner format a20
column collection_name format a20
column with_etag format a10

select *
from   user_json_collection_tables
order by 1, 2;

OWNER                COLLECTION_NAME      WITH_ETAG
-------------------- -------------------- ----------
TESTUSER1            FRUIT_ETAG_JCT       YES
TESTUSER1            FRUIT_JCT            NO

SQL>

column owner format a20
column collection_name format a20

select *
from   user_json_collection_views
order by 1, 2;

OWNER                COLLECTION_NAME
-------------------- --------------------
TESTUSER1            DEPARTMENTS_JCV

SQL>

column owner format a20
column collection_name format a20
column collection_type format a15

select *
from   user_json_collections
order by 1, 2;

OWNER                COLLECTION_NAME      COLLECTION_TYPE
-------------------- -------------------- ---------------
TESTUSER1            DEPARTMENTS_DV       DUALITY VIEW
TESTUSER1            DEPARTMENTS_JCV      VIEW
TESTUSER1            FRUIT_ETAG_JCT       TABLE
TESTUSER1            FRUIT_JCT            TABLE

SQL>

SODA

We can see the JSON collection tables and duality views are visible as a SODA collection. In this release the JSON collection views are not. If we create a collection using the package, it is now created as a collection table. We remove this collection using the package. More information about SODA can be found here.

Code/Command (click line numbers to comment):

set serveroutput on
declare
  l_collection  soda_collection_t;
begin
  l_collection := dbms_soda.open_collection('FRUIT_JCT');
  if l_collection is not null then
    dbms_output.put_line('collection: ' || l_collection.get_name());
  else
    dbms_output.put_line('collection does not exist.');  
  end if;
end;
/
collection: FRUIT_JCT

PL/SQL procedure successfully completed.

SQL>


declare
  l_collection  soda_collection_t;
begin
  l_collection := dbms_soda.open_collection('DEPARTMENTS_DV');
  if l_collection is not null then
    dbms_output.put_line('collection: ' || l_collection.get_name());
  else
    dbms_output.put_line('collection does not exist.');  
  end if;
end;
/
DEPARTMENTS_DV

PL/SQL procedure successfully completed.

SQL>


declare
  l_collection  soda_collection_t;
begin
  l_collection := dbms_soda.open_collection('DEPARTMENTS_JCV');
  if l_collection is not null then
    dbms_output.put_line('collection: ' || l_collection.get_name());
  else
    dbms_output.put_line('collection does not exist.');  
  end if;
end;
/
collection does not exist.

PL/SQL procedure successfully completed.

SQL>

declare
  l_collection  soda_collection_t;
begin
  l_collection := dbms_soda.create_collection('TestCollection1');

  if l_collection is not null then
    dbms_output.put_line('collection id : ' || l_collection.get_name());
  else
    dbms_output.put_line('collection does not exist.');  
  end if;
end;
/
collection id : TestCollection1

PL/SQL procedure successfully completed.

SQL>


column owner format a20
column collection_name format a20
column with_etag format a10

select *
from   user_json_collection_tables
order by 1, 2;

OWNER                COLLECTION_NAME      WITH_ETAG
-------------------- -------------------- ----------
TESTUSER1            FRUIT_ETAG_JCT       YES
TESTUSER1            FRUIT_JCT            NO
TESTUSER1            TestCollection1      NO

SQL>

set serveroutout on
declare
  l_status  number := 0;
begin
  l_status := dbms_soda.drop_collection('TestCollection1');

  dbms_output.put_line('status    : ' || l_status);
end;
/
status    : 1

PL/SQL procedure successfully completed.

SQL>


select *
from   user_json_collection_tables
order by 1, 2;

OWNER                COLLECTION_NAME      WITH_ETAG
-------------------- -------------------- ----------
TESTUSER1            FRUIT_ETAG_JCT       YES
TESTUSER1            FRUIT_JCT            NO

SQL>

Thoughts

Here are some thoughts about the current implementation of JSON collections. - The handling of the "_id" element in JSON collection tables feels odd. It's a RAW value, and doesn't get serialized properly, which causes confusion if you are serializing the data and then applying an update. The "_id" of a JSON collection view and a JSON-relational duality view are based on a primary key column, not a , so they are serialized and handled in a more "normal" way. - JSON collection views feel unfinished. They don't enforce the structure of the view, and are not listed in the views. Also, they are not presented as collections to SODA. - The state management using the "etag" metadata is different between JSON collection tables and JSON-relational duality views. That's not a problem as long as you know the difference. - The synonym creation is not consistent for all objects. The documentation suggest a synonym will be created in the same way for JSON-relational duality views and JSON collection views, but it is not. Instead they are always created with an upper case name. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!