JSON, Java, and the Oracle Database Part 1

13 min readFeb 22, 2023

Databases today are packed with powerful features and helpful enhancements to support the demand that complex and varying architectures, designs, and processes have. One of these features is the ability to work with and store JavaScript Object Notation (JSON) objects in the database.

JSON is one of the common formats for storing, transferring, and exchanging data for modern applications and services. It provides a few data types that include booleans, strings, numbers, arrays, and objects — the last two of which are what make JSON flexible. JSON in the database offers developers simplicity, velocity, and flexibility when building and designing applications, as JSON documents can be easily retrieved, stored, and updated with simple APIs and without the rigidity that relational databases strongly uphold.

Document databases, or databases that are designed to store and query JSON, are widely adopted by companies for use cases that require the storing of structured and unstructured data, such as user preferences, product catalogs, reviews, and comment sections.

However, many of today’s modern relational databases including the Oracle Database now offer the ability to do the same.

The Oracle Database, A Great Document Database

The Oracle Database, a relational database, supports the storing of JSON inside a column since the 12c release, which serves as an extension to the SQL standard providing the ability to query, update and insert JSON documents all with SQL. With this feature, developers can take advantage of the benefits that both relational and documents offer.

The Oracle Database also provides two NoSQL-style APIs below providing the ability to work with JSON document collections without SQL, which we will explore in the following blog:

Simple Oracle Document Access (SODA) — a set of NoSQL-style APIs for create, read, update and delete (CRUD) operations on collections of documents, available since 12c.
MongoDB APIs — Oracle Database API for MongoDB mapping MongoDB commands to corresponding SQL statements which enable MongoDB drivers, frameworks, and tools to work against the Oracle Database starting 19c.

In this first part of the series, we will go over using pure Oracle JDBC and oracle.sql.json package to work with JSON in the Oracle Database. We will first go through the Oracle Database side of things to focus on SQL only and then jump right into Java to explore how to interact with, process, or pass in JSON through Java.

Creating Tables with JSON using SQL

Prior to the 21c release, JSON in the Oracle Database can be stored either as a VARCHAR2, CLOB, or BLOB column as shown below. Using any of these data types to store JSON, it is strongly recommended that the SQL condition is_json is used to ensure both columns are valid JSON data.

CREATE TABLE profiles_19c (
  ...
  preferences VARCHAR2(4000),
  settings BLOB,
  CONSTRAINT prf_is_json CHECK (preferences IS JSON),
  CONSTRAINT set_is_json CHECK (settings IS JSON)
)

With the 21c release, JSON data can now be stored in the new JSON data type (backed by OSON), which is an optimized native binary storage format for storing and processing JSON. Oracle strongly recommends using this new data type for storing JSON for some of the benefits that the OSON format brings:

faster query performance and more efficient updates as it does not need to be parsed as JSON text, unlike its counterparts
more compact JSON text incurring less storage I/O and network transfer costs
efficient random access
ability to store primitive types like timestamps, raws, etc.

CREATE TABLE profiles (
  ...
  preferences JSON,
  settings JSON
)

The rest of the blog will show examples using an Oracle Database version 21c.

Inserting JSON using SQL

With a working table in an Oracle Database, inserting a record can be done with standard Oracle database APIs where the JSON value is passed as a JSON string.

INSERT INTO profiles (profileId, username, preferences, settings) 
VALUES (
  1, 
  'normanaberin',
  '{"timezone": "America/Chicago", "language": "English (US)", "theme": "Dark", "compact": true}', 
  '{"version": "4.12.1", "level": 1, "security": {"sharing": true, "visibility": "private"}, "keywords": ["A", "B"]}'
)

Querying JSON using SQL

Querying for JSON can be done like any other query, but the Oracle Database also offers simple dot-notation access in the column and conditional clauses. In addition, item methods like .number(), .string(), .boolean(), etc. are offered by Oracle for transforming data into a scalar value instead of returning JSON and more. The following code snippets below demonstrate these features and how simple the queries can be using Oracle SQL.

The simplest example fetches the entire preferences JSON document and the corresponding username.

SELECT username, preferences FROM profiles

