soda-json-nosql-rdbms-oracle

Non-relational Oracle

JSON in Oracle

I am sure this may seem like an obvious feature for some of you, especially those who have worked with schema-less stores. However, it is often overlooked by programmers who choose or stick with RDBMS stores. This is the audience I would like to dedicate this short write-up to.

Don’t get me wrong. I have nothing against relational databases. In fact, I have been working with them (particularly with Oracle) since I can remember, and this post only promotes them more. The part that seems weird, though, is the way we store our objects using tables. With RDBMS, we scatter them across multiple tables. Do you hear the uncomfortable sound of a fork scratching a plate?

Now, here comes the good news and the key point of this post. Working with Oracle or PostgreSQL doesn’t mean you are condemned to use ubiquitous relational tables.

Imagine this. Instead of a flat structure that you have to query using multiple joins through different tables, you can access one column and get one JSON document. Only one column, and you have a clear picture of the state of your entire object. How obvious to Mongo or CosmosDB users and how strange to developers welded to RDBMS!

❇️ Let’s take a look at the following example of a Product. The Product is represented by one consistent object.

{
   "id":"b98551ec-afc4-435c-9d11-420f7868b138",
   "name":"polyamides-menazon-tamable",
   "description":"concomitances contractible...",
   "price":525.64,
   "quantity":357,
   "techSpecs":{
      "weight": 5.0,
      "dimensions":{
         "width": 22,
         "height": 33,
         "depth": 44
      }
   }
}

And here is the SQL table to store the above object:

table-json

Could it be any simpler or any more concise?

❇️ Let’s compare it now to the relational representation of the same object. The Product is represented in more flattened way for efficiency and simplicity. Let’s say that was a conscious decision of a team who likes YAGNI.

ID,NAME,DESCRIPTION,PRICE,QUANTITY,TECHSPECSWEIGHT,TECHSPECSDIMENSIONSWIDTH,TECHSPECSDIMENSIONSHEIGHT,TECHSPECSDIMENSIONSDEPTH
b98551ec-afc4-435c-9d11-420f7868b138,polyamides-menazon-tamable,concomitances contractible...,525,64,357,5,22,33,44

And here is the SQL table to store the above object:

table-json

I have no trouble to point out the winner for storing the business objects. The JSON representation is much more readable. Going towards relational model just because you have Oracle or PosgreSQL is not so obvious anymore, is it❓

We must admit that storing a business domain object, such as an aggregate root, as a document looks and behaves much more naturally than spreading it across multiple tables and struggling with crazy ORM mappings, forcing them to behave efficiently.

My current Oracle-based project

In my current Oracle-based project, we have reduced the number of tables representing one business object into a concise JSON representation stored in one table and one column. This means no more mess and no more ORM annotations to manage. It is so much clearer now. Many related Java entity classes are reduced to one cohesive object. Plus, we still have ACID. 🤘

A very few code examples (with Kotlin):

class Product(
  val id: String,
  val name: String,
  val description: String?,
  val price: BigDecimal,
)

val product = Product("1", "Product 1", "Description", BigDecimal("10.00"))

// SODA Java API
val oracleDatabase = oracleRDBMSClient.getDatabase(connection)
val oracleCollection = oracleDatabase.openCollection(collectionName)
val serializedDocument = objectMapper.writer().writeValueAsBytes(product)
val document = oracleDatabase.createDocumentFromByteArray(serializedDocument)
oracleCollection.insert(document)

// JSON (via JDBC) 
SimpleJdbcInsert(jdbcTemplate).withTableName(tableName).usingColumns(jsonColumnName).execute(
    MapSqlParameterSource().addValue(
      "DOC", // see the CREATE TABLE below
      objectMapper.writer().writeValueAsBytes(product),
      OracleType.JSON.vendorTypeNumber
    )    
)

Oracle brings two API-level strategies to work with document-based structures:

The table with one JSON (DOC) and one virtual column (ID):

CREATE TABLE PRODUCT_JSON (
    DOC        JSON,
    ID AS (json_value(DOC, '$.id' RETURNING VARCHAR2(255))),
    PRIMARY KEY (ID)
)

And few SQL examples. JSON query notation provided by Oracle is very powerful and expressive:

-- Search products through all fields!
SELECT * FROM PRODUCT_JSON WHERE json_exists(DOC, '$.*?(@.upper() has substring $p1)' PASSING :0 AS "p1")
-- Search products by name
SELECT * FROM PRODUCT_JSON WHERE json_exists(DOC, '$.name?(@ like "polyamides%")')

The above examples are extracted from the VShop repository. For more specific cases you can look into the Oracle documentation.

The best part with this approach is that you do not need to onboard yet another technology or vendor into your company in order to utilize NoSQL / document-based capabilities. That would almost certainly complicate your infrastructure and increase your TCO. Just use what is available, and you are good to go! This is my advice. Ladies and gentlemen, KISS and RTFM in the best of their forms!

Going even further, you can mix NoSQL with SQL styles in Oracle database and in one database query. Some portions of JSON documents can be extracted as virtual (mapped) columns or joined with relational columns inside a query. Oracle provides very rich notation to query JSON objects. Oracle 21c introduces native JSON data type, and Oracle 23c merges both worlds – document-based and relational together!

Show me the code

If you are interested in exploring NoSQL capabilities in Oracle and learning more about how I have implemented different repositories working with different Spring Boot profiles (JPA, SODA, JSON, and MONGO) all together in one project, check out my recent repository. In addition to showcasing different stores, I have also included some interesting approaches, including:

I have also included a docker-compose.yaml file to run Oracle, Infinispan, and Mongo containers.

The VShop repository is my personal laboratory, created for blogging, training, and R&D purposes. I hope you find it useful!

Afterword

Although I have advertised Oracle SODA a little bit in this post, I do not recommend using it. I created the blinking banner before realizing the actual state of SODA implementation for Java. However, I think Oracle JSON is an outstanding feature that I really like. Besides, SODA is just a wrapper (library) around Oracle JSON.

I strongly suggest you to explore the capabilities of your current RDBMS, before you consider investing your time and money in yet another tool.

❗By the way, did you know that you can query Graph structures in Oracle? But that’s another story.

Further materials

Thank you

Thank you for reading.

comments powered by Disqus