How to Store Vectors in JSON Documents in Oracle

Applications built on JSON documents, through SODA, the MongoDB API, or REST, often keep their embeddings in a separate vector database. In Oracle AI Database 26ai they can live inside the documents: the JSON type has a native scalar type for vectors, as it has for dates and numbers, and a vector in a document stays a binary vector, not a string of digits.

This guide shows vector values in JSON documents, a JSON collection table searched by meaning, and a JSON relational duality view that presents rows, their comments, and their embeddings as documents without storing anything twice.

Code for This Guide

The examples are files 04 to 06 in the examples/ch15 folder of the Oracle AI code repository on GitHub, each with its output.

These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.

They use the tickets and comments of the sample schema, with embeddings from how to generate embeddings in SQL with VECTOR_EMBEDDING.

A Vector Inside a JSON Document

This example builds a document with a vector, shows it in extended JSON, the JSON text form that keeps the types of values, and reads the vector back with JSON_VALUE.

Example:

-- a vector inside a JSON document is a vector, not text
select json_serialize(
         json_object('ticket' value 9, 'topics' value vector('[0.1, 0.9, 0.15]')
                     returning json) extended) as document
from   dual;

select json_value(doc, '$.topics.type()') as json_type,
       json_value(doc, '$.topics' returning vector) as topics
from  (select json_object('topics' value vector('[0.1, 0.9, 0.15]') returning json) as doc
       from   dual);

Output:

DOCUMENT
_____________________________________________________________________________________________
{"ticket":9,"topics":{"$vector":[1.0E-01,9.0E-01,1.5E-01],"$vectorElementType":"float32"}}

JSON_TYPE    TOPICS
____________ ____________________________________________________
vector       [1.00000001E-001,8.99999976E-001,1.50000006E-001]

Search a JSON Collection by Meaning

JSON collection tables store the documents of SODA, MongoDB API, and REST applications. This example creates one with a document per ticket, its embedding included, and searches it by meaning.

Example:

-- a JSON collection: one document per ticket, with its embedding
create json collection table ticket_docs;

insert into ticket_docs
select json_object('_id' value ticket_id, 'subject' value subject, 'status' value status,
                   'embedding' value embedding returning json)
from   tickets;
commit;

select json_value(d.data, '$.subject') as subject,
       round(vector_distance(json_value(d.data, '$.embedding' returning vector),
             vector_embedding(all_minilm_l12_v2 using 'charged twice' as data), cosine), 3)
         as distance
from   ticket_docs d
order  by distance
fetch  first 3 rows only;

Output:

JSON collection table TICKET_DOCS created.

400 rows inserted.

Commit complete.

SUBJECT                               DISTANCE
__________________________________ ___________
Charged twice this month                 0.381
Duplicate charge on credit card          0.413
Duplicate charge on credit card          0.414

The search is the same as on a relational table: JSON_VALUE takes the vector out of each document, and VECTOR_DISTANCE compares it. Documents and their embeddings live together, and one query searches them.

Build the documents by selecting the vector as a column of the query, as above. In a test on Oracle AI Database 26ai Free, putting a scalar subquery inside JSON_OBJECT instead, such as 'embedding' value (select embedding from tickets where ...), raised ORA-00600 and ended the session.

Present Rows as Documents with a Duality View

A JSON relational duality view shows relational tables as JSON documents, and updates the tables when the documents change. TICKET_DV presents each ticket with its embedding and its comments as one document.

Example:

-- a duality view: tickets and their comments as documents, stored in the relational tables
create json relational duality view ticket_dv as
select json {'_id'      : t.ticket_id,
             'subject'  : t.subject,
             'status'   : t.status,
             'embedding': t.embedding,
             'comments' : [select json {'commentId': c.comment_id,
                                        'author'   : c.author_type,
                                        'body'     : c.body}
                           from   ticket_comments c with insert update delete
                           where  c.ticket_id = t.ticket_id]}
from   tickets t with update;

-- the document of ticket 15, without its embedding
select json_serialize(json_transform(data, remove '$.embedding', remove '$._metadata')
                      returning clob pretty) as document
from   ticket_dv
where  json_value(data, '$._id') = 15;

Output:

View TICKET_DV created.

DOCUMENT
__________________________________________________________________________________________________
{
  "_id" : 15,
  "subject" : "Duplicate charge on credit card",
  "status" : "Resolved",
  "comments" :
  [
    {
      "commentId" : 16,
      "author" : "Agent",
      "body" : "The second charge was a retry after a timeout at the payment gateway. I have
refunded it; banks show the refund within 5 to 10 business days. See article KB-201."
    }
  ]
}

The document is assembled from TICKETS and TICKET_COMMENTS when it is read; nothing is stored twice. The query removes the embedding and metadata only to keep the output short. An application that works with documents gets each ticket, its conversation, and its embedding in one read, while the embedding stays in the relational column, where vector indexes can serve it.

Which to Use

Vector search on relational columns is covered in how to build semantic search in Oracle Database.

Conclusion

Oracle's JSON type stores vectors natively, shown as $vector in extended JSON and read with JSON_VALUE ... RETURNING VECTOR, so documents and their embeddings can live together and be searched with VECTOR_DISTANCE. Use JSON collection tables for document-first applications, and duality views to give document clients tickets, comments, and embeddings in one read while the data stays relational and indexable.