The next example shows a breakdown of the number of profiles with a preference where “compact” is true or enabled, per version. This example demonstrates dot-notation syntax in the different parts of the query, as well as usage of the item method .boolean() transforming boolean JSON data into a SQL VARCHAR2(20) interpretation of the targeted JSON value. Note that null is returned if an evaluated attribute is missing in the JSON document.

SELECT p.settings.version.string() version, count(p.profileId) no 
FROM profiles p 
WHERE p.preferences.compact.boolean() = 'true' 
GROUP BY p.settings.version.string()
HAVING count(p.profileId) >= 1

Taking this even further, the Oracle Database also offers SQL/JSON conditions such as json_exists as a way to filter rows based on a JSON document using path expressions. It is also important to note that JSON values can be indexed just like relational attributes. More information on this can be found in the References section at the end of the blog. In the example below, the query returns JSON documents where the attribute visibility exists and is set to true.

SELECT p.settings
FROM profiles p
WHERE json_exists(p.settings, '$.security?(@.visibility == true)')

Updating JSON

Updating JSON in the Oracle Database can be done in three(3) different ways. The first option is done similarly to how you would normally run updates as shown in the example below, which will update the entire document with what is provided as the value.

UPDATE profiles p
SET p.preferences = 
  '{"timezone": "America/New York", "language": "English (US)", "theme": "Light", "compact": true}'
WHERE p.profileId = 1

The second option is the functionJSON_MERGEPATCH which is designed to update only specific parts of a JSON document. The function JSON_MERGEPATH requires two arguments: the first argument takes the source JSON document and the second takes another document that contains the patches that need to be merged. The below example changes the inserted and updated (previous update above) record in the following ways:

Updates the attribute version to 4.12.2
Removes the attribute visibility inside security
Adds a new attribute subscriptions
Replaces the keywords array with an empty array

-- update table set jsondoc = json_mergepatch(jsondoc, patchdoc)
UPDATE profiles p 
SET p.settings =
  json_mergepatch(p.settings, '{"version": "4.12.2", "security": {"visibility": null}, "keywords": [], "subscriptions": []}')
WHERE p.profileId = 1

On a side note, JSON_MERGEPATCH can also be used in select statements to show an updated JSON document without persisting the changes. In the example below, the settings JSON document is returned without showing the security object.

SELECT json_mergepatch(p.settings, '{"security":null}')
FROM profiles p

Finally, the third option is the function JSON_TRANSFORM which is designed to update targeted parts of a JSON document. This function comes with various operations for updating such as SET, INSERT, REPLACE, APPEND, and more. JSON_TRANSFORM also comes with three handlers that are optional: ON EXISTING, ON MISSING, and ON NULL, which provide the developer the ability to define the behavior when these cases are encountered during the update. In the following example below, security.visibility is added back and set to public after being removed in the previous update. Also, note the handler IGNORE ON EXISTING which will prevent any more updates since the visibility attribute already exists.

UPDATE profiles p 
SET p.settings = json_transform(p.settings, SET '$.security.visibility' = '"public' IGNORE ON EXISTING ) 
WHERE p.profileId = 1

One advantage of using JSON_TRANSFORMthat makes it different from JSON_MERGEPATCH is the ability to update arrays without replacing the whole thing. In the example below, a keyword is being appended inside the keywords array of the security property. Prepending and updating an element can also be done besides appending.

UPDATE profiles p 
SET p.settings = json_transform(p.settings, APPEND '$.keywords' = 'A') 
WHERE p.profileId = 1

Just like JSON_MERGEPATCH, JSON_TRANSFORM can be used in a select statement to show an updated JSON document on the fly. The example below shows the settings JSON without the security object.

SELECT json_transform(p.settings, REMOVE '$.subscriptions')
FROM profiles p

Generating JSON from Relational and Back using SQL

The Oracle Database also offers the generation of JSON from relational tables through SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg. Below is a quick breakdown of these functions:

json_object — constructs JSON objects
json_array — constructs JSON arrays
json_objectagg — aggregates into an object
json_arrayagg — aggregates into an array

