Add support for client-side binary OSON encoding/decoding

[loiclefevre]

Describe the feature

PR #1682 added the support for Oracle JSON native data type.

This data type uses the OSON binary format to encode JSON data.

The Oracle JDBC driver provides the ability for client-side encoding and decoding of the OSON format.
Because this binary format compress JSON data (deduplicating field names, serializing dates with less bytes, etc.) the
network traffic is reduced between the client and the database. Also, offloading the envoding and decoding on the client reduces the resources used by the database server.

Regarding decoding, database can send the OSON encoded JSON data instead of the UTF-8 text. This allows for lazy deserialization of the JSON data/fields when only required.

To ease the integration of this capability, the Oracle JDBC extension provides the support of Jackson API. You can use ObjectMapper to write and read OSON binary format.

You can find an example here: https://github.com/oracle/ojdbc-extensions/blob/main/ojdbc-provider-jackson-oson/src/test/java/oracle/jdbc/provider/oson/test/EncondingTest.java

Contribution

No response

[tsegismont]

Thanks for sharing this @loiclefevre

Vert.x does not require Jackson Databind by default and and our SQL Client API only allows usage of Vert.x JsonObject etc. for JSON columns.

So I'm wondering what do you think we could get from this extension?

[loiclefevre]

Hi @tsegismont, basically, you can get:

• SQL fidelity for binary encoded JSON fields (thanks to more control on the binary type used to encode values)
  • and thus better performance when running SQL on JSON
• smaller binary JSON data: less bytes to transfer, less bytes to persist inside the database
• lazy deserialization (if keeping OSON bytes until the very last time)
  • for example, you may do: [database] -> [client: JDBC driver -> OracleJsonObject -Jackson API-> POJO]

[loiclefevre]

Here is a code sample that illustrates the 1st and 2nd point:

import io.vertx.core.json.Json;
import io.vertx.core.json.JsonObject;
import oracle.jdbc.OracleType;
import oracle.sql.json.OracleJsonFactory;
import oracle.sql.json.OracleJsonGenerator;
import oracle.sql.json.OracleJsonValue;

import java.io.ByteArrayOutputStream;
import java.io.StringReader;
import java.sql.*;
import java.time.LocalDateTime;

public class TestOSON {
    private static final OracleJsonFactory JSON_FACTORY = new OracleJsonFactory();

    static void main() {
        ByteArrayOutputStream out = new ByteArrayOutputStream();

        try (Connection c = DriverManager.getConnection("jdbc:oracle:thin:developer/free@localhost/freepdb1")) {
            JsonObject json = new JsonObject()
                    .put("name", "Alice")
                    .put("age", 3)
                    .put("birthdate", "2023-09-21T10:00:00Z");

            OracleJsonValue v = JSON_FACTORY.createJsonTextValue(new StringReader(Json.encode(json)));

            // What the JDBC extension + OSON encoding for Jackson API can do: 
            try (OracleJsonGenerator gen = JSON_FACTORY.createJsonBinaryGenerator(out)) {
                gen.writeStartObject();
                gen.write("name", "Alice");
                gen.write("age", 3);
                gen.write("birthdate", LocalDateTime.of(2023, 9, 21, 10, 0, 0, 0));
                gen.writeEnd();
            }

            try (Statement s = c.createStatement()) {
                s.execute("drop table if exists test_json purge");
                s.execute("create table if not exists test_json ( data json )");
            }

            try (PreparedStatement ps = c.prepareStatement("insert into test_json values (?)")) {
                ps.setObject(1, v);
                // uncomment
                // ps.setObject(1, out.toByteArray(), OracleType.JSON);
                ps.executeUpdate(); // autocommit
            }

            try (Statement s = c.createStatement()) {
                try (ResultSet rs = s.executeQuery("select dbms_lobutil.getphysicallength(oson_get_content(data)), t.data, t.data.birthdate.type() from test_json t")) {
                    if (rs.next()) {
                        System.out.println(rs.getString(1));
                        System.out.println(rs.getString(2));
                        System.out.println(rs.getString(3));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

[loiclefevre]

Output with binding as done today (dependency of the project is io.vertx:vertx-core:5.2.0):

82
{"name":"Alice","age":3,"birthdate":"2023-09-21T10:00:00Z"}
string

[loiclefevre]

When you uncomment this ps.setObject(1, out.toByteArray(), OracleType.JSON);, you get:

69
{"name":"Alice","age":3,"birthdate":"2023-09-21T10:00:00"}
timestamp

[loiclefevre]

The binary size is reduced from 82 to 69 bytes (15% savings). Mostly because we now tell how to encode the date as a SQL timestamp vs a string (remember JSON standard has less data types than databases). So the field t.data.birthdate is stored as a SQL TIMESTAMP and:

• takes less space
• is better handled by the database: no parsing from string to TIMESTAMP for every SQL query access

[loiclefevre]

And the JDBC extension + Jackson can do the job of converting properly, basically doing this for us:

            try (OracleJsonGenerator gen = JSON_FACTORY.createJsonBinaryGenerator(out)) {
                gen.writeStartObject();
                gen.write("name", "Alice");
                gen.write("age", 3);
                gen.write("birthdate", LocalDateTime.of(2023, 9, 21, 10, 0, 0, 0));
                gen.writeEnd();
            }