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!