Take the following relational table for example, which only have non-JSON columns. The applicants table contains the records of applicants with their lastName, countryISOCode3 (ISO code value of the country they are from), and their preferred locations.

create table applicants (
    applicantId number primary key,
    lastName varchar2(32),
    countryISOCode3 varchar2(3),
    prefferedLocation1 varchar2(80),
    prefferedLocation2 varchar2(80),
    prefferedLocation3 varchar2(80)
);

The select statement below demonstrates the usage of all of the JSON-generating functions combined. The query may seem complex, but it simply returns an object containing arrays of applicant information per country. At the core, json_object is used to build the applicant objects containing 2 columns and an array of the preferred locations created with json_array. Next, these rows of JSON objects are aggregated into an array using json_arrayagg. Finally, json_objectagg is used to create an object where the countryISOCode3 are the keys and the aggregated array split by their corresponding country as the value.

SELECT 
  JSON_OBJECTAGG(
    countryISOCode3 VALUE 
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'lastName': lastName,
          'preferredLocations': JSON_ARRAY(
	    prefferedLocation1, 
            prefferedLocation2, 
            prefferedLocation3)
        )
      )
  )
FROM applicants
GROUP BY countryISOCode3

Returning to the profiles examples, the Oracle Database also offers a way to convert JSON documents into a relational table using json_table. The function json_table creates a virtual table to select from by requiring the column clause and collecting the columns values from JSON documents through the explicitly defined paths.

SELECT p.profileId, userPreferences.*, userSettings.version
FROM 
  profiles p , 
  JSON_TABLE(p.preferences, '$' COLUMNS (
      timezone varchar2(30) PATH '$.timezone',
      language varchar2(80) PATH '$.language',
      compact varchar2(10) PATH '$.compact'
      )) userPreferences,
  JSON_TABLE(p.settings, '$' COLUMNS (
      version varchar2(15) PATH '$.version'
      )) userSettings

The query then returns rows of data showing profileId, timezone, language, compact, and version as the columns.

The Java Application

After exploring the Oracle SQL side of working with JSON in the Oracle Database, the Java side of processing and handling JSON is pretty straightforward. In this section of the blog, we will go over working with pure Oracle JDBC on interacting with JSON data.

Database Connectivity

Before diving into JSON, let us look at how the connection to the Oracle Database works with the pure Oracle JDBC examples in the Java application. Inside src/main/java/database, you can find the two files:

DatabaseConfig.java retrieves the connection details from the environment and provides static getter methods
DatabaseServiceWithPooling.java instantiates PoolDataSource by configuring connection properties and provides a function for retrieving a connection

public class DatabaseServiceWithPooling {

    private PoolDataSource pds;

    /**
     * Creates an instance of pool-enabled data source and configures connection properties
     * @throws SQLException
     */
    public DatabaseServiceWithPooling() throws SQLException {
        this.pds = PoolDataSourceFactory.getPoolDataSource();
        this.pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        this.pds.setURL(DatabaseConfig.getDbUrl());
        this.pds.setUser(DatabaseConfig.getDbUser());
        this.pds.setPassword(DatabaseConfig.getDbPassword());
    }

    /**
     * Gets a connection using the data source instance.
     * @return
     * @throws SQLException
     */
    public OracleConnection getDatabaseConnection() throws SQLException {
        return (OracleConnection) this.pds.getConnection();

    }
}

Finally, to get a connection, a variable of type Connection is created as shown below which calls the method getDatabaseConnection from DatabaseServiceWithPooling. You will see this method used throughout the examples in the Java application, such as connection.prepareStatement and connection.prepareCall:

private DatabaseServiceWithPooling pds = new DatabaseServiceWithPooling();
Connection connection = pds.getDatabaseConnection();

Working with JSON with pure Oracle JDBC

With the release of Oracle Database 21c, the database has started supporting the new native JSON SQL type and the database JDBC driver have added support for the oracle.sql.json package. The oracle.sql.json package provides APIs that simplify working with JSON which include the important Java objects that can be found in some of the following examples in the Java application:

