/*===============================================================
ORACLE 23AI COMPLETE AI + SQL + MACHINE LEARNING MASTER SCRIPT
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 3
JSON DATA TYPE AND JSON PROCESSING IN ORACLE 23AI
=================================================================
JSON (JavaScript Object Notation) is a widely used format
for storing semi-structured data.
Oracle 23ai provides strong native support for JSON data.
Key Capabilities
----------------
1 Native JSON data type
2 JSON_VALUE
3 JSON_QUERY
4 JSON_EXISTS
5 JSON_TABLE
6 JSON indexing
7 JSON search
8 JSON modification
JSON allows applications to store flexible schema data
without requiring rigid relational columns.
===============================================================*/
/*===============================================================
SECTION 1
CREATE TABLE WITH JSON COLUMN
===============================================================*/
DROP TABLE customers_json PURGE;
CREATE TABLE customers_json
(
customer_id NUMBER PRIMARY KEY,
customer_data JSON
);
/*===============================================================
INSERT JSON DATA
===============================================================*/
INSERT INTO customers_json VALUES
(
1,
'{
"name":"John",
"age":30,
"city":"New York",
"orders":[1001,1002]
}'
);
INSERT INTO customers_json VALUES
(
2,
'{
"name":"Mary",
"age":25,
"city":"London",
"orders":[1003]
}'
);
INSERT INTO customers_json VALUES
(
3,
'{
"name":"David",
"age":35,
"city":"Sydney",
"orders":[1004,1005,1006]
}'
);
COMMIT;
/*===============================================================
SECTION 2
VIEW JSON DATA
===============================================================*/
SELECT * FROM customers_json;
/*===============================================================
SECTION 3
JSON_VALUE
===============================================================
JSON_VALUE extracts a scalar value from JSON.
It returns a single value such as
number
string
boolean
===============================================================*/
/* Extract customer name */
SELECT
customer_id,
JSON_VALUE(customer_data,'$.name') AS customer_name
FROM customers_json;
/* Extract city */
SELECT
customer_id,
JSON_VALUE(customer_data,'$.city') AS city
FROM customers_json;
/* Extract age */
SELECT
customer_id,
JSON_VALUE(customer_data,'$.age') AS age
FROM customers_json;
/*===============================================================
SECTION 4
JSON_QUERY
===============================================================
JSON_QUERY extracts JSON objects or arrays.
Unlike JSON_VALUE, it returns structured JSON.
===============================================================*/
SELECT
customer_id,
JSON_QUERY(customer_data,'$.orders') AS orders
FROM customers_json;
/*===============================================================
SECTION 5
JSON_EXISTS
===============================================================
JSON_EXISTS checks if a path exists inside JSON.
It returns TRUE or FALSE.
===============================================================*/
SELECT *
FROM customers_json
WHERE JSON_EXISTS(customer_data,'$.orders');
/*===============================================================
SECTION 6
JSON_TABLE
===============================================================
JSON_TABLE converts JSON into relational rows.
This is extremely powerful when working
with arrays inside JSON.
===============================================================*/
SELECT
customer_id,
orders
FROM customers_json,
JSON_TABLE
(
customer_data,
'$.orders[*]'
COLUMNS
(
orders NUMBER PATH '$'
)
);
/*===============================================================
SECTION 7
SEARCH JSON CONTENT
===============================================================*/
SELECT *
FROM customers_json
WHERE JSON_VALUE(customer_data,'$.city') = 'London';
/*===============================================================
SECTION 8
UPDATE JSON DATA
===============================================================*/
UPDATE customers_json
SET customer_data =
JSON_TRANSFORM
(
customer_data,
SET '$.city' = 'Toronto'
)
WHERE customer_id = 2;
COMMIT;
/*===============================================================
VERIFY UPDATE
===============================================================*/
SELECT
customer_id,
JSON_VALUE(customer_data,'$.city')
FROM customers_json;
/*===============================================================
SECTION 9
JSON INDEXING
===============================================================
Indexes improve JSON query performance.
Oracle allows indexing JSON expressions.
===============================================================*/
CREATE INDEX idx_json_city
ON customers_json
(
JSON_VALUE(customer_data,'$.city')
);
/*===============================================================
SECTION 10
COMPLEX JSON SEARCH
===============================================================*/
SELECT *
FROM customers_json
WHERE JSON_VALUE(customer_data,'$.age') > 30;
/*===============================================================
SECTION 11
JSON + RELATIONAL JOIN
===============================================================*/
SELECT
c.customer_id,
JSON_VALUE(c.customer_data,'$.name') AS customer_name,
JSON_VALUE(c.customer_data,'$.city') AS city
FROM customers_json c;
/*===============================================================
END OF PART 3
===============================================================
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied
warranties, including but not limited to warranties of accuracy, completeness, reliability,
merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees
that they do so at their own risk. The author shall not be held liable for any direct, indirect,
incidental, consequential, special, or exemplary damages, including but not limited to loss of data,
loss of profits, system failures, security breaches, or any other damages or losses arising from the
use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and
configurations in a controlled, non-production (test) environment prior to deploying them in any
live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with
all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author
from any claims, damages, liabilities, or expenses arising from their use.
===============================================================*/