SODA – More Than Just A Table

This post begins investigating what goes on behind the scenes to implement a SODA collection in Oracle. Lets dive right in and create a SODA collection named CLCT and put a JSON document in it using the DBMS_SODA PLSQL package.

DECLARE
  v soda_collection_t;
  d soda_document_t;
  t NUMBER;
BEGIN
  v := DBMS_SODA.CREATE_COLLECTION ('CLCT');
  d := SODA_DOCUMENT_T(
  b_content => utl_raw.cast_to_raw('{"KEY1" : "VALUE1"}'));
  t := v.insert_one(d);
END;

What objects get created to support this collection?  A quick query of the USER_OBJECTS view reveals the following:

SELECT object_name,
       object_type
  FROM user_objects;

OBJECT_NAME               OBJECT_TYPE
------------------------- -----------
CLCT                      TABLE
SYS_C007444               INDEX
SYS_IL0000073558C00005$$  INDEX
SYS_LOB0000073558C00005$$ LOB

The CLCT table stores data for the CLCT collection. It has an index as well as a LOB and LOB index to store JSON data in a Binary Large Object (BLOB) field.

Here is what the CLCT table looks like.

SQL> DESC clct
Name            Null?    Type
--------------- -------- -------------
ID              NOT NULL VARCHAR2(255)
CREATED_ON      NOT NULL TIMESTAMP(6)
LAST_MODIFIED   NOT NULL TIMESTAMP(6)
VERSION         NOT NULL VARCHAR2(255)
JSON_DOCUMENT            BLOB

The JSON_DOCUMENT field contains the JSON document. This means it can be queried using Oracle’s SQL JSON functions such as JSON_VALUE shown here.

SELECT JSON_VALUE(json_document,'$.KEY1')
  FROM clct;

JSON_VALUE(JSON_DOCUMENT,'$.KEY1')
----------------------------------
VALUE1

To investigate what JSON keys exist in the JSON document we access it’s Data Guide like this:

SELECT JSON_DATAGUIDE(json_document)
  FROM clct;

JSON_DATAGUIDE(JSON_DOCUMENT)
--------------------------------------------------
[{"o:path":"$.KEY1","type":"string","o:length":8}]

And since we are working in the meta-data crazy world of JSON there must be some meta-data for the collection as well! Here’s how we access that.

Note the use of the JSON_QUERY SQL function to show the results.

DECLARE
  collection SODA_COLLECTION_T;
BEGIN
  collection := DBMS_SODA.open_collection('CLCT');
  DBMS_OUTPUT.PUT_LINE(
        JSON_QUERY(collection.get_metadata,'$' PRETTY));
END;

And here is the meta-data for the collection.

{
"schemaName" : "D",
"tableName" : "CLCT",
"keyColumn" :
{
"name" : "ID",
"sqlType" : "VARCHAR2",
"maxLength" : 255,
"assignmentMethod" : "UUID"
},
"contentColumn" :
{
"name" : "JSON_DOCUMENT",
"sqlType" : "BLOB",
"compress" : "NONE",
"cache" : true,
"encrypt" : "NONE",
"validation" : "STANDARD"
},
"lastModifiedColumn" :
{
"name" : "LAST_MODIFIED"
},
"versionColumn" :
{
"name" : "VERSION",
"method" : "SHA256"
},
"creationTimeColumn" :
{
"name" : "CREATED_ON"
},
"readOnly" : false
}

I’ll discuss details of the meta-data in a later post. For now just notice that the values for the name keys match the keys in the CLCT table.

For more posts about Oracle SQL and JSON click here or for a comprehensive set of training videos click here

Thanks for reading!