JSON_TABLE is one of Oracle Database’s most useful—yet often overlooked—SQL/JSON functions. Available across database editions and versions, it lets you project JSON content into relational columns with standard SQL data types. In practice, JSON_TABLE maps selected parts of a JSON document into the rows and columns of a virtual table (think: an inline view), making JSON data easy to join, filter, aggregate, and report on using familiar SQL.
JSON_TABLE is well documented and there are plenty of examples online. If you want to refresh your knowledge, these references are a good starting points:
I keep coming back to JSON_TABLE when experimenting with Oracle AI Database 26ai features—especially when I want relational output instead of raw JSON. It’s one of my preferred approaches for turning JSON responses into something immediately queryable and presentable in SQL.
In this post, I’ll demonstrate JSON_TABLE alongside three powerful Oracle AI Database 26ai capabilities. You’ll see not only what JSON_TABLE can do, but also how these newer features can be used in practical examples:
- Developer-friendly metadata retrieval for database objects using DBMS_DEVELOPER
- Ubiquitous Database Search
- Hybrid Vector Search
Now, let’s start with the code examples.
1. User-friendly metadata retrieval for database objects using DBMS_DEVELOPER
DBMS_DEVELOPER offers a developer-friendly PL/SQL API for retrieving metadata about database objects. Its GET_METADATA function accepts object identifier (schema, object type, name, and level) and returns a JSON document with the object’s metadata.
Related documentation and posts:
Here’s a simple example and a shortened snippet of the JSON result. I use JSON_SERIALIZE … PRETTY to return a human-readable representation.
SELECT JSON_SERIALIZE(
DBMS_DEVELOPER.GET_METADATA
(schema => 'SCOTT',
object_type => 'TABLE',
name => 'EMP',
level => 'BASIC') returning clob pretty) result;
RESULT
--------------------------------------------------------------------------------
{
"objectType" : "TABLE",
"objectInfo" :
{
"name" : "EMP",
"schema" : "NATIONALPARKS",
"columns" :
[
{
"name" : "EMPNO",
"notNull" : true,
"dataType" :
{
"type" : "NUMBER",
"precision" : 4
}
},
{
"name" : "ENAME",
"notNull" : false,
"dataType" :
{
"type" : "VARCHAR2",
"length" : 10,
"sizeUnits" : "BYTE"
}
},Example 1: extract scalar properties
Next, let’s extract the object and schema name as relational columns using JSON_TABLE. In the FROM clause, JSON_TABLE projects the JSON object at '$."objectInfo"' into a virtual row, and the COLUMNS clause maps individual JSON properties to return the name and schema property values in the VARCHAR2(100) columns OBJECTNAME and OBJECTSCHEMA.
SELECT h.objectname, h.objectschema schema
FROM JSON_TABLE(
(SELECT DBMS_DEVELOPER.GET_METADATA
(schema => 'SCOTT',
object_type => 'TABLE',
name => 'EMP',
level => 'BASIC' )),'$."objectInfo"'
COLUMNS
(objectname VARCHAR2(100) PATH '$."name"',
objectschema VARCHAR2(100) PATH '$."schema"')) as h;
OBJECTNAME SCHEMA
---------- --------------------
EMP SCOTT
Example 2: project the COLUMNS array
A JSON value can be an array or can include one or more arrays, nested to any number of levels inside other JSON arrays or objects.
SELECT h.columnname, h.datatype, h.datatypejson
FROM JSON_TABLE(
(SELECT DBMS_DEVELOPER.GET_METADATA
(schema => 'SCOTT',
object_type => 'TABLE',
name => 'EMP',
level => 'BASIC' )), '$."objectInfo"."columns"[*]'
COLUMNS
(columnname VARCHAR2(100) PATH '$."name"',
datatype VARCHAR2(20) PATH '$."dataType"."type"',
datatypejson JSON PATH '$."dataType"' )) as h;
COLUMNNAME DATATYPE DATATYPEJSON
---------- -------------------- -------------------------------------------------------
EMPNO NUMBER {"type":"NUMBER","precision":4}
ENAME VARCHAR2 {"type":"VARCHAR2","length":10,"sizeUnits":"BYTE"}
JOB VARCHAR2 {"type":"VARCHAR2","length":9,"sizeUnits":"BYTE"}
MGR NUMBER {"type":"NUMBER","precision":4}
HIREDATE DATE {"type":"DATE"}
SAL NUMBER {"type":"NUMBER","precision":7,"scale":2}
COMM NUMBER {"type":"NUMBER","precision":7,"scale":2}
DEPTNO NUMBER {"type":"NUMBER","precision":2}
Example 3: NESTED PATH (parent + child rows)
In this final example for DBMS_DEVELOPER, I’ll show how to use a NESTED PATH in JSON_TABLE to flatten a parent object together with a child array. The COLUMNS clause extracts the table name into a VARCHAR2(100) column called OBJECTNAME. The NESTED PATH clause then iterates over the COLUMNS array and returns one row per column, projecting each column’s name and data type. As a result, OBJECTNAME is repeated on every row, paired with the corresponding column metadata COLNAME and COLTYPE.
SELECT h.objectname, h.colname, h.coltype
FROM JSON_TABLE(
(select DBMS_DEVELOPER.GET_METADATA
(schema => 'SCOTT',
object_type => 'TABLE',
name => 'EMP',
level => 'BASIC' )), '$."objectInfo"'
COLUMNS
(objectname VARCHAR2(100) PATH '$."name"',
NESTED PATH '$."columns"[*]'
COLUMNS (colname VARCHAR2(100) PATH '$."name"',
coltype VARCHAR2(100) PATH '$."dataType"."type"'))) as h;
OBJECTNAME COLNAME COLTYPE
---------- ---------- --------------------
EMP EMPNO NUMBER
EMP ENAME VARCHAR2
EMP JOB VARCHAR2
EMP MGR NUMBER
EMP HIREDATE DATE
EMP SAL NUMBER
EMP COMM NUMBER
EMP DEPTNO NUMBER
8 rows selected.
Now let’s continue with examples on search.
2. Ubiquitous Database Search
DBMS_SEARCH is the PL/SQL API behind Ubiquitous Database Search. It lets you build one search index over multiple objects in a schema (tables, views, and JSON Duality Views), so you can run a single full-text (and range-based) query across all of them. What’s new? Traditionally, using Oracle Text for more complex use cases—especially when you need to search across multiple tables or views—often meant designing and maintaining multiple indexes, wiring them together in the application, or building custom indexing pipelines (for example, with USER_DATASTORE procedures). That can quickly become hard to manage.
With Ubiquitous Database Search, Oracle Database simplifies this dramatically.
- One index, multiple sources
- Simpler administration
- Less custom programming
- Automatic maintenance
Technically, a ubiquitous search index is a JSON search index with a predefined set of preferences and settings optimized for searching across multiple objects.
In the following example, I use schema CO and add tables SHIPMENTS and STORES as sources, then run a single Oracle Text query across both.
(Full walkthrough: Related post: Full-text search across multiple objects: Ubiquitous Database Search)
1. One index , multiple sources
Create the index.
execute DBMS_SEARCH.CREATE_INDEX(index_name=>'SEARCH_PRODUCTS');
It creates the infrastructure for Oracle Text including a table SEARCH_PRODUCTS with two JSON columns DATA and METADATA. Describe the table.
desc SEARCH_PRODUCTS
Name Null? Type
-------- -------- --------------
METADATA NOT NULL JSON
DATA JSON
OWNER VARCHAR2(128)
SOURCE VARCHAR2(128)
KEY VARCHAR2(1024) 2. Add the sources SHIPMENTS and STORES
execute DBMS_SEARCH.ADD_SOURCE(index_name =>'SEARCH_PRODUCTS', source_name => 'SHIPMENTS');
execute DBMS_SEARCH.ADD_SOURCE(index_name =>'SEARCH_PRODUCTS', source_name => 'STORES');3. What you get back
Inspect what the index stores.
SELECT * FROM search_products WHERE rownum=1;
METADATA DATA OWNER SOURCE KEY
-------------------------------------------------------------------------------- ---------- ---------- -------------------- --------------------------------------
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpDAA1"}} null CO SHIPMENTS {"ROWID":"AAAoQsAAAAACNpDAA1"}
You’ll notice: METADATA and KEY are JSON documents that identify where the result came from (owner/source and row identifier) and DATA is null.
4. Query the index (Oracle Text)
Example: find terms similar to “Los” using “FUZZY”:
SELECT metadata, source output
FROM SEARCH_PRODUCTS
WHERE contains(data,'fuzzy(Los)') > 0;
METADATA SOURCE
-------------------------------------------------------------------------------- --------------------
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpKAB5"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpKAB6"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpLAAC"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpLAAD"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpMABZ"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpMABa"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpHAAL"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpHAAM"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpHABT"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpHABU"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpNAA9"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpIABs"}} SHIPMENTS
{"OWNER":"CO","SOURCE":"STORES","KEY":{"ROWID":"AAAoQgAAAAACNoDAAJ"}} STORES This returns hits from multiple sources (e.g., SHIPMENTS and STORES). The key point: the result tells you which source matched and provides a key you can use to fetch full rows from the original table.
5. Why JSON_TABLE?
If you filter to source STORES or SHIPMENTS, you can extract the ROWID from the JSON metadata and join back to the original tables. Here is an example with table SHIPMENTS.
SELECT s.delivery_address, s.shipment_id
FROM SHIPMENTS s
JOIN JSON_TABLE(
(SELECT JSON_ARRAYAGG(metadata)
FROM SEARCH_PRODUCTS
WHERE CONTAINS(DATA, 'fuzzy(Los)') > 0 AND source = 'SHIPMENTS')
,'$[*]'
COLUMNS (row_id VARCHAR2(18) PATH '$.KEY.ROWID')) j ON s.ROWID = j.row_id;
DELIVERY_ADDRESS SHIPMENT_ID
-------------------------------------------------------- -----------
Los Angeles, CA 90026 USA 1806 Los Angeles, CA 90026 USA 1807 ...Why aggregation may be needed: when your search returns multiple rows, you often need to aggregate results into a single JSON array (e.g., JSON_ARRYAGG) before applying JSON_TABLE to get one relational row per hit.
SELECT JSON_ARRAYAGG(metadata)
FROM SEARCH_PRODUCTS
WHERE CONTAINS(data, 'fuzzy(Los)') > 0 AND source = 'SHIPMENTS';
JSON_ARRAYAGG(METADATA) -------------------------------------------------------------------------------------------------
[{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpKAB5"}},{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpKAB6"}},{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpLAAC"}}, ...
...
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpNAA9"}},{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"ROWID":"AAAoQsAAAAACNpIABs"}}]
3. Hybrid Vector Search
Oracle Database 26ai includes Oracle AI Vector Search, designed for AI workloads where users search by meaning (semantics) rather than exact keywords. One major advantage is that you can combine semantic search over unstructured text with classic relational filtering on business data—within the same database.
But many real-world searches can’t rely on semantics alone. Users often have explicit keyword requirements (“must contain / must not contain”), and in some scenarios precision and determinism matter. This is where Oracle Text complements vector search.
What is Hybrid Vector Search?
Hybrid Vector Search combines:
-
- Vector semantic similarity (high recall: find meaningfully related results even when wording differs), and
-
- Oracle Text keyword search (high precision: enforce lexical constraints and exact terms)
Use hybrid search when you want semantic relevance and keyword constraints—for example:
-
- queries that must include specific terms (product names, error codes, regulated phrasing),
-
- support and troubleshooting workflows where false positives are costly, and
-
- scenarios where you want stronger ranking by blending lexical and semantic scoring.
A hybrid vector index provides a single, unified query interface: you can run text-only, vector-only, or hybrid searches through the same API.
Example setup: PARKS indexed with a hybrid vector index
In this tutorial, I use the table PARKS (from the LiveLabs “Getting Started with AI Vector Search” dataset). The table contains a column DESCRIPTION and a corresponding vector column DESC_VECTOR. A hybrid vector index has already been created.
Note: The following example is intentionally shortened to highlight the key concepts. For a complete, end-to-end walkthrough (including setup and index creation), see the posting Hybrid Vector Index – a combination of AI Vector Search with Text Search.
desc parks
Name Null? Type
--------------- -------- -------------------
PARK_ID NOT NULL VARCHAR2(36)
PARK_CODE VARCHAR2(4)
URL VARCHAR2(34)
NAME NOT NULL VARCHAR2(51)
DESCRIPTION VARCHAR2(414)
LATITUDE NUMBER(11,8)
LONGITUDE NUMBER(11,8)
LOCATION PUBLIC.SDO_GEOMETRY
DIRECTIONS_INFO VARCHAR2(524)
DIRECTIONS_URL VARCHAR2(162)
CONTACT_PHONE VARCHAR2(13)
EMAIL_ADDRESS VARCHAR2(44)
STREET VARCHAR2(95)
CITY VARCHAR2(35)
STATES VARCHAR2(2)
POSTAL_CODE VARCHAR2(10)
COUNTRY_ID NOT NULL VARCHAR2(3)
DESC_VECTOR VECTOR
SELECT count(*) from parks;
COUNT(*)
----------
472
SELECT index_name, column_name FROM user_ind_columns WHERE table_name ='PARKS';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
PARKS_HYBRID_IDX DESCRIPTION
SELECT idx_name, idx_table, idx_status, idx_docid_count, idx_maintenance_type,
idx_model_name, idx_vector_type
FROM ctx_user_indexes;
IDX_NAME IDX_TABLE IDX_STATUS IDX_DOCID_COUNT IDX_MA IDX_MODEL_NAME IDX_VECTOR_TYPE
-------------------- -------------------- ------------ --------------- ------ -------------------- -----------------------
PARKS_HYBRID_IDX PARKS INDEXED 472 MANUAL MINILM_L12_V2 IVF
Querying the hybrid vector index
Oracle provides the JSON-based function DBMS_HYBRID_VECTOR.SEARCH. You submit a JSON request that can include:
-
- a vector clause (semantic search),
-
- a text clause (Oracle Text),
-
- and a fusion strategy that defines how results are combined.
In the next example, I use INTERSECT, which returns only rows that match both:
-
- the keyword constraint (“Picnic”) and
-
- the semantic intent (e.g. “swimming”)
I also request the SCORE, ROWID, and text snippet CHUNK_TEXT for the top-N results:
SELECT JSON_SERIALIZE(JSON(
DBMS_HYBRID_VECTOR.SEARCH(JSON
(
'{"hybrid_index_name": "parks_hybrid_idx", "vector": {"search_text": "swimming", "score_weight": "1"}, "text": {"contains": "Picnic", "score_weight": "1", "snippet": "250"}, "search_fusion": "INTERSECT", "return":{"values": ["score", "rowid", "chunk_text"],"topN": "40"} }'
)))) result; RESULT ------------------------------------------------------------------------------------------------- [{"score":34.63,"rowid":"AAAmtuAAAAABeb/AAG","chunk_text":"Today the river valley attracts us for so many reasons. Take a solitary walk to enjoy nature's display, raft leisurely through the rocky shoals with friends, fish the misty waters as the sun comes up, or have a picnic on a Sunday afternoon. Get Outdoors and experience your Chattahoochee River National Recreation Area as you have never done before."},{"score":31.97,"rowid":"AAAmtuAAAAAB0OvAAF","chunk_text":"Experience a park so rich it supports 19 distinct ecosystems with over 2, 000 plant and animal species. Go for a hike, enjoy a vista, have a picnic or learn about the centuries of overlapping history from California's indigenous cultures, Spanish colonialism, the Mexican Republic, US military expansion and the growth of San Francisco. All of this and more awaits you, so get out and find your park."}]
Making results readable with JSON_TABLE
To turn the JSON response into relational rows, JSON_TABLE projects the array into column ROW_ID. We can then join back to PARKS to display business columns like the park name:
SELECT p.name, h.chunk_text
FROM parks p,
JSON_TABLE (
(SELECT JSON(DBMS_HYBRID_VECTOR.SEARCH(JSON(
'{ "hybrid_index_name": "parks_hybrid_idx",
"vector": {"search_text": "swimming", "score_weight": "1"},
"text": {"contains": "Picnic", "score_weight": "1", "snippet": "250"},
"search_fusion": "INTERSECT",
"return": {"values": ["score", "rowid", "chunk_text"], "topN": "10"}
}'
)))), '$.[*]'
COLUMNS
(score NUMBER(5,2) PATH '$."score"',
chunk_text VARCHAR2(400) PATH '$."chunk_text"',
row_id VARCHAR2(18) PATH '$."rowid"')) h
WHERE p.rowid=h.row_id;
NAME
------------------------------------------------------------------------------------------------CHUNK_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Chattahoochee River Today the river valley attracts us for so many reasons. Take a solitary walk to enjoy nature's display, raft leisurely through the rocky shoals with friends, fish the misty waters as the sun comes up, or have a picnic on a Sunday afternoon. Get Outdoors and experience your Chattahoochee River National Recreation Area as you have never done before.
Golden Gate Experience
a park so rich it supports 19 distinct ecosystems with over 2, 000 plant and animal species. Go for a hike, enjoy a vista, have a picnic or learn about the centuries of overlapping history from California's indigenous cultures, Spanish colonialism, the Mexican Republic, US military expansion and the growth of San Francisco. All of this and more awaits you, so get out and find your park.
This pattern—call a JSON-returning AI/search API, then flatten (relationalize) it with JSON_TABLE —is exactly where JSON_TABLE shows its advantages: it makes advanced JSON-based APIs immediately usable in standard SQL workflows.
Conclusion
Across these three examples using DBMS_DEVELOPER, DBMS_SEARCH, and DBMS_HYBRID_VECTOR – JSON_TABLE acts as the bridge between JSON-first APIs and the relational SQL output we often want for reporting, joining, filtering, and presenting results.
Resources
-
- PL/SQL Packages and Types Reference: DBMS_DEVELOPER (documentation)
-
- More Examples on Hybrid Vector Search (Blog posting)
-
- Oracle AI Vector Search User’s Guide DBMS_HYBRID_VECTOR (documentation)