# Package oracle.sql.json

The API for JSON type in Oracle Database.

This package contains classes and interfaces for working with SQL JSON type values. Use this package to:

- Store and retrieve JSON type values in the database
- Read, create, and modify JSON type values
- Encode/decode JSON type values in the same binary JSON storage format used by the database
- Convert JSON type values to and from JSON text
- Access JSON type values using JSON-P interfaces

The package contains three components:

Description | Classes/interfaces
--- | ---
JSON type object-model | `OracleJsonValue`, `OracleJsonObject`, `OracleJsonArray`, `OracleJsonString`, `OracleJsonDecimal`, `OracleJsonDouble`, `OracleJsonFloat`, `OracleJsonTimestamp`, `OracleJsonTimestampTZ`, `OracleJsonDate`, `OracleJsonBinary`, `OracleJsonIntervalDS`, and `OracleJsonIntervalYM`.
JSON type event-stream reader and writer | `OracleJsonParser` `OracleJsonGenerator`
Factory for reading, writing, and creating JSON type values | `OracleJsonFactory`

The following example shows how to insert, get, and modify JSON type values.

    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    import oracle.jdbc.OracleConnection;
    import oracle.jdbc.pool.OracleDataSource;
    import oracle.sql.json.OracleJsonFactory;
    import oracle.sql.json.OracleJsonObject;

    public class JsonExample {

      public static void main(String[] args) throws SQLException {
        OracleDataSource ds = new OracleDataSource();
        ds.setURL("jdbc:oracle:thin:@myhost:1521:orcl");
        ds.setUser("SCOTT");
        ds.setPassword("tiger");
        OracleConnection con = (OracleConnection) ds.getConnection();

        // create a table with a JSON column and insert one value
        Statement stmt = con.createStatement();
        stmt.executeUpdate("CREATE TABLE fruit (data JSON)");
        stmt.executeUpdate("INSERT INTO fruit VALUES ('{"name":"pear","count":10}')");

        // create another JSON object
        OracleJsonFactory factory = new OracleJsonFactory();
        OracleJsonObject orange = factory.createObject();
        orange.put("name", "orange");
        orange.put("count", 12);

        // insert the orange
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO fruit VALUES (:1)");
        pstmt.setObject(1, orange, OracleType.JSON);
        pstmt.executeUpdate();
        pstmt.close();

        // select the pear
        ResultSet rs = stmt.executeQuery("SELECT data FROM fruit f WHERE f.data.name = 'pear'");
        rs.next();
        OracleJsonObject pear = rs.getObject(1, OracleJsonObject.class);
        int count = pear.getInt("count");

        // create a modifiable copy of the pear
        pear = factory.createObject(pear);
        pear.put("count", count + 1);
        pear.put("color", "green");

        // update the pear
        pstmt = con.prepareStatement("UPDATE fruit f SET data = :1 WHERE f.data.name = 'pear');
        pstmt.setObject(1, pear, OracleType.JSON);
        pstmt.executeUpdate();
        pstmt.close();

        rs.close();
        stmt.close();
        con.close();
      }
    }

In this example, `pstmt.setObject(...)` is called to set a parameter to a JSON value and `rs.getObject(...)` is called to get the value of a JSON type column.

# Storing and Retrieving JSON

The following methods in JDBC can accept and return JSON values:

Methods that support JSON type
`java.sql.ResultSet` | `getObject(int, Class<T>)` `getObject(String, Class<T>)` `updateObject(int, Object)` `updateObject(String, Object)`
`java.sql.PreparedStatement` | `setObject(String, Object, int)` `setObject(String, Object, SQLType)`
`java.sql.CallableStatement` | `getObject(int, Class<T>)` `getObject(String, Class<T>)` `setObject(String, Object, int)` `setObject(String, Object, SQLType)`
`javax.sql.RowSet` | `setObject(int, Object, int)` `setObject(String, Object, int)`

Methods that accept JSON values (e.g. `setObject(...)`) support instances of the following Java types:

Class | Description
--- | ---

`java.lang.String`
`java.lang.CharSequence`
`java.io.Reader`

A JSON text value. For example:

      stmt.setObject(1, "{\"hello\":\"world\"}", OracleType.JSON);

`java.io.InputStream` `byte[]` | Either a JSON text value (UTF8, UTF16, etc) or Oracle binary JSON (see below).

`oracle.sql.json.OracleJsonValue`
`jakarta.json.JsonValue`
`javax.json.JsonValue` (deprecated)

A JSON object-model value. This includes any value derived from `OracleJsonValue` such as `OracleJsonObject` and `OracleJsonArray`. For example:

      OracleJsonFactory factory = new OracleJsonFactory();
      OracleJsonArray arr = factory.createArray();
      arr.add("hello");
      arr.add("world");
      stmt.setObject(1, arr, OracleType.JSON);

JSON-P interfaces in `javax.json` and `jakarta.json` are also supported.

`oracle.sql.json.OracleJsonParser` `jakarta.json.stream.JsonParser` `javax.json.stream.JsonParser` (deprecated) | A JSON event stream.
`oracle.sql.json.OracleJsonDatum` | Container for Oracle binary JSON.

Use either `OracleType.JSON` or `OracleTypes.JSON` to specify the input is JSON, as shown in the above examples.

Methods that return JSON values (e.g. `getObject(...)`) support the following Java types:

Class | Description
--- | ---

`java.lang.String`
`java.io.Reader`

The JSON type value is returned as JSON text. For example:

      String json = resultSet.getObject(1, String.class);

`java.io.InputStream` | The JSON type value is returned as UTF8 JSON text.

`oracle.sql.json.OracleJsonValue`
`jakarta.json.JsonValue`
`javax.json.JsonValue` (deprecated)

The JSON type value is returned as `OracleJsonValue`. Any derived interface, such as `OracleJsonObject` and `OracleJsonArray`, may also be used.

      OracleJsonArray arr = resultSet.getObject(1, OracleJsonArray.class);

JSON objects and arrays returned will reference the underlying Oracle binary JSON (see below) directly and will be immutable. To make a mutable copy of a returned object or array, use `OracleJsonFactory.createObject(OracleJsonObject)` and `OracleJsonFactory.createArray(OracleJsonArray)` respectively. JSON-P interfaces (`javax.json` and `jakarta.json`) may also be used - see `OracleJsonValue.wrap(Class)`.

`oracle.sql.json.OracleJsonParser` `jakarta.json.stream.JsonParser` `javax.json.stream.JsonParser` (deprecated) | The JSON type value is returned as an event stream.
`oracle.sql.json.OracleJsonDatum` | Use this to directly access the underlying Oracle binary JSON.

# JSON Extended Types

A JSON text can only contain objects, arrays, strings, numbers, true, false, and null. However, a JSON type value from Oracle Database is extended to support additional SQL types. Specifically:

- `JSON numbers` are broken down into the three SQL number types: `NUMBER`, `BINARY_DOUBLE`, and `BINARY_FLOAT`.
- Oracle JSON type values can contain the SQL primitives `TIMESTAMP`, `TIMESTAMPTZ`, `DATE`, `RAW`, `INTERVALDS`, and `INTERVALYM`.

For more information, see `OracleJsonValue`.

# Oracle Binary JSON

This package also provides facilities for converting JSON values to and from Oracle binary JSON. Oracle binary JSON is the encoding format used by Oracle Database to store JSON type values. It is an indexed format that supports efficient random access within JSON objects and arrays. This enables JSON type values to be read in-place without need to copy the data to other in-memory structures such as hash tables. It also provides a richer type-system than JSON text, allowing SQL types such as timestamp, date, intervals, and raw binary to be stored within JSON type values.

JSON values obtained from the database are implicitly mapped from Oracle binary JSON and values sent to the database are implicitly encoded to binary JSON. For example, in the previous example, `orange` is directly encoded as binary JSON before being sent to the database. Later when `pear` is retrieved from the database, the binary JSON value is accessed in-place by the call to `pear.getInt("count")`. Direct control over conversions to and from binary JSON is also possible. See `OracleJsonFactory` for more information.

JSON type values can also be set and get as JSON text (for example, using methods like `ResultSet.getString(int)` and `PreparedStatement.setString(int, String)`). The JDBC driver or the database will convert JSON text to and from Oracle binary JSON. However, avoiding JSON text conversions is ideal as parsing and generation of JSON text can be expensive and will also cause some nested SQL types to be rendered as JSON strings. When setting JSON as text, use the `setObject()` methods described above, rather than `setString()`, to ensure the JSON text can be encoded in the JDBC driver. The JDBC driver can only perform the binary encoding when the type is known to be JSON, as indicated by setting the type parameter of `setObject()` to `OracleType.JSON`.

# Integration with JSON-P (javax.json)

JSON-P is a Java API to parse and generate JSON text and is defined by JSR 374: Java API for JSON Processing 1.1. The interfaces in `oracle.sql.json` are similar to ones found in JSON-P but there are two key differences:

- The Oracle JSON type API supports an extended set of `primitive types` that includes SQL timestamp, date, raw binary, etc. JSON-P only supports the standard JSON primitives string, number, true, false, and null.
- `oracle.sql.json.OracleJsonObject` and `oracle.sql.json.OracleJsonArray` may be mutable while `javax.json.JsonObject` and `javax.json.JsonArray` are always immutable.

In some cases it may be to desirable read and write JSON type values using JSON-P interfaces. For example, if the consuming application is already built on JSON-P or if you want to ensure your application uses standard JSON types. In general, JDBC supports consuming 3rd-party implementations of JSON-P by methods like `PreparedStatement.setObject(int, Object)` and also supports wrapping values as `javax.json` interfaces. For more information see `OracleJsonValue.wrap(Class)`, `OracleJsonGenerator.wrap(Class)` and `OracleJsonParser.wrap(Class)`.

JSON-P deprecation notice: Starting with version 2.0, JSON-P is part of Jakarta EE 9 and it has been repackaged from `javax.json` to `jakarta.json`. Since Oracle Database JDBC Release 23c both JSON-P 1.0 (`javax.json`) and JSON-P 2.0 (`jakarta.json`) are supported. However, support for `javax.json` is deprecated and may be removed in a future release.

# Dependencies

JDBC depends on the JSON-P API. However, typically JSON-P is only required to be in the classpath when an application directly binds or gets JSON type values using JSON-P interfaces. For example: `rs.getObject(1, javax.json.JsonObject)`

See Also:
  Java API for JSON Processing (JSON-P)

Interface Summary
OracleJsonArray: A JSON array (an ordered sequence of zero or more values).
OracleJsonBinary: A raw binary value (in any format) stored within JSON data.
OracleJsonDate: A SQL/JSON DATE value.
OracleJsonDecimal: A SQL/JSON fixed decimal value.
OracleJsonDouble: A 64-bit, double-precision floating-point number.
OracleJsonFloat: A 32-bit, single-precision floating-point number.
OracleJsonGenerator: Writes a JSON type value to an output source.
OracleJsonIntervalDS: A SQL/JSON time interval in days, hours, minutes, and seconds.
OracleJsonIntervalYM: A SQL/JSON time interval in years and months.
OracleJsonNumber: Super type of `OracleJsonDecimal`, `OracleJsonDouble`, and `OracleJsonFloat`.
OracleJsonObject: A JSON object (an unordered collection of zero or more key/value pairs).
OracleJsonParser: Reads a JSON type value from an input source as a stream of events.
OracleJsonString: A SQL/JSON string value.
OracleJsonStructure: Super type of `OracleJsonObject` and `OracleJsonArray`.
OracleJsonTimestamp: A SQL/JSON timestamp (without a timezone).
OracleJsonTimestampTZ: A SQL/JSON timestamp (with a timezone).
OracleJsonValue: The interface for JSON type in Oracle Database.

Class Summary
OracleJsonDatum: Represents a JSON type value and holds Oracle binary JSON.
OracleJsonFactory: A factory for reading, writing, and creating SQL JSON values.

Enum Summary
OracleJsonDecimal.TargetType: Marker indicating if this value is intended to be mapped to Java int, long, or BigDecimal/BigInteger.
OracleJsonParser.Event
OracleJsonValue.OracleJsonType

Exception Summary
OracleJsonException: Indicates that a problem occurred during JSON processing.
OracleJsonGenerationException: Indicates that a problem occurred during JSON generation.
OracleJsonParsingException: Indicates that a problem occurred during JSON parsing.