For more information on what’s new in Oracle Database 23.7, see: What’s new for Developers in 23.7
Table of Contents
- JSON Data Type Modifier
- JavaScript Restricted Execution Contexts
- User-Defined Vector Distance Functions using JavaScript
JSON Data Type Modifier
You can now specify several JSON data type restrictions, which are collectively referred to as JSON type modifiers. These allow you to restrict the incoming JSON data to conform to certain characteristics without requiring a full-on JSON schema. Using these JSON-type modifiers, you can:
- Restrict what JSON structures can be stored, e.g., JSON objects or JSON arrays only
- Specify the maximum byte size of the JSON object
- For JSON arrays:
- The expected number of elements
- Whether array elements can be null
- Whether array values should be sorted in ascending order
Let’s look at some practical examples.
Restrict JSON type to objects only
Let’s assume you don’t want to allow the storage of JSON arrays but always expect objects. In simple terms, that means you will always want to see JSON enclosed in { } (JSON object) and never in [ ] (JSON array). Today, both are possible, for example:
SQL 🚀 > CREATE TABLE my_json_data (json_col JSON NOT NULL);
Table MY_JSON_DATA created.
SQL 🚀 > INSERT INTO my_json_data VALUES
('{ "name": "Gerald" }'),
('[
{ "id": 1 },
{ "id": 2 },
{ "id": 3 }
]');
2 rows inserted.
SQL 🚀 > SELECT json_col FROM my_json_data;
JSON_COL
_______________________________
{"name":"Gerald"}
[{"id":1},{"id":2},{"id":3}]
The first entry represents a JSON object with a single attribute "name" while the second entry represents a JSON array, containing three JSON objects with a single attribute "id" each. To restrain JSON arrays from being inserted, you can specify the following:
SQL 🚀 > DROP TABLE IF EXISTS my_json_data;
Table MY_JSON_DATA dropped.
SQL 🚀 > CREATE TABLE my_json_data (json_col JSON (OBJECT) NOT NULL);
Table MY_JSON_DATA created.
The only difference the CREATE TABLE statement is the addition of OBJECT to the JSON type. If you were to run the same INSERT statement again, now let’s do one at a time for demonstration purposes, you will see that the JSON object with the "name" attribute is still permitted while the JSON array is no longer permitted:
SQL 🚀 > INSERT INTO my_json_data VALUES ('{ "name": "Gerald" }');
1 row inserted.
SQL 🚀 > INSERT INTO my_json_data VALUES ('[
{ "id": 1 },
{ "id": 2 },
{ "id": 3 }
]');
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-42700: jsontype ('ARRAY') instance does not match 'JSON( OBJECT )' type modifier
Restrict the JSON byte size
Another modification you can undertake is to restrict the maximum number of bytes a JSON document can have in its binary representation. This is similar to saying VARCHAR2(25 BYTE), instead of the often-used VARCHAR2(4000 BYTE). Practically speaking, it will only be useful when the JSON data is known upfront and is known not to exceed a certain size. Realistically speaking, however, that is often not the case. Due to their flexible nature, JSON documents can be arbitrarily long. The length of a JSON document in terms of numbers of characters (including white spaces) will rarely be the same as the size of its binary representation because the binary representation does not contain characters like white spaces and also uses actual data types to represent the data, which all come with their own byte size requirement. For example, the number 12 in the JSON string representation may require 2 bytes, one for each character, but it may only need 1 byte for a data type that counts from 0 – 255.
In any case, to restrict the byte size of the JSON, you can use the LIMIT modifier, for example:
SQL 🚀 > DROP TABLE IF EXISTS my_json_data;
Table MY_JSON_DATA dropped.
SQL 🚀 > CREATE TABLE my_json_data (json_col JSON (LIMIT 50));
Table MY_JSON_DATA created.
SQL 🚀 > INSERT INTO my_json_data VALUES ('{"name": "Gerald"}');
1 row inserted.
SQL 🚀 > INSERT INTO my_json_data VALUES ('{"name": "Gerald", "company": "Oracle"}');
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-42712: JSON binary encoding exceeds JSON binary size limit 50 specified in JSON type modifier.
Only allow JSON arrays containing numbers with no NULLs and with 3 elements
In this last example, we are using the type modifiers to restrict the JSON data to only allow arrays containing 3 elements of type NUMBER, meaning that every array must have 3 elements, and they must all be valid numbers:
SQL 🚀 > DROP TABLE IF EXISTS my_json_data;
Table MY_JSON_DATA dropped.
SQL 🚀 > CREATE TABLE my_json_data (json_col JSON (ARRAY (NUMBER DISALLOW NULL, 3, SORT)));
Table MY_JSON_DATA created.
The next statement fails because it contains 4 instead of 3 array elements:
SQL 🚀 > INSERT INTO my_json_data VALUES ('[1, 2, 3, 4]');
Error at Command Line : 1 Column : 13
Error report -
ORA-42700: jsontype ('') instance does not match 'JSON( ARRAY (NUMBER, 3, SORT ) )' type modifier
The next statement fails because it includes nulls:
SQL 🚀 > INSERT INTO my_json_data VALUES ('[null, null, 3]');
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-42700: jsontype ('') instance does not match 'JSON( ARRAY (NUMBER, 3, SORT ) )' type modifier
The next statement fails becaue it includes types that a not numbers:
SQL 🚀 > INSERT INTO my_json_data VALUES ('["a", "b", "c"]');
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-42700: jsontype ('') instance does not match 'JSON( ARRAY (NUMBER, 3, SORT ) )' type modifier
The next statement succeeds because it only contains arrays with 3 numbers:
SQL 🚀 > INSERT INTO my_json_data VALUES
2 ('[1, 2, 4]'),
3 ('[1, 2, 3]'),
4* ('[2, 3, 1]');
3 rows inserted.
Remember how we said SORT in the JSON type modifier. This becomes apparent when SELECTing the data again. Because we said SORT, the database will sort the array entries in their natural order for each row, so that we get the following:
SQL 🚀 > SELECT * FROM my_json_data;
JSON_COL
___________
[1,2,4]
[1,2,3]
[1,2,3]
Looking up the JSON type modifiers
If you are wondering where the JSON type modifiers are visible, they are unfortunately not part of the DESCRIBE command:
SQL 🚀 > desc my_json_data;
Name Null? Type
___________ ________ _______
JSON_COL JSON
You can, however, find them in the json_modifier column in user_tab_cols:
SQL 🚀 > SELECT column_name, json_modifier FROM user_tab_cols WHERE table_name = 'MY_JSON_DATA';
COLUMN_NAME JSON_MODIFIER
________________________________________________ ________________________
JSON_COL JSON( ARRAY, NUMBER )
SYS_IME_OSON_81D4CFEE64634FB3BFFC44EA050EC6CF
For more information on JSON type modifiers, see Creating Tables With JSON Columns in the Oracle Database Documentation.
JavaScript Restricted Execution Contexts
Creating restricted JavaScript execution contexts using the PURE option provides users with a convenient way to limit the capabilities of JavaScript code running in the database to “pure” JavaScript computational code only. This means that JavaScript programs executing in a restricted context are guaranteed not to modify database tables or use SQL or PL/SQL functions, regardless of database privileges currently in effect. This is done by removing the predefined oracledb and session variables that are used for SQL and PL/SQL interactions. Restricted contexts provide a safety net, prohibiting unwanted database modifications, for example, when using third-party or open-source JavaScript libraries.
You can specify the PURE keyword for MLE modules, DBMS_MLE, and inline JavaScript functions/procedures. For example, for an inline JavaScript procedure, you can specify AS MLE LANGUAGE JAVASCRIPT PURE:
SQL 🚀 > CREATE OR REPLACE PROCEDURE helloWorld
AS MLE LANGUAGE JAVASCRIPT PURE
{{
console.log('Hello World, this is a "pure" JS function');
}};
/
Procedure HELLOWORLD compiled
SQL 🚀 > set serveroutput on;
SQL 🚀 > exec helloworld;
Hello World, this is a "pure" JS function
PL/SQL procedure successfully completed.
For demonstration purposes, the same functionality could be achieved by calling the DBMS_OUTPUT.PUT_LINE() PL/SQL package method:
SQL 🚀 > CREATE OR REPLACE PROCEDURE helloWorldWithPLSQL
AS MLE LANGUAGE JAVASCRIPT
{{
session.execute(
`BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World, this is a JS function calling PL/SQL');
END;
`);
}};
/
Procedure HELLOWORLDWITHPLSQL compiled
SQL 🚀 > exec helloworldwithplsql
Hello World, this is a JS function calling PL/SQL
PL/SQL procedure successfully completed.
However, when creating a MLE LANGUAGE JAVASCRIPT PURE function, the session variable is no longer available to the runtime:
SQL 🚀 > CREATE OR REPLACE PROCEDURE helloWorldWithPLSQL
AS MLE LANGUAGE JAVASCRIPT PURE
{{
session.execute(
`BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World, this is a JS function calling PL/SQL');
END;
`);
}};
/
Procedure HELLOWORLDWITHPLSQL compiled
SQL 🚀 > exec helloworldwithplsql
BEGIN helloworldwithplsql; END;
*
ERROR at line 1:
ORA-04161: ReferenceError: session is not defined
ORA-04171: at :=> (<inline-src-js>:2:4)
ORA-06512: at "GERALD.HELLOWORLDWITHPLSQL", line 1
ORA-06512: at line 1
For more information on JavaScript restricted execution contexts, see About Restricted Execution Contexts in the Oracle Database Documentation.
User-Defined Vector Distance Functions using JavaScript
Vector search operations are often based on standard distance metrics such as Euclidean, Cosine, and Dot Product. However, there are situations where domain-specific or proprietary metrics may be required. JavaScript user-defined functions can be used to define a custom vector distance. This provides greater flexibility in the types of distance equations that can be employed, extending vector search functionality to a broader range of use cases.
A custom distance function is created by a user-defined JavaScript function defined in a Multilingual Engine (MLE) inline call specification. The signature of the function must match the signature of the existing built-in distance functions. As in, it must accept exactly two arguments of type VECTOR and return a BINARY_DOUBLE. The function signature must also include the DETERMINISTIC keyword. The following function definition provides an example of a custom distance function, in this case implementing the Euclidean Squared distance:
SQL 🚀 > CREATE OR REPLACE FUNCTION euclidean_sq_vector_distance("a" VECTOR, "b" VECTOR)
RETURN BINARY_DOUBLE
DETERMINISTIC PARALLEL_ENABLE
AS MLE LANGUAGE JAVASCRIPT PURE
{{
let len = a.length;
let sum = 0;
for(let i = 0; i < len; i++) {
const tmp = a[i] - b[i];
sum += tmp * tmp;
}
return sum;
}};
/
Function EUCLIDEAN_SQ_VECTOR_DISTANCE compiled.
To use the user-defined function in a vector index, you can use the DISTANCE CUSTOM <function name> clause, for example:
SQL 🚀 > CREATE TABLE my_vectors (id NUMBER, vec VECTOR(2, FLOAT32));
Table MY_VECTORS created.
SQL 🚀 > INSERT INTO my_vectors
VALUES (1, vector('[1.1, 2.1]', 2, float32)),
(2, vector('[2.2, 4.2]', 2, float32)),
(3, vector('[1.7, 3.4]', 2, float32)),
(4, vector('[21.4, 15.5]', 2, float32)),
(5, vector('[0.5, 1.6]', 2, float32));
5 rows inserted.
SQL 🚀 > COMMIT;
Commit complete.
SQL 🚀 > CREATE VECTOR INDEX my_vector_index ON my_vectors (vec)
ORGANIZATION INMEMORY
NEIGHBOR GRAPH WITH TARGET ACCURACY 95
DISTANCE CUSTOM EUCLIDEAN_SQ_VECTOR_DISTANCE
PARALLEL 3;
Vector INDEX created.
Similarly, the user-defined function can, of course, also be used in the SELECT and ORDER BY clause:
SQL 🚀 > set autotrace on explain;
Autotrace Enabled
Displays the execution plan only.
SQL 🚀 > SELECT id, euclidean_sq_vector_distance(vec, VECTOR('[1, 2]')) AS distance
FROM my_vectors
ORDER BY euclidean_sq_vector_distance(vec, VECTOR('[1, 2]'))
FETCH FIRST 3 ROWS ONLY;
ID DISTANCE
_____ _______________________
1 0.019999985694894917
5 0.40999998092651424
3 2.450000333786022
PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________
SQL_ID 25sx0nmn069hc, child number 0
-------------------------------------
SELECT id, euclidean_sq_vector_distance(vec, VECTOR('[1, 2]')) AS
distance FROM my_vectors ORDER BY euclidean_sq_vector_distance(vec,
VECTOR('[1, 2]')) FETCH FIRST 3 ROWS ONLY
Plan hash value: 1851966073
----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 5 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 5 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| MY_VECTORS | 5 | | | |
| 5 | VECTOR INDEX HNSW SCAN | MY_VECTOR_INDEX | 5 | | | |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=3)
3 - filter(ROWNUM<=3)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
For more information, see Custom Distance Function in the Oracle Database Documentation.