OracleJsonFactory - provides multiple methods for creating JSON objects, arrays, and more. It also uses a small amount of memory and may pool improving performance. It is recommended to reuse across multiple requests (thread-safe).
OracleJsonObject - holds JSON documents with functionality for retrieving and setting values
OracleJsonArray - holds JSON arrays with functionality for adding values

Querying JSON using Java

To start, the first function (blogExampleA) inside src/main/java/moreexamples/BlogExamples.java, shows a basic example that runs a query and retrieves the column value preferences, and converts it into a JSON string. The explanations for some of the new lines are as follows:

A PreparedStatement is instantiated with the simple query for a VARCHAR2 and a JSON column
The PreparedStatement is executed and the returned ResultSet is generated and assigned to rs
A while-loop looping through all rows retrieved by moving the ResultSet cursor and returning false if the cursor has reached the last row
The JSON column is retrieved and converted into a JSON string using .getObject

public static void blogExampleA(DatabaseServiceWithPooling pds) throws SQLException {
  final String RETRIEVE_QUERY = "select username, preferences from profiles";

  try (Connection connection = pds.getDatabaseConnection()) {
    try (PreparedStatement retrieve_stmt = connection.prepareStatement(RETRIEVE_QUERY)) {
      try (ResultSet rs = retrieve_stmt.executeQuery()) {
        while (rs.next()) {
          String username = rs.getObject("username", String.class);
          String productInformation = rs.getObject("preferences", String.class);
          System.out.println(username + "=" + productInformation);
        }
      }
    }
  }
}

blogExampleB retrieves the settings JSON document for all profiles where the attribute visibility exists and is set to true. This next function incorporates the new methods added by the new package oracle.sql.json and uses OracleJSONObject to hold the JSON document and .get methods to retrieve parts of the document. The explanation for the important changes is as follows:

The settings column is retrieved from using .getObject and converted OracleJsonObject. In the SQL query, settings is backed by OSON (optimized binary JSON format) and is not retrieved and parsed as text.
The security object is retrieved using .get from the OracleJsonObject settings
The attributes version and visibility are retrieved using .getBoolean and .getString from the two objects: settings and security, without having to parse any JSON text or look at the other parts of the document

public static void blogExampleB(DatabaseServiceWithPooling pds) throws SQLException {
  final String RETRIEVE_QUERY = """
    SELECT p.settings
    FROM profiles p
    WHERE json_exists(p.settings, '$.security?(@.visibility == true)')
    """;
   
  try (Connection connection = pds.getDatabaseConnection()) {
    try (PreparedStatement retrieve_stmt = connection.prepareStatement(RETRIEVE_QUERY)) {
      try(ResultSet rs = retrieve_stmt.executeQuery()) {
        while (rs.next()) {

          OracleJsonObject settings = rs.getObject("settings", OracleJsonObject.class); // 1
          OracleJsonObject security = settings.get("security").asJsonObject(); // 2
          System.out.println("version=" +settings.getString("version") + "; security.visibility=" + security.getString("visibility")); // 3
        }
      }
    }
  }
}

Inserting/Updating JSON using Java

For inserting and updating JSON, a new OracleJsonObject or an existing one is used as the value bound to bind variables in an update or insert statement. blogExampleC below creates the JSON objects preferences and settings using OracleJsonObject and keywords using OracleJsonArray with OracleJsonFactory . The explanation for the following snippet is as follows:

An instance of OracleJsonFactory factory is either instantiated or retrieved from the method getJsonFactory() as it may pool and reuse memory
An empty preferences of the type OracleJsonObject is created using factory.createObject()
The timezone attribute is added with the .put method along with other attributes
An empty keywords of the type OracleJsonArray is created using factory.createArray()
A mapping with OracleJsonObject as the value is inserted into settings
A mapping with OracleJsonArray as the value is inserted into settings
The OracleJsonObject is bound to bind parameter 2 as a OracleType.JSON using .setObject()

