andersswanson.dev

Did you know Oracle AI Database supports rich, full-text search and indexing, without the need to operate an external document store? In this article, we’ll implement full-text search on Oracle’s JSON data type, following the jdbc-json-oracle-text sample hosted on GitHub. The JSON text-search sample shows how we can:

• Load and bind Oracle JSON (OSON) documents with a JSON search index using Oracle Text
• Run ranked full-text searches with json_textcontains
• Combine text relevance with regular JSON field filters

Full-text search is a great compliment to knowledge bases, document catalogs, enhanced vector search, or content lookup.

Sample overview

The sample inserts four JSON documents into a table and runs three full-text search queries:

1. A keyword search for oracle
2. A proximity search using NEAR((json, search), 3)
3. A mixed search that combines full-text search with structured filters for category and author

Using JSON documents that look like this:

{
  "title": "Oracle Text for JSON Search",
  "summary": "A hands-on lab for building ranked Oracle Text search over JSON knowledge entries.",
  "body": "The guide walks through loading API notes as JSON, creating a search index, and testing keyword plus NEAR queries for phrase-style retrieval.",
  "category": "GUIDE",
  "author": "Ava"
}

{
  "title": "Oracle Text for JSON Search",
  "summary": "A hands-on lab for building ranked Oracle Text search over JSON knowledge entries.",
  "body": "The guide walks through loading API notes as JSON, creating a search index, and testing keyword plus NEAR queries for phrase-style retrieval.",
  "category": "GUIDE",
  "author": "Ava"
}

If you want to follow along in the code as you read, the full sample is here: jdbc-json-oracle-text on GitHub.

The table, JSON column, and JSON search index

We create a small schema for storing and indexing JSON documents:

create table if not exists json_documents (
    id number generated always as identity primary key,
    search_document json not null
);

create search index if not exists json_documents_search_idx
on json_documents (search_document)
for json
parameters ('sync (on commit) search_on text include ($.title, $.summary, $.body)');

create table if not exists json_documents (
    id number generated always as identity primary key,
    search_document json not null
);

create search index if not exists json_documents_search_idx
on json_documents (search_document)
for json
parameters ('sync (on commit) search_on text include ($.title, $.summary, $.body)');

There are two details worth calling out:

1. JSON is stored in a native JSON column, not as a plain CLOB or VARCHAR2. That gives the database a better foundation for JSON-aware storage and query behavior.
2. The search index is configured to include title, summary, and body fields. In most applications, not every JSON field should participate in text search. Typically, you only index user-facing content fields and leave structural metadata alone.

The sync (on commit) setting is also a nice fit for sample code and many CRUD-style apps, because committed inserts become searchable right away.

Loading JSON into Oracle AI Database

The sample reads JSON documents from a file, maps each JSON object into a TextDocument record, then serializes each one into OSON before inserting it as a batch:

try (PreparedStatement statement = connection.prepareStatement("insert into json_documents (search_document)
values (?)")) {
    for (TextDocument document : documents) {
        statement.setObject(1, oson.toOSON(document), OracleTypes.JSON);
        statement.addBatch();
    }
    statement.executeBatch();
}

try (PreparedStatement statement = connection.prepareStatement("insert into json_documents (search_document)
values (?)")) {
    for (TextDocument document : documents) {
        statement.setObject(1, oson.toOSON(document), OracleTypes.JSON);
        statement.addBatch();
    }
    statement.executeBatch();
}

The application creates bytes in Oracle’s binary JSON format, binds them as OracleTypes.JSON, and inserts them into the table.

The OSONMapper helper is tiny, but useful. It uses JSON-B plus OracleJsonFactory to convert between Java objects and OSON, which gives the sample a clean way to round-trip JSON documents through the database.

Full-text search with json_textcontains

Once the data is loaded, the sample runs a few json_textcontains queries and orders results by SCORE. Here’s the keyword search:

select id,
       search_document,
       score(1) as score
from json_documents
where json_textcontains(search_document, '$', ?, 1)
order by score(1) desc, id

select id,
       search_document,
       score(1) as score
from json_documents
where json_textcontains(search_document, '$', ?, 1)
order by score(1) desc, id

The sample binds a search string, runs the query, and deserialize each matched JSON document back into Java. If you’re coming from document-store search APIs, this feels pretty natural.

The sample prints the text ranking score and explicitly explains that Oracle Text SCORE is a relevance ranking for the current query, not a percentage.

Proximity search with NEAR

The second query uses:

NEAR((json, search), 3)

NEAR((json, search), 3)

This is where Oracle Text starts to feel like a real search feature and less like a glorified LIKE clause.

This query asks whether json and search occur within three tokens of each other. For document retrieval use cases, this kind of proximity logic is often much closer to what users actually mean when they type a search phrase..

Mixing full-text and structured filters

Pure keyword search is useful, but most production systems need more than that. You rarely want “all documents matching this term”. You usually want “all matching documents in this category, created by this team, with this lifecycle state”.

The sample shows that pattern by combining json_textcontains with json_value filters:

select id,
       search_document,
       score(3) as score
from json_documents
where json_textcontains(search_document, '$', ?, 3)
  and json_value(search_document, '$.category' returning varchar2(30)) = ?
  and json_value(search_document, '$.author' returning varchar2(30)) = ?
order by score(3) desc, id

select id,
       search_document,
       score(3) as score
from json_documents
where json_textcontains(search_document, '$', ?, 3)
  and json_value(search_document, '$.category' returning varchar2(30)) = ?
  and json_value(search_document, '$.author' returning varchar2(30)) = ?
order by score(3) desc, id

Oracle Text handles the text queries, and regular SQL/JSON filters handle the structured constraints:

• use full-text search for relevance
• use JSON field predicates for precision

That combination is usually where JSON search starts feeling application-ready.

Running the sample with Testcontainers

You can run the sample locally, using Oracle AI Database Free and testcontainers. This requires Java 21+, Maven, and a Docker-compatible environment.

From the module directory, you can run:

mvn test

mvn test

If you want the complete project files, including the schema, sample documents, Java code, and integration test, they are all in the GitHub source for jdbc-json-oracle-text.

Final Thoughts

There are flashier Oracle AI Database features to talk about, but this is the kind of sample that solves a real day-to-day problem:

A lot of teams have semi-structured content sitting in JSON already. They want to keep the flexibility of documents, but they also want:

• native storage
• proper indexing
• ranked full-text search
• normal database driver (including JDBC) access
• the ability to mix text and structured predicates in one query

If you want to run it or adapt it, start with the source here: jdbc-json-oracle-text on GitHub.