public static void blogExampleC(DatabaseServiceWithPooling pds) throws SQLException {
  final String INSERT_QUERY = "INSERT INTO profiles (username, preferences, settings) VALUES (:1, :2, :3)";

  OracleJsonFactory factory = getJsonFactory(); // 1
  OracleJsonObject preferences = factory.createObject(); // 2
  preferences.put("timezone", "America/Chicago"); // 3
  preferences.put("language", "English (US)");
  preferences.put("theme", "Dark");
  preferences.put("compact", true);

  OracleJsonObject security = factory.createObject();
  security.put("sharing", true);
  security.put("visibility", "private");

  OracleJsonArray keywords = factory.createArray();  // 4
  keywords.add("A");
  keywords.add("B");

  OracleJsonObject settings = factory.createObject();
  settings.put("version", "4.12.1");
  settings.put("level", 1);
  settings.put("security", security); // 5
  settings.put("keywords", keywords); // 6

  try (Connection connection = pds.getDatabaseConnection()) {
    try(PreparedStatement insert_stmt = connection.prepareStatement(INSERT_QUERY)) {
      insert_stmt.setString(1, "normanaberin");
      insert_stmt.setObject(2, preferences, OracleType.JSON); // 7
      insert_stmt.setObject(3, settings, OracleType.JSON);
      insert_stmt.executeUpdate();
      int inserted = insert_stmt.executeUpdate();
      System.out.println("inserted:" + inserted);
    }
  }
}

For updating JSON documents with any of the three (3) options in the earlier section above, you can take the same approach that the example blogExampleC shows. For example, blogExampleD demonstrates updating the settings JSON document in the table profiles using JSON_TRANSFORM and appending a new object inside subscriptions using the APPEND operation. Similar to the previous example, blogExampleD updates the JSON document by retrieving an instance of factory and creating the object that needs to be appended. The rest of the code shows the creation of a PreparedStatement and the binding of the OracleJsonObject new_subscription before execution. Note that with the handler CREATE ON MISSING the immediate parent subscriptions attribute will be created as a singleton array with the value as the single array element if it does not exist.

public static void blogExampleD(DatabaseServiceWithPooling pds) throws SQLException {
  final String UPDATE_QUERY = """
    UPDATE profiles p 
    SET p.settings = json_transform(p.settings, APPEND '$.subscriptions' = :1 CREATE ON MISSING) 
    WHERE p.profileId = :2
  """;

  final int profileId = 1;
  OracleJsonFactory factory = getJsonFactory();
  OracleJsonObject new_subscription = factory.createObject();
  new_subscription.put("subscriptionId", 10191);
  new_subscription.put("subscriptionName", "Jules");
  new_subscription.put("subscriptionDate", LocalDateTime.now());

  try (Connection connection = pds.getDatabaseConnection()) {
    try(PreparedStatement update_stmt = connection.prepareStatement(UPDATE_QUERY)) {
      update_stmt.setObject(1, new_subscription, OracleType.JSON);
      update_stmt.setInt(2, profileId);
      int updated = update_stmt.executeUpdate();
      System.out.println("updated:" + updated);
    }
  }
}

JSON can also be inserted and retrieved as a JSON string as shown in previous examples. The last function blogExampleE shows the preferences JSON document being updated entirely with a JSON string.

public static void blogExampleE(DatabaseServiceWithPooling pds) throws SQLException {
  final String UPDATE_QUERY = "UPDATE profiles SET preferences=:1 WHERE profileId = :2";
  final String jsonstring = "{\"timezone\": \"America/Chicago\"}";
  final int profileId = 1;

  try (Connection connection = pds.getDatabaseConnection()) {
    try(PreparedStatement update_stmt = connection.prepareStatement(UPDATE_QUERY)){
      update_stmt.setString(1, jsonstring);
      update_stmt.setInt(2, profileId );
      int updated = update_stmt.executeUpdate();
      System.out.println("updated:" + updated);
    }
  }
}

Final Thoughts

Working with JSON and the Oracle Database has been made simpler and faster with the addition of the new JSON data type and the methods and Java objects from the oracle.sql.json package. Having the ability to store JSON in a relational database can provide developers the flexibility and velocity that non-relational databases provide as well as the security and integrity of relational databases.

In the next part of the series, we will explore working with JSON and the Oracle Database using SODA, MongoDB APIs, and more! Thank you for the read and stay tuned!

Have questions? Leave a comment below or chat with us on Discord! https://discord.gg/WQR5u5R5gG